MySQL foreign key usage and description (easy to understand)

MySQL foreign key usage and description

First, FOREIGN KEY constraints

MySQL uses foreign key constraints to ensure the integrity and accuracy of data between tables.

use conditions for foreign keys:
1. Two tables must be a InnoDB table, the MyISAM table temporarily does not support foreign keys (it is said that later versions may be supported, but at least not currently supported);
2. The foreign key column must be indexed, MySQL 4.1.2 later version will automatically create the index when the foreign key is established, but if the earlier version needs to display the establishment;
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, and int and char are not allowed;

The advantage of foreign key: can make two tables association, ensure the consistency of data and realize some cascade operation;

Definition syntax for foreign keys:

  [CONSTRAINT symbol] FOREIGN KEY [ID] (index_col_name, ...)    REFERENCES tbl_name (index_col_name, ...)    | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]    | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] This syntax can be used when creating table and ALTER table, and if you do not specify constraint Symbol,mysql automatically generates a name. On DELETE, the on update indicates the event trigger limit, can be set parameters: RESTRICT (limit foreign key changes in the appearance) CASCADE (following foreign key changes) set NULL (set NULL) set default (set defaults) No action (no action, Default)

Syntax Explanation

A simple demo to use, do dage and xiaodi two tables, Big Brother table is the primary key, the younger brother table is a foreign key

Build table:

1CREATE TABLE ' dage ' (2`ID`int( One) not NULL auto_increment,3' Name ' varchar ( +) Default"', 4PRIMARY KEY ('ID`)) 5Engine=innodb DEFAULT charset=latin1;67CREATE TABLE ' Xiaodi ' (8`ID`int( One) not NULL auto_increment,9' dage_id 'int( One) Default NULL,Ten' Name ' varchar ( +) Default"', OnePRIMARY KEY ('ID`), AKEY ' dage_id ' (' dage_id '), -CONSTRAINT ' Xiaodi_ibfk_1 ' FOREIGN KEY (' dage_id ') REFERENCES ' dage ' ('ID`) -) Engine=innodb DEFAULT charset=latin1;

Insert a Big Brother:

1mysql> INSERT into Dage (name) VALUES (' Tongluowan '1 row affected (0.01  sec) 3mysqlSelect * from dage; 4+----+--------+5ID | Name   |  6+----+--------+7|  1 | Tongluowan | 8+----+--------+ in Set (0.00 sec)

Insert a little brother:

1mysql> INSERT into Xiaodi (dage_id,name) VALUES (1,'Tongluowan _ little brother a'); 2Query OK,1Row affected (0.02sec)34mysql>Select*From Xiaodi;5+----+---------+--------------+6|ID| dage_id | name |7+----+---------+--------------+8|1|1| Tongluowan _ Little Brother a |9+----+---------+--------------+

Remove the eldest brother:

id=11451 (23000): Cannot delete or update a parent row:a foreign key Constra int fails (' Bstar/xiaodi ', CONSTRAINT ' Xiaodi_ibfk_1 ' FOREIGN KEY (' dage_id ') REFERENCES ' dage ' ('ID'))

hint: No, there is restraint, Big Brother below also have younger brother, can not leave us no matter !

Insert a new little brother:

1mysql> INSERT into Xiaodi (dage_id,name) VALUES (2,' Mong Kok _ younger brother a')              ; 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails (' Bstar/xiaodi ', Constrai NT ' Xiaodi_ibfk_1 ' FOREIGN KEY (' dage_id ') REFERENCES ' dage ' ('ID'))3

Hint: Boy, want to rebel! You don't have a big brother yet!

Increase the event trigger limit for foreign KEY constraints:

1mysql>Show CREATE table Xiaodi;2 3CONSTRAINT ' Xiaodi_ibfk_1 ' FOREIGN KEY (' dage_id ') REFERENCES ' dage ' ('ID`) 45mysql>ALTER TABLE Xiaodi drop foreign key xiaodi_ibfk_1; 6Query OK,1Row affected (0.04sec) 7Records:1Duplicates:0Warnings:8mysql> ALTER TABLE Xiaodi add foreign key (dage_id) references Dage (ID) on the DELETE cascade on UPDATE cascade; 9Query OK,1Row affected (0.04sec) 10Records:1Duplicates:0Warnings:0

Try to delete the eldest brother again:

id=11 row affected (0.01  sec)34mysql  Select * from dage;5empty set (0.01  sec)67mysqlSelect * from xiaodi;8empty set (0.00 sec)

Ah yo, this time the corresponding brother also no, no way, who let you and I on DELETE cascade (cascading restrictions)!

MySQL foreign key usage and description (easy to understand)