首页 > 代码库 > 【MySQL】FOREIGN KEY
【MySQL】FOREIGN KEY
1
1
- FOREIGN KEY reference PRIMARY KEY
CREATE TABLE `roottb` ( `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, `data` VARCHAR(100) NOT NULL DEFAULT ‘‘, PRIMARY KEY (`id`) ) ENGINE =InnoDB; CREATE TABLE `subtb` ( `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, `rootid` INT(11) UNSIGNED NOT NULL DEFAULT ‘0‘, `data` VARCHAR(100) NOT NULL DEFAULT ‘‘, PRIMARY KEY (`id`), INDEX (`rootid`), FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE ) ENGINE =InnoDB;
插入数据，sub表的rootid使用root表中id的值
mysql> INSERT INTO `roottb` (`id`,`data`) -> VALUES (‘1‘, ‘test root line 1‘), -> (‘2‘, ‘test root line 2‘), -> (‘3‘, ‘test root line 3‘); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO `subtb` (`id`,`rootid`,`data`) -> VALUES (‘1‘, ‘1‘, ‘test sub line 1 for root 1‘), -> (‘2‘, ‘1‘, ‘test sub line 2 for root 1‘), -> (‘3‘, ‘1‘, ‘test sub line 3 for root 1‘), -> (‘4‘, ‘2‘, ‘test sub line 1 for root 2‘), -> (‘5‘, ‘2‘, ‘test sub line 2 for root 2‘), -> (‘6‘, ‘2‘, ‘test sub line 3 for root 2‘), -> (‘7‘, ‘3‘, ‘test sub line 1 for root 3‘), -> (‘8‘, ‘3‘, ‘test sub line 2 for root 3‘), -> (‘9‘, ‘3‘, ‘test sub line 3 for root 3‘); Query OK, 9 rows affected (0.01 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from roottb; +----+------------------+ | id | data | +----+------------------+ | 1 | test root line 1 | | 2 | test root line 2 | | 3 | test root line 3 | +----+------------------+ 3 rows in set (0.00 sec) mysql> select * from subtb; +----+--------+----------------------------+ | id | rootid | data | +----+--------+----------------------------+ | 1 | 1 | test sub line 1 for root 1 | | 2 | 1 | test sub line 2 for root 1 | | 3 | 1 | test sub line 3 for root 1 | | 4 | 2 | test sub line 1 for root 2 | | 5 | 2 | test sub line 2 for root 2 | | 6 | 2 | test sub line 3 for root 2 | | 7 | 3 | test sub line 1 for root 3 | | 8 | 3 | test sub line 2 for root 3 | | 9 | 3 | test sub line 3 for root 3 | +----+--------+----------------------------+ 9 rows in set (0.00 sec)
插入数据，sub表的rootid使用非root表中id的值
mysql> INSERT INTO `subtb` (`id`,`rootid`,`data`) -> VALUES (‘10‘, ‘4‘, ‘test sub line 1 for root 4 not existed‘); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`subtb`, CONSTRAINT `subtb_ibfk_1`
FOREIGN KEY (`rootid`) REFERENCES `roottb` (`id`) ON DELETE CASCADE) mysql>
- FOREIGN KEY reference UNIQUE KEY not primary
CREATE TABLE roottb3 ( id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, rootdata varchar(100) NOT NULL, PRIMARY KEY (id), UNIQUE KEY(rootdata) )ENGINE=InnoDB; CREATE TABLE subtb3 ( id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, subdata varchar(100) NOT NULL , PRIMARY KEY (id), INDEX (subdata), FOREIGN KEY (`subdata`) REFERENCES roottb3(`rootdata`) ON DELETE CASCADE on update CASCADE )ENGINE =InnoDB; INSERT INTO `roottb3` (`id`,`rootdata`) VALUES (‘1‘, ‘aaa‘), (‘2‘, ‘bbb‘), (‘3‘, ‘ccc‘); INSERT INTO `subtb3` (`id`,`subdata`) VALUES (‘1‘, ‘aaa‘), (‘2‘, ‘aaa‘), (‘3‘, ‘aaa‘), (‘4‘, ‘bbb‘), (‘5‘, ‘bbb‘), (‘6‘, ‘bbb‘), (‘7‘, ‘ccc‘), (‘8‘, ‘ccc‘), (‘9‘, ‘ccc‘);
- FOREIGN KEY -- ON DELETE CASCADE on update CASCADE
- on update CASCADE
mysql> select * from roottb3; +----+----------+ | id | rootdata | +----+----------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+----------+ 3 rows in set (0.00 sec) mysql> select * from subtb3; +----+---------+ | id | subdata | +----+---------+ | 1 | aaa | | 2 | aaa | | 3 | aaa | | 4 | bbb | | 5 | bbb | | 6 | bbb | | 7 | ccc | | 8 | ccc | | 9 | ccc | +----+---------+ 9 rows in set (0.00 sec) mysql> mysql> update roottb3 set rootdata=http://www.mamicode.com/‘ddd‘ where id =3; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from roottb3; +----+----------+ | id | rootdata | +----+----------+ | 1 | aaa | | 2 | bbb | | 3 | ddd | +----+----------+ 3 rows in set (0.00 sec) mysql> select * from subtb3; +----+---------+ | id | subdata | +----+---------+ | 1 | aaa | | 2 | aaa | | 3 | aaa | | 4 | bbb | | 5 | bbb | | 6 | bbb | | 7 | ddd | | 8 | ddd | | 9 | ddd | +----+---------+ 9 rows in set (0.00 sec)
- ON DELETE CASCADE
mysql> delete from roottb3 where id=2; Query OK, 1 row affected (0.01 sec) mysql> select * from roottb3; +----+----------+ | id | rootdata | +----+----------+ | 1 | aaa | | 3 | ddd | +----+----------+ 2 rows in set (0.00 sec) mysql> select * from subtb3; +----+---------+ | id | subdata | +----+---------+ | 1 | aaa | | 2 | aaa | | 3 | aaa | | 7 | ddd | | 8 | ddd | | 9 | ddd | +----+---------+ 6 rows in set (0.00 sec) mysql>
3. delete subtb data
mysql> select * from subtb3; +----+---------+ | id | subdata | +----+---------+ | 1 | aaa | | 2 | aaa | | 3 | aaa | | 7 | ddd | | 8 | ddd | | 9 | ddd | +----+---------+ 6 rows in set (0.00 sec) mysql> delete from subtb3 where id=9; Query OK, 1 row affected (0.03 sec) mysql>
4. insert and delete the data of roottb that not used by subtable
mysql> select * from roottb3; +----+----------+ | id | rootdata | +----+----------+ | 1 | aaa | | 3 | ddd | +----+----------+ 2 rows in set (0.00 sec) mysql> INSERT INTO `roottb3` (`id`,`data`) VALUES (‘5‘, ‘eeee‘),(‘6‘, ‘ffff‘); ERROR 1054 (42S22): Unknown column ‘data‘ in ‘field list‘ mysql> INSERT INTO `roottb3` (`id`,`rootdata`) VALUES (‘5‘, ‘eeee‘),(‘6‘, ‘ffff‘); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from roottb3; +----+----------+ | id | rootdata | +----+----------+ | 1 | aaa | | 3 | ddd | | 5 | eeee | | 6 | ffff | +----+----------+ 4 rows in set (0.00 sec) mysql> delete from table roottb3 where rootdata =http://www.mamicode.com/ ‘ffff‘; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near ‘table roottb3 where rootdata = http://www.mamicode.com/‘ffff‘‘ at line 1 mysql> delete from roottb3 where rootdata =http://www.mamicode.com/ ‘ffff‘; Query OK, 1 row affected (0.01 sec) mysql> select * from roottb3; +----+----------+ | id | rootdata | +----+----------+ | 1 | aaa | | 3 | ddd | | 5 | eeee | +----+----------+ 3 rows in set (0.00 sec) mysql>
- if without ON DELETE CASCADE on update CASCADE
CREATE TABLE roottb4 ( id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, rootdata varchar(100) NOT NULL, PRIMARY KEY (id), UNIQUE KEY(rootdata) )ENGINE=InnoDB; CREATE TABLE subtb4 ( id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, subdata varchar(100) NOT NULL , PRIMARY KEY (id), INDEX (subdata), FOREIGN KEY (`subdata`) REFERENCES roottb4(`rootdata`) )ENGINE =InnoDB; INSERT INTO `roottb4` (`id`,`rootdata`) VALUES (‘1‘, ‘aaa‘), (‘2‘, ‘bbb‘), (‘3‘, ‘ccc‘); INSERT INTO `subtb4` (`id`,`subdata`) VALUES (‘1‘, ‘aaa‘), (‘2‘, ‘aaa‘), (‘3‘, ‘aaa‘), (‘4‘, ‘bbb‘), (‘5‘, ‘bbb‘), (‘6‘, ‘bbb‘), (‘7‘, ‘ccc‘), (‘8‘, ‘ccc‘), (‘9‘, ‘ccc‘);
result
mysql> mysql> select * from roottb4; +----+----------+ | id | rootdata | +----+----------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+----------+ 3 rows in set (0.00 sec) mysql> select * from subtb4; +----+---------+ | id | subdata | +----+---------+ | 1 | aaa | | 2 | aaa | | 3 | aaa | | 4 | bbb | | 5 | bbb | | 6 | bbb | | 7 | ccc | | 8 | ccc | | 9 | ccc | +----+---------+ 9 rows in set (0.00 sec) mysql> mysql> mysql> update roottb4 set rootdata = http://www.mamicode.com/‘ddd‘ where id = 3; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
fails (`test`.`subtb4`, CONSTRAINT `subtb4_ibfk_1` FOREIGN KEY (`subdata`) REFERENCES `roottb4` (`rootdata`)) mysql> mysql> delete from roottb4 where id =3; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
fails (`test`.`subtb4`, CONSTRAINT `subtb4_ibfk_1` FOREIGN KEY (`subdata`) REFERENCES `roottb4` (`rootdata`)) mysql>
【MySQL】FOREIGN KEY
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。