Home » SQL & PL/SQL » SQL & PL/SQL » Asking about PK & FK Relations (Oracle 10g)
icon5.gif  Asking about PK & FK Relations [message #288266] Sun, 16 December 2007 16:49 Go to next message
FreeNokia
Messages: 4
Registered: December 2007
Junior Member
Hi all...
if we have five tables for example:
t1(c1 PK,c2)
t2(c3,c5 FK(t1.c1))
t3(c6,c7 FK(t1.c1))
t4(c8,c9 FK(t1.c1))
t5(c10,c11 FK(t1.c1))
in case we want to make update on values on t1 table and we have inserted values on t2,t3,t4,t5 related to the old value that we want to make the update on it.
it will give us reference child error and prevent us from making this update
how could we solve this problem ?

[Edit MC: we can also read the post if it is not in bold size 4 but with default font, more it is easier to read]

[Updated on: Mon, 17 December 2007 00:46] by Moderator

Report message to a moderator

Re: Asking about PK & FK Relations [message #288268 is a reply to message #288266] Sun, 16 December 2007 19:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte has an update cascade package for this purpose:


http://asktom.oracle.com/tkyte/update_cascade/index.html
Re: Asking about PK & FK Relations [message #288308 is a reply to message #288266] Mon, 17 December 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course you have to know that you must lock the tables otherwise unpredictible result can occur if several users do the same thing at the same time.

Regards
Michel
Re: Asking about PK & FK Relations [message #288310 is a reply to message #288266] Mon, 17 December 2007 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
FreeNokia wrote on Sun, 16 December 2007 23:49

how could we solve this problem ?


By not updating primary key values.
Re: Asking about PK & FK Relations [message #288436 is a reply to message #288268] Mon, 17 December 2007 09:23 Go to previous messageGo to next message
FreeNokia
Messages: 4
Registered: December 2007
Junior Member
Barbara Boehmer wrote on Mon, 17 December 2007 04:47

Tom Kyte has an update cascade package for this purpose:


http://asktom.oracle.com/tkyte/update_cascade/index.html


what about if we add cascade property to the column on creation of the table
Re: Asking about PK & FK Relations [message #288444 is a reply to message #288436] Mon, 17 December 2007 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not exist in Oracle.

Regards
Michel
Re: Asking about PK & FK Relations [message #288468 is a reply to message #288436] Mon, 17 December 2007 12:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
FreeNokia wrote on Mon, 17 December 2007 07:23

Barbara Boehmer wrote on Mon, 17 December 2007 04:47

Tom Kyte has an update cascade package for this purpose:


http://asktom.oracle.com/tkyte/update_cascade/index.html


what about if we add cascade property to the column on creation of the table



Oracle has a built-in ON DELETE CASCADE, but not UPDATE CASCADE.
icon3.gif  Re: Asking about PK & FK Relations [message #288499 is a reply to message #288468] Mon, 17 December 2007 16:00 Go to previous messageGo to next message
FreeNokia
Messages: 4
Registered: December 2007
Junior Member
Barbara Boehmer wrote on Mon, 17 December 2007 21:16

FreeNokia wrote on Mon, 17 December 2007 07:23

Barbara Boehmer wrote on Mon, 17 December 2007 04:47

Tom Kyte has an update cascade package for this purpose:


http://asktom.oracle.com/tkyte/update_cascade/index.html


what about if we add cascade property to the column on creation of the table



Oracle has a built-in ON DELETE CASCADE, but not UPDATE CASCADE.



SO THE ONLY WAY BY USING "UPDATE CASCADE PACKAGE".THAT IS RIGHT
Re: Asking about PK & FK Relations [message #288512 is a reply to message #288266] Mon, 17 December 2007 21:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have to disagree. I agree with Frank.

Best way is to not update primary key.

Is this a new system you are building?
Or is this an existing system you have to live with?

If it is a new system you are building, then go back to your data modelers and ask them to redesign the table to use a surrogate that can not be updated. Then you are free to change the "original" value that was a key becaues it does not live anywhere else.

Alternatively, you might wish to look into deferred constraints. I am not a fan of these but some people think they are great.

Otherwise, you can start thinking (as was indicated by another replay), about writing a stored procedure to do the delete and having that procedure delete and/or update in the correct order (children first, then parent). Yes, this means you write your own cascade update/cascade delete process.

Good luck, Kevin
Re: Asking about PK & FK Relations [message #288563 is a reply to message #288512] Tue, 18 December 2007 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Best way is to not update primary key.

This is not strong enough.

A PRIMARY KEY IS IMMUTABLE.

Regards
Michel
Re: Asking about PK & FK Relations [message #288707 is a reply to message #288266] Tue, 18 December 2007 07:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
True, but one can still update date its columns just the same, and I find many non-data oriented developers don't mind updating primary key values if they can.

SQL> create table temp1 (a number not null);

Table created.

SQL> alter table temp1 add primary key (a);

Table altered.

SQL> insert into temp1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> update temp1 set a = 2;

1 row updated.

SQL> commit;

Commit complete.


updating primary keys = bad form.

Kevin
Re: Asking about PK & FK Relations [message #289310 is a reply to message #288266] Thu, 20 December 2007 11:34 Go to previous message
FreeNokia
Messages: 4
Registered: December 2007
Junior Member
actually we have an existing system you have to live with.
so we need shortest solutions.
that is right its better to not update the PK (in good designed systems).
Previous Topic: Schedule DBMS job every day in week except Friday
Next Topic: Please help, problems refining searches.
Goto Forum:
  


Current Time: Fri Dec 09 15:22:40 CST 2016

Total time taken to generate the page: 0.40079 seconds