Key constraints outside MySQL

Turn: http://www.cnblogs.com/yidianfeng/archive/2011/02/24/1964148.html

MySQL has two common engine types: MyISAM and InnoDB. Currently, only the InnoDB Engine type supports foreign key constraints. The syntax for defining foreign key constraints in InnoDB is as follows:

[Constraint [Symbol] foreign key
[Index_name] (index_col_name ,...)
References tbl_name (index_col_name ,...)
[On Delete reference_option]
[On update reference_option]

Reference_option:
Restrict | cascade | set null | the usage of the no action foreign key must meet the following conditions:

1. Both tables must be InnoDB tables without temporary tables.

2. The corresponding columns for establishing foreign key relationships must have similar InnoDB internal data types.

3. indexes must be created for the columns corresponding to the foreign key relationship.

4. If the constraint symbol is explicitly given, it must be unique in the database. If no explicit statement is provided, InnoDB is automatically created.

If a child table tries to create a foreign key value that does not exist in the parent table, InnoDB rejects any insert or update operations. If the parent table tries to update or delete any foreign key values that exist or match in any child table, the final action depends on the on update and on delete options in the foreign key constraint definition. InnoDB supports five different actions. If on delete or on update is not specified, the default action is restrict:

1. Cascade: deletes or updates corresponding rows from the parent table, and automatically deletes or updates matched rows from the table. Both on Delete canscade and on update canscade are supported by InnoDB.

2. Set NULL: delete or update the corresponding row from the parent table, and set the foreign key column in the child table to null. Note that these foreign key columns are valid only when they are not set to not null. Both on Delete set null and on update set null are supported by InnoDB.

3. No action: InnoDB refuses to delete or update the parent table.

4. Restrict: refuse to delete or update the parent table. The effect of specifying restrict (or no action) is the same as that of ignoring the on delete or on update options.

5. Set Default: InnoDB currently does not support this function.

The maximum number of foreign key constraints is exceeded in the following two cases:

1) when the parent table is updated, the child table is also updated. When the parent table is deleted, if the child table has matched items, the deletion fails;

2) when the parent table is updated, the child table is also updated. When the parent table is deleted, the matching items of the child table are also deleted.

In the previous case, in the foreign key definition, we use on update cascade on Delete restrict; in the latter case, we can use on update cascade on Delete cascade.

InnoDB allows you to use alter table to add a new foreign key to an existing table:

Alter table tbl_name
Add [constraint [Symbol] foreign key
[Index_name] (index_col_name ,...)
References tbl_name (index_col_name ,...)
[On Delete reference_option]
[On update reference_option]

InnoDB also supports the use of alter table to delete Foreign keys:
Alter table tbl_name drop foreign key fk_symbol;

 

 

InnoDB also supports foreign key constraints. The syntax for defining foreign key constraints in InnoDB looks as follows:

 

 
[ConstraintSymbol] Foreign key [ID] (Index_col_name,...)
 

 

 
ReferencesTbl_name(Index_col_name,...)
 

 

 
[On Delete {restrict | cascade | set null | no action}]
 

 

 
[On update {restrict | cascade | set null | no action}]
 

 

The foreign key definition follows the following conditions:

 

· All Tables must be of the InnoDB type and cannot be temporary tables.

· In the referenced table, an index is required, and the foreign key column is listed in the same order as the first column. If such an index does not exist, it must be automatically created in the reference table.

· In the referenced table, there must be an index. The referenced columns are listed in the same order as the first column.

· The index prefix of foreign key columns is not supported. One of the consequences is that blob and text columns are not included in an external key, because the index of these columns must always contain a prefix length.

· If ConstraintSymbolGiven, it must be unique in the database. If it is not given, InnoDB automatically creates this name.

 

InnoDB rejects any insert or update operation that tries to create a foreign key value in the sub-table but does not match the candidate key value in the parent table. A parent table has some child tables with matched rows. InnoDB performs some actions on any update or delete operations that attempt to update or delete the candidate key values in the parent table, this action depends onReferential action. When a user tries to delete or update a row from a parent table and has one or more matched rows in the child table, InnoDB has five options based on the action to be taken:

 

· Cascade: deletes or updates matched rows from the parent table and automatically deletes or updates the matched rows in the child table. On Delete cascade and on update cascade are both available. Between two tables, you should not define several on update cascade clauses that take actions in the same column of the parent or child table.

· Set NULL: delete or update rows from the parent table, and set the foreign key column in the child table to null. If the foreign key column does not specify the not null qualifier, this is unique and valid. The on Delete set null and on update set null clauses are supported.

· No action: in ANSI SQL-92 standards, no action means this does not take action, that is, if there is a related foreign key value in the referenced table, attempts to delete or update key values are not allowed (Gruber,Understanding SQL, 2000: 181 ). InnoDB rejects the deletion or update operation on the parent table.

· Restrict: The operation to delete or update the parent table is denied. No action and restrict are both the same. Delete the on delete or on update clause. (Some database systems have an extension check, and no action is an extension check. In MySQL, foreign key constraints are checked immediately, so no action and restrict are the same ).

· Set Default: this action is parsed.ProgramBut InnoDB rejects table definitions that contain the on Delete set default or on update set default clause.

 

When the candidate keys in the parent table are updated, InnoDB supports the same selection. Select cascade. The foreign key column in the child table is set as the new value of the candidate key in the parent table. In the same way, if the column updated in the sub-Table references the foreign key in another table, update cascade.

 

Note that InnoDB supports foreign keys to be referenced in a table. In these cases, the sub-Table actually means the records attached to the table.

 

InnoDB requires an index for the external key and the referenced key so that the foreign key check can be performed quickly without a table scan. The index of the foreign key is automatically created. Compared with some old versions, indexes must be explicitly created in old versions. Otherwise, the creation of foreign key constraints will fail.

 

In InnoDB, the corresponding columns in the foreign key and referenced columns must have similar internal data types so that they can be compared without type conversion. The size and symbol of the integer type must be the same. The length of the string type does not need to be the same. If you specify a set null action, make sure that you have not declared the columnNot null.

 

If MySQL reports an error code 1005 from the create table statement and the error message string points to errno 150, this means that a foreign key constraint is incorrectly formed and the table creation fails. Similarly, if alter table fails and points to errno 150, it means that the foreign key definition of the modified table will be incorrectly formed. You can use show InnoDB status to display a detailed explanation of the recent InnoDB foreign key error on the server.

 

Note:InnoDB does not check the foreign key constraints for those foreign keys or referenced key values that contain null columns.

 

Deviations from SQL standards:If there are several rows in the parent table with the same referenced key value, then InnoDB takes the action in the foreign key check, as if other parent rows with the same key value do not exist. For example, if you have defined a restrict type constraint and there is a child row with several parent rows, InnoDB cannot delete these parent rows.

 

For records that reside in the corresponding index with foreign key constraints, InnoDB performs cascade operations by means of deep priority selection.

 

Deviations from SQL standards:If on update cascade or on update set null recursively updates the same table, the table is updated during cascade, and it acts like restrict. This means that you cannot use the auto-reference on update cascade or on update set null operation. This will prevent the infinite loop caused by cascade updates. On the other hand, a self-referenced on Delete set null is possible, just like a self-referenced on Delete cascade. Cascade operations cannot be nested for more than 15 layers.

 

Deviations from SQL standards:Similar to MySQL, InnoDB checks unique and foreign key constraints row by row in an SQL statement that inserts, deletes, or updates many rows. According to the SQL standard, the default behavior should be checked for delay, that is, the constraint is checked only after the entire SQL statement is processed. Before InnoDB implements the latency constraint check, some things are impossible, such as deleting a record referenced by a foreign key.