53.笔记 MySQL学习——外键和引用完整性

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语句来查看表有哪些外键关系。