[Restrict] cascade restrict of MySQL

FromHttp://androider.javaeye.com/blog/352332

 

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.

meanings of foreign key constraints on parent tables:
when updating or deleting a parent table to update or delete one or more candidate keys matching rows 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,
. cascade mode
when updating/deleting records on the parent table, synchronize update/delete child table matching records
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