Delete a child and a parent row with one SQL script

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

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

Answer 2

After some really bad experiences with this problem on a relatively big and extremely critical database, I decided to make a Silver bullet for it! because I couldn't find any! Actually, none of the solutions/answers in this thread meet the problem's needs.

See CASCADELETE repo on my github.

Answer 3

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 4

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 5

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 6

In case it helps anyone else, I just wrote a PLSQL script to do this for all foreign key constraints in a table with some help from this Stackoverflow question. Hope it helps.

DECLARE
  CURSOR constraint_cursor IS SELECT *
                              FROM (SELECT a.table_name,
                                           a.constraint_name,
                                           a.column_name,
                                           c_pk.table_name r_table_name,
                                           b.column_name   r_column_name
                                    FROM user_cons_columns a
                                           JOIN user_constraints c ON a.owner = c.owner
                                                                        AND a.constraint_name = c.constraint_name
                                           JOIN user_constraints c_pk ON c.r_owner = c_pk.owner
                                                                           AND
                                                                         c.r_constraint_name = c_pk.constraint_name
                                           JOIN user_cons_columns b ON C_PK.owner = b.owner
                                                                         AND
                                                                       C_PK.CONSTRAINT_NAME = b.constraint_name AND
                                                                       b.POSITION = a.POSITION
                                    WHERE c.constraint_type = 'R'
                                      and c_pk.owner = 'YOUR SCHEMA HERE') tbl;
  sql_statement VARCHAR2(2048) := NULL;
  tab_row       constraint_cursor%rowtype;
BEGIN
  OPEN constraint_cursor;
  FOR i in 1..80 LOOP
    FETCH constraint_cursor into tab_row;
    EXECUTE IMMEDIATE 'ALTER table ' || tab_row.table_name || ' drop constraint ' || tab_row.constraint_name;
    EXECUTE IMMEDIATE 'ALTER table ' || tab_row.table_name || ' add constraint ' || tab_row.constraint_name || ' FOREIGN KEY (' ||
    tab_row.column_name || ') references ' || tab_row.r_table_name || '(' || tab_row.r_column_name || ') ON DELETE CASCADE ';
  end loop;
  close constraint_cursor;
end;

Answer 7

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...