53.笔记 MySQL学习——外键和引用完整性
利用外键关系,可以再一个表里声明与另一个表里的某个索引相关联的索引。
数据库会根据关系里的规则来维护数据引用的完整性。
外键在行的插入操作中很有用,在删除和更新操作中也有用处。例如:级联删除、级联更新。
外键可以帮我们维护数据的一致性。
在MYSQL中,INNODB引擎提供了对外键的支持。
父表:包含原始键值的表
子表:引用了父表中键值的相关表
子表里的索引会引用父表里的索引。子表的索引值必须与父表中的索引值相匹配;或者被设置为NULL,以表明在父表里不存在与之对应的行。
InnoDB存储引擎关注外键定义如下:
CONSTRAINT : 为外键约束提供一个名字
FOREIGN KEY: 列出子表里的索引列,列必须与父表里的索引值相匹配。
REFERENCES:列出父表及其索引列的名字,让子表里的外键可以引用它们
ON DELETE: 指定在删除父表的行时,子表应该做什么。(默认,拒绝从父表里删除仍被子表的行所引用的那些行)
ON UPDATE:指定当父表更新时候,子表应该做什么
子表必须建立索引,且外键列需要放在首位。父表也必须建立索引,且REFERENCES子句里的列需要放在首位。
父表和子表索引里的对应列必须类型兼容。
不能对外键关系里的字符串列的前缀进行索引。
例如如下:
创建表:
mysql> create table parent ( par_id int notnull,primary key(par_id)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table child ( par_id int notnull,child_id int not null,primary key (par_id,child_id),foreign key (par_id)references parent (par_id) on delete cascade on update cascade) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
其中ON DELETE CASCADE和ON UPDATE CASCADE表示,父表删除和更新会级联到子表。
插入到父表:
mysql> insert into parent (par_id) values(1),(2),(3);
插入到子表:
mysql> insert into child ( par_id,child_id) values(1,1),(1,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into child ( par_id,child_id) values(2,1),(2,2),(2,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into child ( par_id,child_id) values(3,1);
Query OK, 1 row affected (0.00 sec)
查询如下:
mysql> select * from parent;
+--------+
| par_id |
+--------+
| 1 |
| 2 |
| 3 |
+--------+
3 rows in set (0.00 sec)
mysql> select * from child;
+--------+----------+
| par_id | child_id |
+--------+----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
+--------+----------+
6 rows in set (0.00 sec)
在子表中插入一个在父表不存在的行如下:
mysql> insert into child (par_id,child_id) values(4,1);
ERROR 1452 (23000): Cannot add or update achild row: a foreign key constraint fails (`sampdb`.`child`, CONSTRAINT`child_ibfk_1` FOREIGN KEY (`par_id`) REFERENCES `parent` (`par_id`) ON DELETECASCADE ON UPDATE CASCADE)
出现了报错。
从父表删除一行
mysql> delete from parent where par_id=1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from parent;
+--------+
| par_id |
+--------+
| 2 |
| 3 |
+--------+
2 rows in set (0.00 sec)
mysql> select * from child;
+--------+----------+
| par_id | child_id |
+--------+----------+
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
+--------+----------+
4 rows in set (0.00 sec)
更新父表:
mysql> update parent set par_id=100 wherepar_id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from parent;
+--------+
| par_id |
+--------+
| 3 |
| 100 |
+--------+
2 rows in set (0.00 sec)
mysql> select * from child;
+--------+----------+
| par_id | child_id |
+--------+----------+
| 3 | 1 |
| 100 | 1 |
| 100 | 2 |
| 100 | 3 |
+--------+----------+
4 rows in set (0.00 sec)
发现子表也会更新
这是因为设置了ON UPDATE CASCADE
可以使用SHOW CREATE TABLE语句来查看表有哪些外键关系。