Suppose I have tables t1, t2, t3 and having the relation t1 -> t2 -> t3, where t1 is the
root level parent and t3 is the child.
Suppose, if there exists a relation between t1 and t2 with delete cascade,t2 and t3 with
delete cascade and t1 and t3 with delete restrict.
t1 --> t2 --> t3 (say delete cascade)
t1 --> t3 (say delete restrict)
Now, how does oracle determines, whether to delete the records from "t3" or not?
If it chooses the path of t1 --> t2, t2 --> t3 then, it deletes the record and t1 --> t3
may find no record to delete.
On the other hand, if it chooses t1 -- t3, it fails with "child record found". Now my question is what strategy oracle uses to choose the delete path.
It would appear we cascade the delete and then check:
CREATE TABLE t1 (x INT PRIMARY KEY);
CREATE TABLE t2 (y INT PRIMARY KEY, x REFERENCES t1 ON DELETE CASCADE);
CREATE TABLE t3 (y REFERENCES t2 ON DELETE CASCADE, x REFERENCES t1);
INSERT INTO t1
VALUES (1);
INSERT INTO t2
VALUES (2, 1);
INSERT INTO t3
VALUES (2, 1);
delete from t1;