Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 143
  • Last Modified:

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?
0
smalig
Asked:
smalig
  • 3
1 Solution
 
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
 
slightwv (䄆 Netminder) Commented:
If you set the constraints to cascade on delete, then a single delete will remove all the children as well.
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now