
When you create a foreign key in your database, you can specify what happens upon delete of the parent row. There are usually four possibilities:
- ON DELETE SET NULL
- ON DELETE CASCADE
- ON DELETE NO ACTION
- ON DELETE RESTRICT
Today we’ll investigate the subtle difference between the last two options.
In Some Databases There Is No Difference at All
In Oracle, there is no RESTRICT keyword. The only option is NO ACTION. In MySQL, there is no difference between ON DELETE RESTRICT and ON DELETE NO ACTION.
If There Is a Difference Though...
The databases IBM DB2, PostgreSQL (for INITIALLY DEFERRED constraints), HSQLDB, and SQLite know the difference between ON DELETE RESTRICT and ON DELETE NO ACTION.
In databases in which the two clauses are different:
- RESTRICT constraint rules are checked before any other operation,
- NO ACTION constraint rules are checked after the statement and all other operations (such as triggers) are completed.
In most cases, there is no difference between the two options. The difference is visible when the delete operation is triggered by some other operation, such as delete cascade from a different table, delete via a view with a UNION, a trigger, etc.
Example: ON DELETE RESTRICT
Let’s take a look at an example. There are four tables here: r0
, r1
, r2
, r3
. Table r0
is the parent table for tables r1
and r2
with the DELETE CASCADE constraint, the table r2
is the parent of r3
with the DELETE CASCADE constraint, and r1
is the parent of r3
with the ON DELETE RESTRICT constraint. In the example I use the ON DELETE RESTRICT INITIALLY DEFERRED constraint because that’s when you see the difference in PostgreSQL.
The blue notes show data in all tables.
We try to delete all rows in r0
in PostgreSQL:
delete from r0;
The result:
ERROR: update or delete on table "r1" violates foreign key constraint "t3_t1" on table "r3" DETAIL: Key (id)=(1) is still referenced from table "r3".
What happened here? A possible scenario looks like this:
- the database tries to delete all rows in the table
r0
- cascading, it tries to delete rows in
r1
- cascading again, it tries to delete rows in
r3
. BUT rows inr3
are still references inr2
. - The delete operation is forbidden.
Example: ON DELETE NO ACTION
Now take a look at this example. The only difference is that now the constraint on the reference between table n1
and n3
is ON DELETE NO ACTION (plus I have renamed the r* tables to n* tables). The data in the tables is the same as above.
We try to delete all rows in n0
:
delete from n0;
The result:
DELETE 2
This time the constraints are checked after all operations have been completed, i.e., after we have deleted both child and parent rows.