mysql 外键_MySQL外键相关的扩展

外键：

在实际开发的项目中，一个健壮数据库中的数据一定有很好的参照完整性。如：有学生档案和成绩单两张表，如果成绩单中有张三的成绩，学生档案中张三的档案却被删除了，这样就会产生垃圾数据或者错误数据。为了保证数据的完整性，将两表之间的数据建立关系，因此就需要在成绩表中添加外键约束。

什么是外键：

外键是指引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。

create database chapter05 charset="utf8";

use chapter05;

create table grade(

id int(4) not null PRIMARY KEY auto_increment,

name varchar(36)

);

create table student(

  sid int(4) not null PRIMARY KEY auto_increment,

  sname varchar(36),

  gid int(4) not null

);

首先创建两个表：一个班级表(grade)和一个学生表(student)。

学生表(student)中的gid是学生所在的班级id，是引入了班级表(grade)中的主键id，那么gid就可以作为表student的外键。被引用的表，即表grade是主表，引用外键的表，即表student是从表，两个表是主从关系。表student用gid可以连接表grade中的信息，从而建立了两个表数据之间的连接。

引入外键后，外键列只能插入参照列存在的值。参照列被参照的值不能被删除，这就保证了数据的参照完整性。

为表添加外键约束：

grade表中的id为主键，student表中的gid为外键。但是结果中不能明确地看出两个表之间的关系。可以使用下面的方式进行查看：

从查询结果可以看出：gid为student表的外键，并且gid外键依赖于grade表中的id主键，这样两个表就通过外键关联起来了。

在为表添加外键约束时，有些需要注意的地方：

1、建立外键的表必须是Innodb型，不能是临时表。因为在msql中只有innodb类型的表才支持外键。

2、定义外键名时，不能加引号，如constraint‘fk_id’或constraint “fk_id”都是错误的。

3、添加外键的时候，也可以在创建表的时候进行添加。

删除外键约束：

从查询结果可以看出：表student中的外键约束已经被成功删除。

多学一招：添加外键约束的参数说明。

我们知道了建立外键是为了保证数据的完整和统一性，但如果主表中的数据被删除或修改，从表中对应的数据该怎么办？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。mysql可以在建立外键时添加on delete或on update子句来告诉数据库，怎么样避免垃圾数据的产生。

alter table表名 add constraint fk_id foreign key (外键字段名) references 外表表名(主键字段名);

[on delete {cascade | set null | no action | restrict}]

[on update {cascade | set null | no action | restrict}]

cascade：删除包含与已删除键值有参照关系的所有记录。

set null：修改包含与已删除键值有参照关系的所有记录，使用null值替换(不能用于已标记为not null的字段)

no action：不进行任何操作

restrict：拒绝主表删除或修改外键关联列。(在不定义on delete和on update子句时，这是默认设置，也是最安全的设置)

存储引擎只能是InnoDB，因为只有这种存储模式才支持外键。

外键约束条件有以下4种：

no action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 这个是ANSI SQL-92标准,从mysql4.0.8开始支持；

         --无参照完整性关系，有了也不生效。

restrict方式：同no action，都是立即检查外键约束；

- - 限制，指的是如果子表引用父表的某个字段的值，那么不允许直接删除父表的该值。

set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null 要注意子表的外键列不能为not null。  On delete set null从mysql3.23.50开始可用；，on update set null从mysql4.0.8开始可用。

cascade方式：在父表上update/delete记录时，同步update/delete子表的匹配记录； On delete cascade从mysql3.23.50开始可用，on update cascade从mysql4.0.8开始可用 ；

         -- 级联，删除/更新父表的某条记录，子表中引用该值的记录会自动被删除/更新。

外键约束(On Delete和On Update)都有Restrict，No Action, Cascade,Set Null属性。

外键约束1--ON DELETE

外键约束2--ON UPDATE

注：NO ACTION和RESTRICT的区别：只有在及个别的情况下会导致区别，前者是在其他约束的动作之后执行，后者具有最高的优先权执行。

案例演示：

1、级联方式cascade

首先：创建表，并插入两条记录：

create table t_group(id int auto_increment primary key, name varchar(20))engine=InnoDB;

insert into t_group values (1,'Group_1'),(2,'Group_2');

创建：级联子表：

create table t_user(id int not null primary key, name varchar(10), groupid int, foreign key(groupid) references t_group(id) on delete cascade on update cascade)engine=InnoDB;

insert into t_user values (1,'dayu',1),(2,'duoduo',2);

实体完整性测试，不能插入重复主键值

参照完整性测试，不能插入在主表中不存在的外键值

级联测试：

主表中更新一个记录：

总结：改变主表数据，从表数据随着更新。

主表中删除一个记录：

总结：子表中对应的记录自动被删除。

注意：删除表的时候，要先删除从表，再来删除主表。

2、置空模式(set null，主表记录被删除，从表中对应的值设置为NULL)

首先：创建表，并插入两条记录：

create table t_group(id int auto_increment primary key, name varchar(20))engine=InnoDB;

insert into t_group values (1,'Group_1'),(2,'Group_2');

创建：置空子表：

create table t_user_1(id int not null primary key, name char(10),groupid int, foreign key(groupid) references t_group(id) on delete set null on update set null)engine=InnoDB;

insert into t_user_1 values (1,'dayu',1),(2,'duoduo',2),(3,'huanhuan',2),(4,'maiqi',1);

置空测试：

主表中更新一个记录：

总结：子表中对应的属性值被自动设置为NULL。

主表中删除一个记录：

总结：子表中对应的属性值被自动设置为NULL。

注意：删除表的时候，要先删除从表，再来删除主表。

3、禁止模式(no action/restrict)，如果在子表中有引用，则不允许在主表中进行更新或删除

首先：创建表，并插入两条记录：

create table t_group(id int auto_increment primary key, name varchar(20))engine=InnoDB;

insert into t_group values (1,'Group_1'),(2,'Group_2');

创建：禁止模式子表：

create table t_user_2(id int not null primary key, name char(10),groupid int, foreign key(groupid) references t_group(id) on delete no action on update restrict)engine=InnoDB;

insert into t_user_2 values (1,'dayu',1),(2,'duoduo',2),(3,'huanhuan',2),(4,'maiqi',1);

禁止模式测试：

主表中更新一个记录：

主表中删除一个记录：

总结：如果在子表中有引用，则不允许在主表中进行更新或删除。

注意：删除表的时候，要先删除从表，再来删除主表。

总结：主从表的删除

如果存在引用的从表，则主要不能随意删除：

mysql> drop table t_group;

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

删除从表没有限制：

mysql> drop table t_user_2;

Query OK, 0 rows affected (0.01 sec)

经过以上测试，了解到MySQL通过外键约束实现了数据的完整性与一致性。

操作关联表：

关联关系：

在实际开发中，需要根据实体的内容设计数据表，实体间会有各种关联关系。所以，根据实体设计的数据表之间也存在着各种关联关系。

多对一：

多对一是数据表中最常见的一种关系。如：员工与部门之间的关系，一个部门可以有多个员工，而一个员工不能属于多个部门，也就是说部门表中的一行在员工表中可以有许多匹配行，但员工表中的一行在部门表中只能有一个匹配行。

表之间的关系是通过外键建立的，在多对一的表关系中，应该将外键键在多的一方，否则会造成数据的冗余。

多对多：

多对多也是数据表中的一种关系。如：学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程也供多个学生选择，也就是说学生表中的一行在课程表中可以有许多匹配行，课程表中的一行在学生表中也有许多匹配行。

通常情况下，为了实现这种关系需要定义一张中间表(称为连接表)，该表会存在两个外键，分别参照课程表和学生表。在多对多的关系中，需要注意的是，连接表的两个外键都是可以重复的，但是两个外键之间的关系是不能重复的，所以这两个外键又是连接表的联合主键。

一对一：

一对一关系在实际生活中比较常见，如：人与身份证之间就是一对一的有关系，一个人对应一张身份证，一张身份证只能匹配一个人。那么，一对一关系的两张表如何建立外键？

首先，要分清主从关系。从表需要主表的存在才有意义，身份证需要人的存在才会意义。因此人为主表，身份证为从表。要在身份证表中建立外键。由实际经验可知，身份证中的外键必须是非空唯一的，因此，通常会直接用从表(表身份证)中的主键作为外键。

需要注意的是：这种关系在数据库中并不常见，因为以这种方式存储的信息通常会放在一个表中。在实际开发中，一对一关联关系可以应用于以下几方面：

1．分割具有很多列的表。

2．由于安全原因而隔离表的一部分。

3．保存临时的数据，并且可以毫不费力地通过删除该表而删除这些数据。

添加数据：

在实际开发中，最常见的关联关系就是多对一关系。

在student表和grade表中添加外键约束来建立两个表的关联关系：

从查询结果可以看出：student表的外键约束已经成功添加。此时，表student和表grade之间是多对一的关系。因为，外键列只能插入参照列存在的值，所以，如果要为两个表添加数据，就需要先为主表grade添加数据。

添加的主键id为1和2，由于student表的外键与grade表的主键关联，因此，在为student表添加数据时，gid的值只能是1或2，不能使用其他的值。

这样，这两个表之间的数据就具有关联性。假如要查询class001有哪些学生，首先，需要查询class001班的id，然后，再根据这个id在student表中查询该班级有哪些学生。

在grade表中查询出班级名称为”class001”的id：

在student表中，查询gid=1的学生，即为class001班的学生。

通过子查询来实现该功能(查询班级为class001的都有哪些学生)：

select sname from student where gid in (select id from grade where name=”class001”)

select sname from student where gid = any (select id from grade where name=”class001”)

select sname from student where gid = some (select id from grade where name=”class001”)

删除数据：

如：学校的class001班取消了，就需要在数据库中将该班级以及该班级的学生一起删除。由于grade表和student表之间具有关联关系。参照列被参照的值是不能被删除的，因此，在删除class001班时，一定要先删除该班级的所有学生，然后，再删除班级。

将class001班的所有学生进行全部删除：

从上述的结果可以看出：student表中已经没有任何学生的记录了。

在grade表中，将class001班删除：

这样就删除了关联表中的数据。

如果直接删除表grade的class002班，会出现什么情况呢？

由此可以看出：在两个具有关联关系的表中删除数据时，一定要先删除从表中的数据，然后，再删除主表中的数据，否则会报错的。

需要注意的是：在实际情况中，想要删除class001班，并不需要删除class001班的学生，可以将表student中“wanghong”和“liqiang”的gid改成null，只要主表中该列没有被从表参照就可以删除。但是在建表时，gid字段有非空约束，所以，只能将“wanghong”和“liqiang”的记录删除。