Use of MYSQL Foreign Key

MySQL 3.23.44 and later versions support foreign key constraints for InnoDB Engine tables.
Usage conditions of foreign keys:
1. the two tables must be InnoDB tables, and MyISAM tables do not currently support foreign keys (it is said that later versions may support them, but at least they are currently not supported); 2. the foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when a foreign key is created. However, an index must be created in an earlier version. 3. the columns of the foreign key relationship must be of similar data types, that is, columns that can be converted to each other. For example, int and tinyint can be used, but int and char cannot; advantages of the foreign key www.2cto.com: two tables can be associated to ensure data consistency and perform cascade operations. The foreign key definition Syntax: [CONSTRAINT symbol] foreign key [id] (index_col_name ,...) REFERENCES tbl_name (index_col_name ,...) [on delete {RESTRICT | CASCADE | set null | no action | SET DE FAULT}] [on update {RESTRICT | CASCADE | set null | no action | set default}] This syntax can be used in create table and alter table. If you do not specify CONSTRAINT symbol, MYSQL automatically generates a name. On delete and on update indicate the event trigger limit. You can SET the parameter: RESTRICT (RESTRICT foreign key changes in the external table) CASCADE (Follow foreign key changes) set null (set null) set default (SET the DEFAULT value) no action (no action, DEFAULT) www.2cto.com for an example, a simple demonstration of the use, do dage and xiaodi two tables, Big Brother table is the primary key, younger brother TABLE is foreign key: TABLE creation: 1 create table 'dage' (2 'id' int (11) not null auto_increment, 3 'name' varchar (32) default '', 4 primary key ('id') 5) ENGINE = InnoDB default charset = latin1; 6 7 create table 'xiaodi '(8 'id' int (11) not null auto_increment, 9 'dage _ id' int (11) default NULL, 10 'name' varchar (32) default '', 11 primary key ('id '), 12 KEY 'dage _ id' ('dage _ id'), 13 CONSTRAINT 'xiaodi _ ibfk_1 'foreign key ('dage _ id') REFERENCES 'dage' ('id ') 14) ENGINE = InnoDB default charset = latin1; insert a Eldest Brother: 1 mysql> insert into dage (name) values ('causeway Bay '); 2 Query OK, 1 row affected (0.01 sec) 3 mysql> select * from dage; 4 + ---- + -------- + 5 | id | name | 6 + ---- + -------- + 7 | 1 | Causeway Bay | 8 + ---- + -------- + 91 row in set (0.00 sec) www.2cto.com insert younger brother: 1 mysql> insert into xiaodi (dage_id, name) values (1, 'causeway Bay _ younger brother A'); 2 Query OK, 1 row affected (0.02 sec) 34 mysql> select * from xiaodi; 5 + ---- + --------- + ------------ + 6 | id | dage_id | name | 7 + ---- + --------- + -------------- + 8 | 1 | 1 | Causeway Bay _ younger brother A | 9 + ---- + --------- + -------------- + Delete the eldest brother: 1 mysql> delete from dage where id = 1; 2 ERROR 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') www.2cto.com prompt: No, there are constraints, there are still younger brothers under the eldest brother, you can not leave us alone! Insert A new younger brother: 1 mysql> insert into xiaodi (dage_id, name) values (2, 'mong Kok _ A '); 2 ERROR 1452 (23000 ): cannot add or update a child row: a foreign key constraint fails ('bstar/xiaodi ', CONSTRAINT 'xiaodi _ ibfk_1' foreign key ('dage _ id ') REFERENCES 'dage' ('id') 3. Tips: I want to rebel! You have no big brother yet! Add the foreign key constraint to the event trigger limit: 1 mysql> show create table xiaodi; 2... 3 CONSTRAINT 'xiaodi _ ibfk_1 'foreign key ('dage _ id') REFERENCES 'dage' ('id') 4... 5 mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1; 6 Query OK, 1 row affected (0.04 sec) 7 Records: 1 Duplicates: 0 Warnings: 8 mysql> alter table xiaodi add foreign key (dage_id) references dage (id) on delete cascade on update cascade; 9 Query OK, 1 row affec Ted (0.04 sec) 10 Records: 1 Duplicates: 0 Warnings: 0 try again to delete the eldest brother: 1 mysql> delete from dage where id = 1; 2 Query OK, 1 row affected (0.01 sec) 3 www.2cto.com 4 mysql> select * from dage; 5 Empty set (0.01 sec) 67 mysql> select * from xiaodi; 8 Empty set (0.00 sec) yes, this time the corresponding younger brother is gone. No way. Who asked you to talk to me on delete cascade! The example should be quite clear. practice other functions in the corresponding manual! :-) Old wolf