MySQL RESTRICT and NO ACTION
What's the difference in a MySQL FK between RESTRICT
and NO ACTION
? From the doc they seem exactly the same. Is this the case? If so, why have both?
From MySQL Documentation: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
Some database systems have deferred checks, and
NO ACTION
is a deferred check. In MySQL, foreign key constraints are checked immediately, soNO ACTION
is the same asRESTRICT
.
It is to comply with standard SQL syntax. Like the manual says: (emphasis mine)
NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.
They are identical in MySQL.
In the SQL 2003 standard there are 5 different referential actions:
CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT
The difference between NO ACTION
and RESTRICT
is that according to the standard, NO ACTION
is deferred while RESTRICT
acts immediately.