ON DELETE RESTRICT vs NO ACTION

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:

1. ON DELETE SET NULL
2. ON DELETE CASCADE
3. ON DELETE NO ACTION
4. 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 in r3 are still references in r2.
• 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.