Some points to note about MySQL foreign key usage

Recently just contacted MySQL, in the establishment of the expression encountered some problems, always prompt error code: The Can ' t create table ..., so I went to the internet to search for a bit of the following points to note:

[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 use of NO ACTION foreign key requires 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.

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;

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/cnjsnt_s/archive/2010/05/01/5548280.aspx

Some points to note about MySQL foreign key usage