MySQL cascade deletion-mysql cascade restrict

When a primary key ID is deleted from the primary table, it cannot be deleted because the table associated with the primary key has related field data. I checked a lot of information and found that I used on restrict to create a table Association. Maybe the problem is here.

Meanings of foreign key constraints on subtables:

If no candidate key is found in the parent table, insert/update is not allowed in the child table.

Description of the foreign key constraint on the parent table:

When updating or deleting a parent table to update or delete one or more candidate keys that match the row in the child table, the behavior of the parent table depends on: the on update/on Delete clause specified when defining the foreign key of the sub-Table. InnoDB supports five methods, which are listed as follows:

 

. Cascade Method

When updating/deleting records in the parent table, synchronize update/delete matching records in the child table

On Delete cascade is available from mysql3.23.50; on update cascade is available from mysql4.0.8

. Set null

When updating/deleting records in the parent table, set the columns of matched records in the child table to null.

Note that the foreign key column of the sub-table cannot be not null.

On Delete set null is available from mysql3.23.50; on update set null is available from mysql4.0.8

. No action Method

If the child table has matched records, the update/delete operation on the candidate keys corresponding to the parent table is not allowed.

This is ANSI SQL-92 standard, supported since mysql4.0.8

. Restrict Method

The same as no action, both check foreign key constraints immediately

. Set Default Mode

The parser recognizes this action, but InnoDB cannot recognize it and doesn't know what it means...

 

Note: trigger is not affected by the Cascade action of the foreign key, that is, trigger is not released.

In MySQL, three points against SQL standards

1. if multiple key values are the same in the parent table, when performing a foreign key check, other rows with the same key value will not exist; for example, when a restrict foreign key is defined, a child table row corresponds to multiple parent table rows (with the same key value), InnoDB cannot delete all these rows in the parent table.

2. The Parent and Child tables are the same table. You are not allowed to specify on update cascade and on update set null when you reference the table.

From mysql4.0.13, allow the on Delete set null on the same table

From mysql4.0.21, allow on Delete cascade on the same table

However, the cascade level cannot exceed 15

3. When InnoDB checks the unique and constraint constraints, it is row by row instead of the statement or the end of the transaction;

The constraint check in SQL standards is completed when the statement is executed