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?
Answer
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.