In MySQL, a table with the InnoDB engine type supports foreign key constraints.
Use conditions for foreign keys:
1. Two tables must be INNODB tables, MyISAM table temporarily does not support foreign keys (it is said that future versions may be supported, but at least not currently supported);
2. The foreign key column must be indexed, and the MySQL 4.1.2 version will automatically create the index when the foreign key is established, but if it is required to be displayed in an earlier version;
3. The columns of the two tables of the foreign-key relationship must be of similar data types, i.e. columns that can be converted to each other, such as int and tinyint, while int and char are not;
The advantage of foreign key: can make two tables association, guarantee data consistency and realize some cascade operation;
Definition syntax for foreign keys:
Copy Code code as follows:
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[on DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[on UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
This syntax can be used in CREATE table and ALTER table, and a name is automatically generated if constraint symbol,mysql is not specified.
On DELETE and on update to indicate the event trigger limit, you can set parameters:
RESTRICT (restricts foreign key changes in the appearance)
CASCADE (following foreign key changes)
Set NULL (SET NULL value)
Set default (Set defaults)
No action (no action, default)