Home » SQL & PL/SQL » SQL & PL/SQL » Foreign Key Problem
icon2.gif  Foreign Key Problem [message #248163] Thu, 28 June 2007 03:44 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi all,


if i modified a column in one table which is a primary key , is that changes will be automatically reflected in the foreign keys referencing that key?? or i have to create a trigger , if so please tell me how?? Give me the example
Re: Foreign Key Problem [message #248165 is a reply to message #248163] Thu, 28 June 2007 03:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
primary keys are immutable.
Re: Foreign Key Problem [message #248168 is a reply to message #248163] Thu, 28 June 2007 03:57 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Thnks Frank,

Is it possible to create the Upadate_cascade trigger and that to change the columns which is primary key.. But one thing I dont know what is Update_cascade trigger..I heard tht word from one of my seniors told..Pls send along with that defination also(update_cascade trigger)with examples..
Re: Foreign Key Problem [message #248172 is a reply to message #248163] Thu, 28 June 2007 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is that changes will be automatically reflected in the foreign keys referencing that key

No.

Quote:
or i have to create a trigger

No.

As changing a PK value must be exceptional, create a procedure that locks the tables (master and dependant ones) and make the update.
A trigger will fail in concurrent access.

Regards
Michel
Re: Foreign Key Problem [message #248193 is a reply to message #248163] Thu, 28 June 2007 04:29 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Please reply my post message...If it is not use Update_cascade trigger for that solution..then tell me the exact explanation of Update_cascade trigger..what purpose of using that trigger?
Re: Foreign Key Problem [message #248212 is a reply to message #248163] Thu, 28 June 2007 05:17 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
No such trigger exists, you shall implement it yourself.
Better way is to redesign your application to use only immutable primary keys.

See Updating child table automatically thread on AskTom and study the example it contains.
Re: Foreign Key Problem [message #248229 is a reply to message #248193] Thu, 28 June 2007 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balaji23_d,

Oracle does not natively support "on update cascade".

Regards
Michel
Re: Foreign Key Problem [message #248232 is a reply to message #248212] Thu, 28 June 2007 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Jiri (flyboy),

Tom would surely go back on his first solutions.
As he said in his answer to Amit's question "Why we prefer "procedure/deferrable constraints" OVER Triggers.":
Quote:
because triggers are evil.

because side effects are bad.

because explicit linear code is more maintainable then "happens as a by product of something int he background"

because your goal is to never actually CALL this procedure in real life

because the triggers would add great overhead, that is unnecessary

because the triggers (plural, it takes a minimum of three) would be really hard to code for each table.

because triggers are evil.

magic should be avoided. Experience tells me this.

And I fully agree with him.

Regards
Michel
Re: Foreign Key Problem [message #248411 is a reply to message #248163] Thu, 28 June 2007 23:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Michel,

I was not accurate mentioning trigger, just copied it from original post.
In fact I have not find trigger implementation there, just the procedure you mentioned.
I agree stored procedure is better solution.
Re: Foreign Key Problem [message #248413 is a reply to message #248411] Thu, 28 June 2007 23:45 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, I didn't read the whole thread just remembered the original paper from Tom about "update cascade" wrote almost 10 years ago.
You can find it at: UPDATE CASCADE PACKAGE

Regards
Michel
Previous Topic: Transpose functionality
Next Topic: Objects in Oracle
Goto Forum:
  


Current Time: Thu Dec 08 08:30:53 CST 2016

Total time taken to generate the page: 0.16943 seconds