MySQL RESTRICT and NO ACTION

mysql foreign-keys cascade
Erebus picture Erebus · Apr 27, 2011 · Viewed 48k times · Source

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?

Answer

Anthony Accioly picture Anthony Accioly · Apr 27, 2011

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, so NO ACTION is the same as RESTRICT.

Related questions

FOREIGN KEY references same table's column. Can't insert values

I created table with FOREIGN KEY and can't insert anything. CREATE TABLE menus ( id int(10), parent_id int(10), label varchar(255), PRIMARY KEY (id), FOREIGN KEY (parent_id) REFERENCES menus (id) ); I need FOREIGN KEY to automatically delete children when parent …

Read More
Add Foreign Key to existing table

I want to add a Foreign Key to a table called "katalog". ALTER TABLE katalog ADD CONSTRAINT `fk_katalog_sprache` FOREIGN KEY (`Sprache`) REFERENCES `Sprache` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL; When I try to do this, …

Read More
How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) …

Read More