Home » SQL & PL/SQL » SQL & PL/SQL » Foreign key on updating
Foreign key on updating [message #246020] Tue, 19 June 2007 10:35 Go to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

I have a small dilemma with oracle foreign keys.
Oracle give me to set up foreign key to another table, and this is ok, but it have only "on delete" rule which is set to none to protect data from erasinf while referential data is exsist.

So, is there any solution to set "on update" rule? If I have a master and detail table and foreign key which protect master record delete while detail data exsist. How to set foreign key is I UPDATE master field, to update detail field which is part of foreign key?

Thanks in advance...
Re: Foreign key on updating [message #246027 is a reply to message #246020] Tue, 19 June 2007 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle only implements "on delete cascade" and "on delete set null", no other option.
You can implement yours using triggers but this is very difficult (impossible?) to do it without locking the whole table.

The best way is to always update through a procedure then you can do what you want during the "update".

Regards
Michel

[Edit: add last sentence]

[Updated on: Tue, 19 June 2007 10:50]

Report message to a moderator

Re: Foreign key on updating [message #246058 is a reply to message #246020] Tue, 19 June 2007 12:24 Go to previous message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Thanks Michel!
Previous Topic: Leap years with a twist
Next Topic: Regd CONCAT_ALL
Goto Forum:
  


Current Time: Fri Dec 09 19:32:45 CST 2016

Total time taken to generate the page: 0.12279 seconds