Home » SQL & PL/SQL » SQL & PL/SQL » Change primary key value
Change primary key value [message #246302] Wed, 20 June 2007 10:11 Go to next message
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! Embarassed
Re: Change primary key value [message #246304 is a reply to message #246302] Wed, 20 June 2007 10:17 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #246389 is a reply to message #246304] Wed, 20 June 2007 15:08 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
When I tried to update the table I get the following an ORA:02291 error
Re: Change primary key value [message #246392 is a reply to message #246389] Wed, 20 June 2007 15:17 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
martijn
Messages: 283
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.

Re: Change primary key value [message #246396 is a reply to message #246302] Wed, 20 June 2007 15:32 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
Is there a dict view that can show me which table this foreign key points to? Thank you so much for your assistance
Re: Change primary key value [message #246481 is a reply to message #246396] Thu, 21 June 2007 01:27 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
user_constraints gives you the table + constraint name on the other table. user_cons_columns gives you more details about the constraints.

MHE
Previous Topic: can i use multiple LIKE operator in select query
Next Topic: some interview questions
Goto Forum:
  


Current Time: Sat Dec 10 01:06:09 CST 2016

Total time taken to generate the page: 0.06404 seconds