数据库常见约束——外键

一、外键的定义

外键
用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号。

二、为什么要使用外键

下面本文将从一个例子出发来阐述使用外键的原因。
如何将某京东用户Ryan的信息及其多个商品邮寄地址保存到数据库中呢?

方案一
用单张表来保存用户信息以及商品邮寄地址，如下所示：

create table user_info(//创建user_info表
    id int(10) primary key,
    user_name varchar(30) not null,
    password varchar(30) not null,
    real_name varchar(8),
    mobile char(11),
    address varchar(150)
);

//向user_info表中插入若干条数据
insert into user_info (id,user_name,password,real_name,mobile,address) 
values (1,'Ryan','123456','张三','18920120206','河南安阳');
insert into user_info (id,user_name,password,real_name,mobile,address) 
values (2,'Ryan','123456','李四','18617297545','北京海淀');
insert into user_info (id,user_name,password,real_name,mobile,address)
values (3,'Ryan','123456','王五','17694976949','山西大同');

结果如下

由于每个用户可以有多个收货地址，可以看出这种方法创建的表存在较为严重的字段冗余（user_name和password列），而随着该表中的个人信息字段越来越多，则这一问题表现的就越严重，所以不推荐使用。
为了解决字段冗余的问题我们可以创建两张表，一张用来保存个人信息，另外一张用来保存收货地址。

方案二
设计两张表：一张表保存用户信息，一张表保存商品邮寄信息，如下所示：
表一

create table user_info(//创建user_info表保存用户信息
    id int(10) primary key,
    user_name varchar(30) not null,
    password varchar(30) not null
);
//向user_info表中插入一条数据
insert into user_info (id,user_name,password) values (1,'Ryan','123456');

结果如下

表二

create table address(//创建address表保存商品邮寄信息
    id int(10) primary key,
    user_info_id char(36),
    real_name varchar(8) not null,
    mobile char(11) not null,
    address varchar(150) not null
);
//向address表中插入若干条数据
insert into address (id,user_info_id,real_name,mobile,address) 
values (1,1,'张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address) 
values (2,1,'李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address) 
values (3,1,'王五','17694976949','山西大同');

结果如下

这种方法虽然解决了字段的冗余问题，但由于只是逻辑上的“外键”关系，在插入数据的时候需要靠数据库操作者的自觉性，所以依然无法保证数据完整性，有可能会出现垃圾信息(没有用的信息)。
如果这里在向address表中添加一条数据，并且该数据没有与之对应的用户信息则这里添加的数据就是没有用的信息。

insert into address (id,user_info_id,real_name,mobile,address) values (4,6,'XiaoMing','18338970095','China');

如果这里如果把user_info表中的数据删除，但此时地址表中数据将不再完整，这些地址没有与之对应的用户。此时addres表中与之前用户对应的收货地址成为了垃圾信息。


这里只是逻辑上的“外键”关系，所以还需要手动删除address表中对应的数据。
显然逻辑上的"外键"关系需要依靠操作者的自觉性，依然无法保证数据的完整性。

方案三
只需在address表中添加外键约束即可。如下所示：

create table address(//创建address表保存商品邮寄信息
    id int(10) primary key,
    user_info_id char(36),
    real_name varchar(8) not null,
    mobile char(11) not null,
    address varchar(150) not null,
    constraint address_user_info_id_fk foreign key(user_info_id) references user_info(id)//添加外键约束
);

分析：这种方案为user_info_id添加了外键，指向user_info表的主键，该约束起到了保护数据完整性的作用：如果删除的用户信息id已经在address表中使用，则该条数据无法删除；也无法向address表中添加用户id不存在的地址信息。

三、外键使用时的注意事项

1、要求在从表设置外键关系；
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求；
3、主表的关联列必须是一个key（一般是主键或唯一）；
4、插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表；
5、可以通过以下两种方式来删除主表的记录：
　　方式一：级联删除：ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
　　方式二：级联置空：ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL。