Delete a child and a parent row with one SQL scrip

Question

Instead of deleting the child row and then writing another sql statement to delete the parent row I wanted to use one statement which will do both. FYI: we use Oracle database.

Update: I dont have a privilege to do DELETE ON CASCADE

Answer 1

delete from 
(
select * from parent join child using (id)
where id = 1
)

WARNING! Will only delete where both parent AND child rows exist. Will NOT delete parents without children

Answer 2

You can only do it badly - i.e., using triggers.

create table parent
(pid number,
   constraint parent_pk
     primary key (pid)
     using index
);

create table child
  (cid number,
   pid number,
   constraint child_pk
     primary key(cid)
     using index,
   constraint child_fk
     foreign key (pid)
     references parent (pid)
  );

create index child_fk on child (pid);

create trigger fake_delete_cascade
before delete on parent
for each row
begin
  delete from child where pid = :old.pid;
end;
/

insert into parent values (1);
insert into child values (1,1);
commit;
select count(*) from child;
delete from parent where pid = 1;
select count(*) from child;

Answer 3

Another (boring way, we have this in a database which, for unknown reason, don't use foreign keys as constraints - yes yes) to do this would be to create a trigger after (or before) delete.

You'll have to write another delete query, but just in the trigger.

But if you can't put delete cascade, I'm not sure you can add triggers...

Answer 4

If you always want to delete the children when you delete a parent row, you can declare the foreign key constraint so that Oracle does the child delete automatically

create table parent (
  parentID number primary key,
  parentData varchar2(100)
);

create table child (
  childID number primary key,
  parentID number references parent( parentID ) on delete cascade,
  childData varchar2(100)
);

for example, will declare a parent table and a child table and automatically delete the child rows when you delete the parent row. If you don't want that sort of thing to be enforced automatically or you don't like the complexity that is added when things happen "automagically" in the background, you're probably stuck with using multiple DELETE statements.

Answer 5

Define your foreign keys with cascading deletes. Then you only need to delete the "parent" row.