| Change primary key value [message #246302] |
Wed, 20 June 2007 10:11  |
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
|
Member |
|
|
I need to change the value of primary key from one value to another. Do I drop the index on the primary key first? Is there a way to disable the primary constraint change the value and then re-enable the constraint? Is this possible at all? If so, can someone please let me know as soon as possible. Thanks so much! 
|
|
|
|
| Re: Change primary key value [message #246304 is a reply to message #246302] |
Wed, 20 June 2007 10:17  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
What have you tried so far? Have you tried to change the value and received an error? Has someone told you something that implies that you can't do this?
|
|
|
|
| Re: Change primary key value [message #246309 is a reply to message #246302] |
Wed, 20 June 2007 10:20 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Firstly, needing to do this usually means that you've got a design flaw - you're probably storing information in the primary key. If you'd used a surrogate primary key populated by a sequence, you'd probably not need to change it.
If you don't have any foreign key constraints pointing to the Pk, then you've not got a problem.
If you do have FK constraints, then your best bet is to change the constraints to DEFERRED. That way you can alter the value of the PK and the related FKs, and the constraints will only be checked when the transaction is committed.
|
|
|
|
| Re: Change primary key value [message #246388 is a reply to message #246302] |
Wed, 20 June 2007 15:07 |
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
|
Member |
|
|
When I try to update my table with new values as
update svc_ee_mtf
set parent_dmis_id = 0100
where parent_dmis_id = 6223.
I get the following error message:
ORA-02291: integrity constraint (EASIV.PDMISID_SVCEEMTF) violated - parent key not found
Please, can anyone diagnose the problem?
|
|
|
|
|
|
| Re: Change primary key value [message #246392 is a reply to message #246389] |
Wed, 20 June 2007 15:17 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
Then you are trying to change a foreign key value, not a primary key value. There must be a value of 0100 in the related parent table for your update to be successful.
|
|
|
|
| Re: Change primary key value [message #246393 is a reply to message #246388] |
Wed, 20 June 2007 15:17 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
cwvabs00 [pabs] /oracle/net/network/admin> oerr ora 2291
02291, 00000,"integrity constraint (%s.%s) violated - parent key not found"
// *Cause: A foreign key value has no matching primary key value.
// *Action: Delete the foreign key or add a matching primary key.
cwvabs00 [pabs] /oracle/net/network/admin>
So .... you are changing a column wich has a foreign key constraint. This foreign key checks whether a primary key in the other table (to wich the foreign key is pointing) exist.
And .... in your case it doesn't.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
