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

在这里可以讨论MariaDB/MySql相关使用技术

版主: jvip_chen

回复
头像
jvip_chen
社区版主
社区版主
帖子: 177
注册时间: 2019年 1月 5日 13:36 星期六

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

帖子 jvip_chen »

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 外键名;
1
如果解除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数据库表之间的关系,对增强数据库表设计的理解,是程序员增强数据结构存储的必备知识储备。

回复

回到 “MariaDB/MySql数据库”