What are CASCADE, NO ACTION, RESTRICT, and SET NULL in Mysql foreign key settings?-Mysql Tutorial-php.cn

When I use Navicat for mysql to design the table today, when setting the foreign key, there are four values to choose from in the two columns when deleting and updating: CASCADE, NO ACTION, RESTRICT, SET NULL, I tried it all myself, and the differences are as follows:

CASCADE: When the parent table deletes and updates, the child table will delete and update the associated records;
SET NULL: When the parent table deletes or updates, the child table will set the column of the foreign key field of the associated record to null, so please note that the foreign key cannot be set to not null when designing the child table;
When there are records associated with the parent table in the child table, RESTRICT (restriction) will prevent the deletion of the parent table record
NO ACTION: Same as RESTRICT also checks the foreign key first;

Empty, RESTRICT, NO ACTION	Delete: when the record from the table does not exist , the master table can be deleted. If the slave table is deleted, the master table will remain unchanged. Update: When the slave table record does not exist, the main table menu can be updated. When the slave table is updated, the main table remains unchanged.
CASCADE	Delete: Automatically delete the slave table when deleting the master table. Delete the slave table, leaving the master table unchanged. Update: Automatically update the slave table when updating the master table. Update the slave table, leaving the master table unchanged.
SET NULL	Delete: When deleting the master table, the slave table is automatically updated to NULL. When the slave table is deleted, the master table remains unchanged. Update: Automatically update the slave table value to NULL when updating the master table. Update the slave table, leaving the master table unchanged.

Here is a small example to help understand:

(1) In the database bookshop, I created a new table a as follows:

What are CASCADE, NO ACTION, RESTRICT, and SET NULL in Mysql foreign key settings?

Set foreign keys:

What are CASCADE, NO ACTION, RESTRICT, and SET NULL in Mysql foreign key settings?

(2) In the database bookshop, I created a new table b as follows:

What are CASCADE, NO ACTION, RESTRICT, and SET NULL in Mysql foreign key settings?

(3) Insert records into tables a and b as follows:

What are CASCADE, NO ACTION, RESTRICT, and SET NULL in Mysql foreign key settings?

Here: the id2 field of table a is foreign For the key field, refer to the primary key of table b, so table b is the parent table and table a is the child table; then when setting the foreign keys of table a, set four different values to draw the conclusion I mentioned above.

The above is the detailed content of What are CASCADE, NO ACTION, RESTRICT, and SET NULL in Mysql foreign key settings?. For more information, please follow other related articles on the PHP Chinese website!