MYSQL foreign key (ForeignKey)
BitsCN.com
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; benefits of foreign keys: two tables can be associated to ensure data consistency and perform cascade operations. The definition syntax of foreign keys 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 U PDATE {RESTRICT | CASCADE | set null | no action | set default}] this syntax can be used in create table and alter table, 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 default value) no action (no action, DEFAULT value) is used as an example. it is a simple demonstration to use dage and xiaodi tables. the eldest 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) insert A younger brother: 1 mysql> insert into xiaodi (dage_id, name) values (1, 'Causeway Bay _ younger brother '); 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') tip: No, there are constraints. there are still younger siblings under the eldest brother. you can't 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 4 mysql> select * from dage; 5 Empty set (0.01 sec) 67 mysql> select * from xiaodi; 8 Empty set (0.00 sec, 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! :-) Author: Old Wolf bitsCN.com
