Skip to Main Content
  • Questions
  • Deleting rows from parent / Child tables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 07, 2013 - 2:51 am UTC

Last updated: March 07, 2013 - 3:12 pm UTC

Version: 11.2.0.3.0

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I don't know whether this question was posted earlier. I have searched here for similar questions but didn't find the related one, please direct me if the same question was asked earlier.

My requirement is , I have to delete few rows from a parent table and its child tables. And even if child tables contain its child tables then from there also data has to be deleted. I have checked the Foreign keys of most of the child tables where ON DELETE CASCADE clause is not being used. Could you please help me with the ways to delete the rows without using ON DELETE CASCADE clause or any dynamic code using which I can delete any row. Many thanks in advance.


Regards,
Thahessen

and Tom said...

You would have to write a query for each child of child of child and so on that looks like:


delete from some_child
where (some_child.fkey) in (select primary-key
from some_other_child
where (some_other_child.fkey) in (select key
from parent))


for a child of a child of a parent - for example. You'd need to go as many layers deep (all information needed is in user_constraints and user_cons_columns).

IF you truly want this
then
recreate constraints with on delete cascade
end if


else, you should create a procedure, using STATIC SQL, to perform these operations.


Please don't say "but I have to make this dynamic" - because no you don't. Just create procedures to clearly and statically and in a documented fashion do what you programmed it to do.

I would not use on delete cacade.

I would use a procedure that statically deleted from the children on up, using my knowledge of the schema as it exists.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Other nice syntax ...

David Aldridge, March 12, 2013 - 8:11 am UTC

It's not very well documented, but you can delete from a multitable inline view if the joins are supported by foreign key contraints, with the "child-most" (can't think of the right word for it) table being the target of the deletes. I think the syntactic benefits get better the more levels are involved.


SQL Fiddle here: http://sqlfiddle.com/#!4/e0ea4/9

Demonstration:

create table parent (
  id integer primary key,
  value      varchar2(30));

create table child  (
  id        integer primary key,
  parent_id references parent);

create table grandchild  (
  id        integer primary key,
  child_id  references child);

insert into parent values (1,'a');
insert into parent values (2,'b');
insert into parent values (3,'c');

insert into child  values (1,1);
insert into child  values (2,2);
insert into child  values (3,2);
insert into child  values (4,3);
insert into child  values (5,3);
insert into child  values (6,3);

insert into grandchild values(1,1);
insert into grandchild values(2,1);
insert into grandchild values(3,3);
insert into grandchild values(4,3);
insert into grandchild values(5,3);
insert into grandchild values(6,6);
insert into grandchild values(7,6);

select count(*) grandchildren_before_delete
from   parent join
       child      on (parent.id           = child.parent_id) join
       grandchild on (grandchild.child_id = child.id       )
where  parent.value = 'a';

delete from (
  select *
  from   parent join
         child      on (parent.id           = child.parent_id) join
         grandchild on (grandchild.child_id = child.id       )
  where  parent.value = 'a');

select count(*) grandchildren_after_delete
from   parent join
       child      on (parent.id           = child.parent_id) join
       grandchild on (grandchild.child_id = child.id       )
where  parent.value = 'a';

delete from (
  select *
  from   parent join
         child      on (parent.id           = child.parent_id)
  where  parent.value = 'a');

select count(*) children_after_delete
from   parent join
       child      on (parent.id           = child.parent_id)
where  parent.value = 'a';

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library