Oracle delete from tables with referential integrity

Experts,
I have 3 tables say a, b and c. table a has the primary key 'a_id' and table b has the primary key 'b_id' and also foreign key from table a as 'a_id'. Table c has the primary key 'c_id' and the foreign key from table b which is 'b_id'.
If I want to delete a record from table a which has child record in table b and the child record in table b has a child record in table c.

can it be done in a single delete statement?
smaligAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If you set the constraints to cascade on delete, then a single delete will remove all the children as well.
0
 
awking00Commented:
If you disable the foreign keys for tables b and c and table a was created with on delete cascade. You should be able to delete the record from table a. However, you would need to delete the child records from b and c before you could re-enable the foreign keys.
0
 
awking00Commented:
I meant to say if the delete rule for b and c is not cascade, you would need to remove the child records first before re-enabling.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in other terms: with the referential constraints ( foreign key ) enabled, you delete from table a only

ogherwise you delete from c first, then from b, finally from a
0
 
awking00Commented:
slightwv is right. I should have stopped my initial response after the second sentence.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.