A simple tutorial on MySQL foreign keys

  • 2020-12-05 17:25:39
  • OfStack

In MySQL, tables of the InnoDB engine type support foreign key constraints.
Conditions for foreign keys:
1. Both tables must be InnoDB tables, and MyISAM tables do not support foreign keys for the time being (it is said that later versions may support them, but at least they do not support them at present);
2. Foreign key columns must be indexed. In the versions after MySQL 4.1.2, indexes are automatically created when foreign keys are created, but in the earlier versions, they need to be displayed.
3. The columns of the two tables in the foreign key relationship must have similar data types, that is, the columns that can be converted to each other. For example, int and tinyint can, while int and char cannot.
Advantages of foreign keys: can make two tables associated, to ensure the data of 1 and achieve 1 some cascade operations;
The definition syntax of foreign keys:
The 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 with CREATE TABLE and ALTER TABLE. If CONSTRAINT symbol is not specified, MYSQL automatically generates a name.
ON DELETE and ON UPDATE represent event trigger restrictions, and parameters can be set:

RESTRICT (limits foreign key changes in appearance) CASCADE (with foreign key changes) SET NULL (null) SET DEFAULT (set default) NO ACTION (no action, default)


If a 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 of the child tables with existing or matching foreign key values, the final action depends on the ON UPDATE and ON DELETE options in the foreign key constraint definition. InnoDB supports 5 different actions. If ON DELETE or ON UPDATE are not specified, the default action is RESTRICT:

1. CASCADE: Delete or update the corresponding row from the parent table, and automatically delete or update the matching row from the table. ON DELETE CANSCADE and ON UPDATE CANSCADE are both supported by InnoDB.

2. SET NULL: Delete or update the corresponding row from the parent table, while leaving the foreign key columns in the child table empty. Note that these foreign key columns are valid only if they are not set to NOT NULL. ON DELETE SET NULL and ON UPDATE SET SET NULL are supported by InnoDB.

NO ACTION: InnoDB refuses to delete or update the parent table.

RESTRICT: Refuses to delete or update the parent table. The effect of specifying RESTRICT (or NO ACTION) and ignoring ON DELETE or ON UPDATE options is one.

5. SET DEFAULT: InnoDB is not currently supported.

The two situations in which foreign key constraints are most frequently used are:

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

2) When the parent table is updated, the child table is also updated, and when the parent table is deleted, the items matched in the child table are also deleted.

In the first case, in the foreign key definition, we use ON UPDATE CASCADE ON DELETE RESTRICT; In the latter case, ON UPDATE CASCADE ON DELETE CASCADE can be used.

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 remove foreign keys:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;


Related articles: