Use of MySQL foreign key (Foreign key)

After MySQL version 3.23.44, the InnoDB Engine type table supports foreign KEY constraints.
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, ...)
[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 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-triggering limit, and can be set with parameters:
RESTRICT (Restrict foreign key changes in appearance)
CASCADE (following foreign key changes)
SET NULL (null value set)
Set Default (set defaults)
No action (no action, default)

For example, a simple demonstration of using, 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 ',
4 PRIMARY KEY (' id ')
5) Engine=innodb DEFAULT charset=latin1;
6
7CREATE TABLE ' Xiaodi ' (
8 ' id ' int (one) not NULL auto_increment,
9 ' dage_id ' int (one) default NULL,
' name ' varchar (+) default ',
PRIMARY KEY (' id '),
KEY ' 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 ');
2Query OK, 1 row affected (0.01 sec)
3mysql> SELECT * from Dage;
4+----+--------+
5| ID | name |
6+----+--------+
7| 1 | Tongluowan |
8+----+--------+
(0.00 sec)

Insert a little brother:

1mysql> INSERT INTO Xiaodi (dage_id,name) VALUES (1, ' Tongluowan _ younger brother a ');
2Query OK, 1 row affected (0.02 sec)
3
4mysql> SELECT * from Xiaodi;
5+----+---------+--------------+
6| ID | dage_id | name |
7+----+---------+--------------+
8|       1 | 1 | Tongluowan _ Little Brother a |
+ ----+---------+--------------+

Remove the eldest brother:

1mysql> Delete from dage where id=1;
2ERROR 1451 (23000): Cannot delete or update a parent ROW:A FOREIGN KEY constraint 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 ');
2ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails (' Bstar/xiaodi ', constraint ' xia Odi_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
3 CONSTRAINT ' xiaodi_ibfk_1 ' FOREIGN KEY (' dage_id ') REFERENCES ' dage ' (' id ')
4
5mysql> ALTER TABLE xiaodi drop foreign key xiaodi_ibfk_1;
6Query OK, 1 row affected (0.04 sec)
7records:1 duplicates:0 Warnings:
8mysql> ALTER TABLE Xiaodi add foreign key (dage_id) references dage (ID) on the DELETE cascade on UPDATE cascade;
9Query OK, 1 row affected (0.04 sec)
10records:1 duplicates:0 warnings:0

Try to delete the eldest brother again:

1mysql> Delete from dage where id=1;
2Query OK, 1 row affected (0.01 sec)
3
4mysql> SELECT * from Dage;
5Empty Set (0.01 sec)
6
7mysql> SELECT * from Xiaodi;
8Empty Set (0.00 sec)

have, this time corresponding brother also no, no way, who let you with me on DELETE cascade!

The example should be quite clear, other functions corresponding to the manual practice it! :-)

Use of MySQL foreign key (Foreign key)