MySQL-Foreign Key

本文用到的参考链接:
外键约束-Using FOREIGN KEY Constraints

一、概念

1) parent & child

简单地说
有外键的是child表，被外键引用的就是parent表。
例如有这样两个表：
教室表: Classroom

id 教室编号，主键

课程表: Course

id 课程编号，主键
name 课程名称
classroom 教室编号，外键，取值为教室表中的值

那么这里教室表就是parent，而课程表就是child

2) 参照完整性

参照完整性就是对外键的约束，就是说外键的取值必须要在它的parent中存在，否则就不满足参照完整性。

例如要在Course表中插入一个记录，其中的教室编号根本就不存在，那么这就违反了参照完整性。
参照完整性在上面的参考链接中也有说明，具体在Referential Actions

二、操作

外键的各种操作都在参考链接中有详细说明，这里用例子对各个参数进行说明。

下面先创建一个简单的表parent，之后的外键都将引用这个表
parent

create table Parent(
id int primary key,
name varchar(20));

约束名symbol

对每个外键的约束都有一个约束名，可以显式设定也可以自动生成。
在我们对一个约束进行操作时(如删除或添加约束)都要用到约束名

未指定约束名
输入下面的语句，创建新表

create table Child1(
id int primary key,
name varchar(20),
pid int,
foreign key(id) references Parent(id));

然后查看刚刚创建的表的结构show create table Child1，可以看到自动生成的约束名(在constraint后面)

指定约束名
用constraint symbol指定约束名，例如将上面的foreign key(id)一行换成下面的
constraint id_fk foreign key(id) references Parent(id));
这样就指定了约束名为id_fk

添加删除外键

添加外键
添加外键使用下面的语法

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

示例：

mysql> create table Child2(
    -> id int primary key,
    -> name varchar(20),
    -> pid int);
Query OK, 0 rows affected (0.06 sec)
# 下面添加外键
mysql> alter table Child2
    -> add constraint pid_fk foreign key(pid) references Parent(id);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用show create table Child2可以看到外键的存在

删除外键

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

fk_symbol指的是约束名

示例：

alter table Child2
drop foreign key pid_fk;

关于参照完整性的操作ON DELETE, ON UPDATE

ON DELETE和 ON UPDATE都说的是当parent表发生变化（被删除或被更新）时，child表应该如何变化，这两个后面的可选参数是一样的，有下面几个：

CASCADE

级联操作，parent行删除，对应的child行删除；parent行更新，对应的child行更新

例如parent表内容为

+----+-------+
| id | name  |
+----+-------+
|  0 | Alice |
|  1 | Bob   |
|  2 | Cathy |
+----+-------+

创建child表，并插入数据

mysql> create table Child3(
    -> id int primary key,
    -> name varchar(20),
    -> pid int,
    -> foreign key (pid) references Parent(id) on delete cascade);
mysql> insert into Child3 values(
    -> 0, "a_child", 0);
mysql> insert into Child3 values(
    -> 1, "b_child", 1);
mysql> select * from Child3;
+----+---------+------+
| id | name    | pid  |
+----+---------+------+
|  0 | a_child |    0 |
|  1 | b_child |    1 |
+----+---------+------+

现在删除parent中的Alice项，child中的对应的项也会删除

mysql> delete from Parent where id=0;
mysql> select * from Parent;
+----+-------+
| id | name  |
+----+-------+
|  1 | Bob   |
|  2 | Cathy |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from Child3;
+----+---------+------+
| id | name    | pid  |
+----+---------+------+
|  1 | b_child |    1 |
+----+---------+------+
1 row in set (0.00 sec)

同样的，如果是用于ON UPDATE，parent更新，child也会更新，这里不举例了。

SET NULL

parent变化时，child相应的项设为空值

RESTRICT

这是默认选项，如果parent中的行更新之后会影响到child表，那么拒绝此操作

mysql> select * from Parent;
+----+-------+
| id | name  |
+----+-------+
|  1 | Bob   |
|  2 | Cathy |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into Child1 values(
    -> 1,"b_child",1);
Query OK, 1 row affected (0.01 sec)

mysql> delete from Parent where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydatabase`.`Child1`, CONSTRAINT `Child1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `Parent` (`id`))
mysql> drop table Parent;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

从上面看到，只有parent和child中的行有联系而又设定了RESTRICT的话，删除相应的行或删除表都是会报错的。

Note:当然如果一定要这样做，可以这样

SET foreign_key_checks = 0;
-- Drop tables
drop table ...
-- Drop views
drop view ...
SET foreign_key_checks = 1;

NO ACTION

这是一个来自SQL标准的关键字，在mysql中和上面的RESTRICT相同

SET DEFAULT

设置为默认值