In MySQL, tables of the InnoDB Engine type support foreign key constraints.
Usage conditions of foreign keys:
1. The two tables must be InnoDB tables, and MyISAM tables do not currently support foreign keys (it is said that foreign keys may be supported in later versions, but at least not supported currently );
2. The foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when creating the foreign key. However, if the foreign key column is in an earlier version, the index must be created;
3. the columns of the foreign key relationship must be of similar data types, that is, columns that can be converted to each other. For example, int and tinyint can be used, but int and char cannot;
Benefits of Foreign keys: two tables can be associated to ensure data consistency and perform cascade operations;
Syntax for defining foreign keys:
Copy codeThe Code is 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. If the CONSTRAINT symbol is not specified, MYSQL automatically generates a name.
On delete and on update indicate the event trigger limit. You can set the following parameters:
RESTRICT)
CASCADE (with foreign key changes)
Set null (set null)
SET DEFAULT)
No action (no action, default)