In MS SQL Server it is possible to create a foreign key with ON UPDATE CASCADE option, so whenever you update one of the columns in the primary key, the foreign keys in other tables will also be update by the DBMS.
So, how to do it in Oracle?
Asked
Active
Viewed 2.4k times
3 Answers
4
Oracle does not allow a Foreign Key constraint with “ON UPDATE CASCADE”.
Here are a couple of options you have.
Create the Foreign Key, and create an “On Update” trigger. Make use of the package below (needs to be installed in the db).
http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteupdatecascade.html
Let me know if you have additional questions or need more information.
Justin Cave
- 227,342
- 24
- 367
- 384
Yves
- 12,059
- 15
- 53
- 57
-
1The Post is old to comment but I see a dead [URL](http://asktom.oracle.com/tkyte/update%5Fcascade/index.html). Being inquisitive here; If anyone happens to know the updated URL, pls. let us know. – hiFI Dec 18 '13 at 10:51
-
@hiFI The post at https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034 may contain the same information. – beldaz Apr 27 '17 at 21:59
-
As usual, link is dead and no code given in answer, just hints... – Matthieu Jul 03 '21 at 10:10
2
Would a database trigger do the job for you ?
Here is the Oracle doc on the subject of Data Integrity for 11g (just incase you were interested).
berlebutch
- 257
- 5
- 13
0
You can't use on update cascade, but you can create a trigger that will resolve the issue:
create table tab1(
pk int PRIMARY KEY,
aa int);
create table tab2(
pk int PRIMARY KEY,
tab1_pk int,
FOREIGN KEY(tab1_pk) REFERENCES tab1(pk));
------------------------------------------
create or replace trigger tab1_pkUpdate
after update of pk on tab1
for each row
begin
update tab2 s
set s.tab1_pk = :new.pk
where s.tab1_pk = :old.pk;
end;
/
