Categories
innodb mysql

What is MySQL’s default ON DELETE behavior?

I’m trying to parse the MySQL docs. They could be clearer. What they seem to be saying is that there are five possibilities: SET NULL, NO ACTION, RESTRICT, CASCADE, and SET DEFAULT.

NO ACTION and RESTRICT do the same thing (prevent any DB change that breaks an FK) and that thing is the default so if you omit an ON DELETE clause you’re saying NO ACTION (or RESTRICT — same thing).

SET NULL allows a parent row deletion, sets the FK to NULL.

CASCADE deletes child row.

SET DEFAULT should just never be used.

Is this more or less correct?

Yes, it is correct:

NO ACTION: […] InnoDB
rejects the delete or update operation
for the parent table.

RESTRICT: Rejects the delete or update
operation for the parent table.
Specifying RESTRICT (or NO ACTION) is
the same as omitting the ON DELETE or
ON UPDATE clause. […]

Apparently NO ACTION and RESTRICT are synonymous. Additionally, since they are used whenever there is no ON DELETE / UPDATE clause, this is the default behavior.

SET NULL: Delete or update the row from the parent table and set the
foreign key column or columns in the
child table to NULL. […]

The foreign column is set to NULL, provided it is not declared as NOT NULL (or InnoDB will not allow deletion or update).

CASCADE: Delete or update the row from the parent table and
automatically delete or update the
matching rows in the child table.
[…]

Cascade deletes (or updates) the foreign column.

SET DEFAULT: This action is recognized
by the parser, but InnoDB rejects
table definitions containing ON DELETE
SET DEFAULT or ON UPDATE SET DEFAULT
clauses.

So basically you cannot use that option.