【MySQL】FOREIGN KEY
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【MySQL】FOREIGN KEY，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8415字，纯文字阅读大概需要13分钟。
内容图文
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=‘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 = ‘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 = ‘ffff‘‘ at line 1 mysql> delete from roottb3 where rootdata = ‘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 = ‘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
标签：parent unsigned val ott prim ddd list style use
本文系统来源：http://www.cnblogs.com/AlexBai326/p/6275396.html
内容总结
以上是互联网集市为您收集整理的【MySQL】FOREIGN KEY全部内容，希望文章能够帮你解决【MySQL】FOREIGN KEY所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】