Home » SQL & PL/SQL » SQL & PL/SQL » Updating a primaty key and reflecting the changes to foreign keys.
Updating a primaty key and reflecting the changes to foreign keys. [message #224664] Thu, 15 March 2007 01:19 Go to next message
pyaranoid
Messages: 5
Registered: June 2006
Junior Member
Hi Gurus,

I want to update the primary key..and reflect the changes to the foreign keys as well..how can i do it??

Regards

Jim
Re: Updating a primaty key and reflecting the changes to foreign keys. [message #224670 is a reply to message #224664] Thu, 15 March 2007 01:49 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You'll have to use deferrable constraints(these constraints are checked before commit, so in a mean time you may have orphaned children).

HTH.
Michael
Re: Updating a primaty key and reflecting the changes to foreign keys. [message #224694 is a reply to message #224664] Thu, 15 March 2007 02:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pyaranoid wrote on Thu, 15 March 2007 07:19
I want to update the primary key..


Wrong. If it really is a primary key, you do NOT want to update it. That is what makes a primary key a primary key!
Re: Updating a primaty key and reflecting the changes to foreign keys. [message #224801 is a reply to message #224664] Thu, 15 March 2007 09:28 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
A way to do this would be to insert a duplicate row except for the primary key column

insert into my_table(col1,col2,col3)
select 23,col2,col3,col4
from my_table
where col1 = 22;

Then alter the child table rows

update my_table_child
set col1 = 23
where col1 = 22;

then drop the parent key
delete from my_table
where col1 = 22;

commit;
Re: Updating a primaty key and reflecting the changes to foreign keys. [message #224962 is a reply to message #224801] Fri, 16 March 2007 02:54 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm with Frank here: Primary keys shouldn't change. But, perhaps Tom Kyte's approach of cascading update is of any help.

MHE
Previous Topic: OUTER JOINS
Next Topic: How to create a procedure in a package body
Goto Forum:
  


Current Time: Fri Dec 02 16:53:26 CST 2016

Total time taken to generate the page: 0.23060 seconds