Transferred from: http://blog.csdn.net/cnjsnt_s/article/details/5548280
For specific use, refer to: http://blog.csdn.net/codeforme/article/details/5539454
There are two common engine types for MySQL: MyISAM and InnoDB. Currently only the InnoDB engine type supports foreign key constraints. The syntax for INNODB FOREIGN KEY constraint definitions 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 | NO ACTION
The use of foreign keys needs to meet the following conditions:
1. Two tables must all be innodb tables, and they have no temporary tables.
2. The corresponding column that establishes the foreign key relationship must have a similar INNODB internal data type.
3. The corresponding column establishing the foreign key relationship must be indexed.
4. If the constraint symbol is explicitly given, the symbol must be unique in the database. If not explicitly given, InnoDB will be created automatically.
If the child table attempts 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 attempts to update or delete any foreign key values that exist or match in any of the child tables, the final action depends on the on update and on delete options in the FOREIGN key constraint definition. The INNODB supports 5 different actions, and if no on delete or on UPDATE is specified, the default action is restrict:
1. CASCADE: Deletes or updates the corresponding row from the parent table, and automatically deletes or updates the matching rows from the table. On DELETE Canscade and on UPDATE Canscade are supported by InnoDB.
2. Set NULL: Deletes or updates the corresponding row from the parent table, and sets the foreign key column in the child table to be empty. Note that these foreign key columns are not set to NOT NULL when they are valid. On DELETE set null and on UPDATE set set NULL are supported by INNODB.
3. NO Action:innodb refuses to delete or update the parent table.
4. RESTRICT: Refuses to delete or update the parent table. specifying Restrict (or no ACTION) is the same as ignoring the on delete or the on Update option.
5. SET Default:innodb is not currently supported.
FOREIGN KEY constraints have the most use of the two conditions are no different:
1) The parent table is updated when the child table is also updated when the parent table is deleted if the child table has a matching item, the deletion fails;
2) When the parent table is updated, the child table is also updated, and the parent table is deleted when the child table matches the item.
In the former case, in the foreign key definition, we use on update CASCADE on delete RESTRICT; in the latter case, you can use on Update CASCADE on delete CASCADE.
InnoDB allows you to add a new foreign key to an already existing table using ALTER 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;
Four different foreign keys for MySQL
