Andrew Fraser DBA

Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child records

Here is a demo example.

1) Set up example tables:

CREATE TABLE parent
( parent_id number(10) not null,
CONSTRAINT parent_pk PRIMARY KEY (parent_id)
);

CREATE TABLE child
( child_id        numeric(10)     not null,
parent_id       numeric(10)     not null,
CONSTRAINT fk_child FOREIGN KEY (parent_id) REFERENCES parent(parent_id)
);

insert into parent values (1) ;
insert into child values (1,1) ;

2) By default, deletes from the parent will not work until child records have been removed:

delete from parent where parent_id = 1 ;
delete from parent where parent_id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (FK_CHILD) violated - child record found

3) But we can make the constraint have the “on delete cascade” option. That requires dropping and recreating the constraint (or it could have been specified that way at original creation time):

alter table child drop constraint fk_child ;
alter table child add constraint fk_child FOREIGN KEY (parent_id) REFERENCES   parent(parent_id) on delete cascade enable novalidate ;
alter table child modify constraint fk_child validate ;

Now we can delete from the parent even with child records in place:

delete from parent where parent_id = 1 ;

1 row deleted

And the child rows have been automagically deleted also.

The big advantage of this is that it is faster, especially for mass deletes.