A simple learning tutorial on Mysql foreign key _mysql

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:
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 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)

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 operation. The final action depends on the on update and on delete options in the FOREIGN KEY constraint definition if the parent table attempts to update or delete a foreign key value that exists or matches in any of the child tables. 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 rows from the parent table, and automatically deletes or updates the rows that are matched from the table. The 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 null. Note that these foreign key columns are not valid until they are set to NOT NULL. 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. The effect of specifying restrict (or no ACTION) and ignoring on delete or on update options is the same.

5. SET Default:innodb is not currently supported.

There are no exceptions to the two situations where foreign key constraints are used most:

1 The child table is also updated when the parent table is updated, and deletion fails if the child table has a matching entry when the parent table is deleted;

2 The child table is also updated when the parent table is updated, and the child table matching items are deleted when the parent table is deleted.

In the previous case, in the foreign key definition, we used the on Update CASCADE on delete RESTRICT, and in the latter case, the on update CASCADE to delete CASCADE.

InnoDB allows you to use ALTER TABLE to add a new foreign key to an already 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;