I’ve recently realized that some technique that I use sometimes is subject to an important restriction which I used to ignore.
The Requirement
We have two tables (let’s call them PARENTS and CHILDREN) with a foreign key between them (CHILDREN references PARENTS).
We need to write a procedure that deletes a given child, and if its parent has no other children the parent should be deleted as well.
Demo Data
SQL> select * from parents;
ID
----------
1
2
SQL> select * from children;
ID PARENT_ID
---------- ----------
101 1
201 2
202 2
The Implementation
A simple solution for the conditional deletion of the parent is to try to delete it.
If this parent has no other children, the DELETE statement will succeed.
If it has other children, the DELETE statement will fail with ORA-2292 (integrity constraint violated – child record found), and we can catch this exception and ignore it.
I like this kind of solutions mainly because Oracle automatically takes care of the necessary serialization of concurrent sessions (trying, in this case, to delete or insert children of the same parent).
SQL> create or replace package demo as
2 procedure delete_child(i_id in children.id%type);
3 end demo;
4 /
Package created.
SQL> create or replace package body demo as
2 procedure delete_parent_if_it_has_no_children(i_id in parents.id%type) is
3 e_children_exist exception;
4 pragma exception_init(e_children_exist, -2292);
5 begin
6 delete parents p
7 where p.id = i_id;
8 dbms_output.put_line('parent was deleted successfully');
9 exception
10 when e_children_exist then
11 dbms_output.put_line('parent was not deleted');
12 end delete_parent_if_it_has_no_children;
13
14 procedure delete_child(i_id in children.id%type) is
15 v_parent_id children.parent_id%type;
16 begin
17 delete children c
18 where c.id = i_id
19 returning c.parent_id into v_parent_id;
20
21 delete_parent_if_it_has_no_children(v_parent_id);
22
23 end delete_child;
24 end demo;
25 /
Package body created.
Parent 1 has only one child – 101, so when we delete child 101 its parent is deleted as well:
SQL> exec demo.delete_child(101)
parent was deleted successfully
PL/SQL procedure successfully completed.
Parent 2 has two children – 201 and 202. When we delete one of the children, the parent is not deleted. When we delete the second child, the parent is deleted.
SQL> exec demo.delete_child(201) parent was not deleted PL/SQL procedure successfully completed. SQL> exec demo.delete_child(202) parent was deleted successfully PL/SQL procedure successfully completed. SQL> rollback; Rollback complete.
The Catch
This solution is based on the fact that the foreign key constraint is enforced in the statement level. It means that we can use this solution as long as the foreign key is not deferred. Deferred constraints are enforced at the end of the transaction, and therefore the DELETE PARENTS statement will succeed without raising an exception, even if the deleted parent has children.
I executed the previous example after creating the tables as follows:
SQL> create table parents (
2 id number not null primary key
3 );
Table created.
SQL> create table children (
2 id number not null primary key,
3 parent_id not null
4 constraint fk_children_parents
5 references parents
6 deferrable initially immediate
7 );
Table created.
SQL> begin
2 insert into parents values (1);
3 insert into parents values (2);
4
5 insert into children values (101,1);
6 insert into children values (201,2);
7 insert into children values (202,2);
8
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
Now let’s set the foreign key as deferred, and try to delete only one child of parent 2.
SQL> set constraint fk_children_parents deferred; Constraint set. SQL> exec demo.delete_child(201) parent was deleted successfully PL/SQL procedure successfully completed.
The parent was deleted successfully, although it still has an existing child.
But when we try to commit the transaction, the foreign key is checked, and the whole transaction is rolled back (including the deletion of the child).
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (TRANZMATE_DEV.FK_CHILDREN_PARENTS) violated - child record found