Use of MySQL foreign key (Foreign key)

Reprinted from: Http://www.cppblog.com/wolf/articles/69089.html#Post

The original text is too incisive and image, can't help reprint came to leave notes, like the author salute

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:

1  1 CREATE TABLE' Dage ' (2  2' ID 'int( One) not NULLAuto_increment,3  3' Name 'varchar( +)default "',4  4  PRIMARY KEY(' id ')5  5) ENGINE=InnoDBDEFAULTCHARSET=latin1;6  677CREATE TABLE' Xiaodi ' (8  8' ID 'int( One) not NULLAuto_increment,9  9' dage_id 'int( One)default NULL,Ten Ten' Name 'varchar( +)default "', One  One  PRIMARY KEY(' id '), A  A  KEY' dage_id ' (' dage_id '), -  -  CONSTRAINT' Xiaodi_ibfk_1 'FOREIGN KEY(' dage_id ')REFERENCES' dage ' (' id ') -  -) ENGINE=InnoDBDEFAULTCHARSET=Latin1;

Insert a Big Brother:

11mysql> Insert  intoDage (name)Values('Tongluowan');22Query OK,1Row affected (0.01sec)33mysql> Select *  fromdage;4 4+----+--------+5 5|Id|Name|6 6+----+--------+7 7|  1 |Causeway bay|8 8+----+--------+9  theRowinch Set(0.00Sec

Insert a little brother:

11mysql> Insert  intoXiaodi (Dage_id,name)Values(1,'Tongluowan _ little brother a');22Query OK,1Row affected (0.02sec)3 344mysql> Select *  fromXiaodi;5 5+----+---------+--------------+6 6|Id|dage_id|Name|7 7+----+---------+--------------+8 8|  1 |       1 |Tongluowan _ Little Brother a|9 9+----+---------+--------------+

Remove the eldest brother:

11mysql> Delete  fromDagewhereId=1;22ERROR1451(23000): CannotDelete or UpdateA parent Row:aForeign Key constraintFails (' 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:

11mysql> Insert  intoXiaodi (Dage_id,name)Values(2,'Mong Kok _ little brother a'); 22ERROR1452(23000): CannotAdd or UpdateA child row:aForeign Key constraintFails (' Bstar/Xiaodi ',CONSTRAINT' 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:

11mysql>ShowCreate TableXiaodi;2  23  3  CONSTRAINT' Xiaodi_ibfk_1 'FOREIGN KEY(' dage_id ')REFERENCES' dage ' (' id ')4  455mysql> Alter TableXiaodiDrop Foreign KeyXiaodi_ibfk_1;66Query OK,1Row affected (0.04sec)77Records:1Duplicates:0Warnings:88mysql> Alter TableXiaodiAdd Foreign Key(dage_id)ReferencesDage (ID) on Delete Cascade  on Update Cascade;99Query OK,1Row affected (0.04sec)Ten10Records:1Duplicates:0Warnings:0

Try to delete the eldest brother again:

11mysql> Delete  fromDagewhereId=1;22Query OK,1Row affected (0.01sec)3 344mysql> Select *  fromdage;55EmptySet(0.01sec)6 677mysql> Select *  fromXiaodi;88EmptySet(0.00Sec

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)