MySQL中外键的定义、作用、添加和删除

<>1 简介


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

<>2 外键的定义

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

<>3 材料准备
mysql> create table grade( -> id int(4) not null primary key, -> name
varchar(36) -> ); Query OK, 0 rows affected (1.14 sec)
上表为班级表。
mysql> drop table student; Query OK, 0 rows affected (0.41 sec) mysql> show
tables; +----------------+ | Tables_in_test | +----------------+ | grade |
+----------------+ 1 row in set (0.00 sec) mysql> create table student ( -> sid
int(4) not null primary key, -> sname varchar(36), -> gid int(4) not null -> );
Query OK, 0 rows affected (0.44 sec)
上述的代码片段则是首先删除在本数据库中的student表，然后创建student表。

首先建立两张表，student和grade，学生表中的gid是学生所在的班级id，是引入了班级表grade中的主键id。那么gid就可以作为表student表的外键。被引用的表，即表grade是主表，引用外键的表，即student，是从表。两个表是主从关系。表student用gid可以连接表grade中的信息，从而建立了两个表中的连接。
可以这么理解，外键即依赖关系，可以明确的声明表和表之间的关系额字段的参照关系，这种就叫做表和表之间声明了一个外键。

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

<>4 外键的添加和删除

<>4.1 添加外键

为表添加外键约束的语法格式如下：
alert table 表名 add constraint FK_ID foreign key(外键字段名) references 外表表名(主键字段名)
其中FK_ID为外键的名称，是随意的。
为表student添加外键约束，具体语句如下：
mysql> alter table student add constraint FK_ID foreign key gid references
grade id; ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'references grade id' at line 1 mysql> alter table student add
constraint FK_ID foreign key (gid) references grade (id); Query OK, 0 rows
affected (1.27 sec)
语句执行成功后，使用DESC语句来查看学生表和班级表，查询结果如下：
mysql> desc grade; +-------+-------------+------+-----+---------+-------+ |
Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+ | id | int(4) | NO | PRI
| NULL | | | name | varchar(36) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+ 2 rows in set (0.06 sec)
mysql> desc student; +-------+-------------+------+-----+---------+-------+ |
Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+ | sid | int(4) | NO |
PRI | NULL | | | sname | varchar(36) | YES | | NULL | | | gid | int(4) | NO |
MUL | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in
set (0.00 sec)
可以从上述的输出中看出表grade中id为主键，student表中gid为外键，但是结果不能明确的看出两个表之间的关系。在MySQL中可以用show
create table来查看表的详细结构，具体语句如下：
mysql> show create table student;
+---------+------------------------------------------------------------ | Table
| Create Table
+---------+------------------------------------------------------------ |
student | CREATE TABLE `student` ( `sid` int(4) NOT NULL, `sname` varchar(36)
DEFAULT NULL, `gid` int(4) NOT NULL, PRIMARY KEY (`sid`), KEY `FK_ID` (`gid`),
CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------ 1 row
in set (0.06 sec)


<>4.2 验证外键的作用
mysql> insert into student (sid, sname, gid) values(1000, 'wusong', 123);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`test`.`student`, CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES
`grade` (`id`))
上述错误表示，若要向student中插入学生数据，则必须插入已经在grade中存在的gid值，实际意义就是要把新来的学生放入某个班级，则班级必须先存在。
mysql> insert grade values(10, '一班'); Query OK, 1 row affected (0.16 sec)
mysql> insert grade values(11, '二班'); Query OK, 1 r，ow affected (0.12 sec)
mysql> insert grade values('', 1); ERROR 1366 (HY000): Incorrect integer value:
'' for column 'id' at row 1 mysql> insert grade values(12, 1);
//可见整数1可以自动转换为varchar1 Query OK, 1 row affected (0.09 sec) mysql> select * from
grade; +----+--------+ | id | name | +----+--------+ | 10 | 一班 | | 11 | 二班 | |
12 | 1 | +----+--------+ 3 rows in set (0.00 sec)
通过插入语句，已经向grade中插入了三个班级。向班级1中插入数据和班级2中插入。学生记录维持在表student中。
mysql> insert into student(sid, sname, gid) values(1000, '周华健', 10); Query OK,
1 row affected (0.11 sec) mysql> insert into student(sid, sname, gid)
values(1001, '周芷若', 10); Query OK, 1 row affected (0.17 sec) mysql> insert into
student(sid, sname, gid) values(1002, '周杰伦', 10); Query OK, 1 row affected
(0.19 sec) 以同样的方式向班级2中插入两条数据 mysql> insert into student(sid, sname, gid)
values(10003, '赵云', 11), (10004, '赵子龙', 11); Query OK, 2 rows affected (0.12
sec) Records: 2 Duplicates: 0 Warnings: 0
可以插入gid不存在的班级吗？
mysql> insert into student(sid, sname, gid) values(10003, '赵云', 0); ERROR 1062
(23000): Duplicate entry '10003' for key 'PRIMARY' mysql> insert into
student(sid, sname, gid) values(10004, '赵云', 0); ERROR 1062 (23000): Duplicate
entry '10004' for key 'PRIMARY' mysql> insert into student(sid, sname, gid)
values(10005, '赵云', 0); ERROR 1452 (23000): Cannot add or update a child row: a
foreign key constraint fails (`test`.`student`, CONSTRAINT `FK_ID` FOREIGN KEY
(`gid`) REFERENCES `grade` (`id`))
上述的代码片段中演示了插入数据时外键所起的作用，那么删除时外键依然会外参照完整性提供保护。
mysql> select * from grade; +----+--------+ | id | name | +----+--------+ | 10
| 一班 | | 11 | 二班 | | 12 | 1 | +----+--------+ 3 rows in set (0.00 sec) mysql>
select * from student; +-------+-----------+-----+ | sid | sname | gid |
+-------+-----------+-----+ | 1000 | 周华健 | 10 | | 1001 | 周芷若 | 10 | | 1002 |
周杰伦 | 10 | | 10003 | 赵云 | 11 | | 10004 | 赵子龙 | 11 | +-------+-----------+-----+
5 rows in set (0.00 sec)
如果此时要直接删除grade中的班级1会发生什么？
mysql> delete from grade where id=10; ERROR 1451 (23000): Cannot delete or
update a parent row: a foreign key constraint fails (`test`.`student`,
CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`)) mysql> delete
from grade where id=12; Query OK, 1 row affected (0.16 sec)
上述代码片段id为10在删除记录时弹出了Error，而删除id为12的记录时可以成功执行，是因为在student表中有依赖id=10的记录存在，因此无法删除。

<>4.3 外键关联表联合删除


建立外键是为了保证数据的完整和统一性，但如果主表中的数据被删除或修改，从表中的数据该怎么办？很明显应该删除，否则数据库中会存在很多无意义的垃圾数据。为此，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}]
其中restrict是默认操作，表示拒绝主表删除或修改外键关联列，这是最安全的设置。
而cascade表示删除包含与已删除键值有参考关系的所有记录。

<>4.4 删除外键约束

在实际开发中，根据业务逻辑的需求，需要解除两个表之间的关联关系时，就需要删除外键约束。删除外键约束的语法如下：
alter table 表名 drop foreign key 外键名;
如果解除student表的外键约束，具体语句如下：
mysql> show create table student;
+---------+-------------------------------------------------------------- |
Table | Create Table
+---------+-------------------------------------------------------------- |
student | CREATE TABLE `student` ( `sid` int(4) NOT NULL, `sname` varchar(36)
DEFAULT NULL, `gid` int(4) NOT NULL, PRIMARY KEY (`sid`), KEY `FK_ID` (`gid`),
CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------- 1 row
in set (0.00 sec) mysql> alter table student drop foreign key FK_ID; Query OK,
0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0
此时表grade和student的参照关系便被移除了，
mysql> desc student; +-------+-------------+------+-----+---------+-------+ |
Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+ | sid | int(4) | NO |
PRI | NULL | | | sname | varchar(36) | YES | | NULL | | | gid | int(4) | NO |
MUL | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in
set (0.00 sec) mysql> insert into student (sid, sname, gid) values (1003,
'刘亦菲', 5); Query OK, 1 row affected (0.13 sec) mysql> delete from student where
id=10; ERROR 1054 (42S22): Unknown column 'id' in 'where clause' mysql> delete
from grade where id=10; //此时student表中存在gid为10的记录 Query OK, 1 row affected (0.13
sec) mysql> show create table student;
+---------+------------------------------------------------------ | Table |
Create Table +---------+------------------------------------------------------
| student | CREATE TABLE `student` ( `sid` int(4) NOT NULL, `sname` varchar(36)
DEFAULT NULL, `gid` int(4) NOT NULL, PRIMARY KEY (`sid`), KEY `FK_ID` (`gid`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------ 1 row in set
(0.00 sec) mysql> insert into student (sid, sname, grade^C mysql> desc student;
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null |
Key | Default | Extra | +-------+-------------+------+-----+---------+-------+
| sid | int(4) | NO | PRI | NULL | | | sname | varchar(36) | YES | | NULL | | |
gid | int(4) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
由于参照关系被移除了，则插入student表或者删除表grade记录则不再存在参照依赖关系了，由上述的代码片段可以看到结果。

<>5 总结


在多表查询中，外键是参照关系的提现，而明确的理解外键关系的含义，了解外键关系的添加和删除对于mysql数据库表之间的关系，对增强数据库表设计的理解，是程序员增强数据结构存储的必备知识储备。

<>6文档下载

https://download.csdn.net/download/lk142500/10756524
<https://download.csdn.net/download/lk142500/10756524>

• « 上一篇：蚂蚁金服SOFA开源负责人鲁直：不只是中间件 ，未来会开源更多
• » 下一篇：Android studio 横竖屏切换