MySQL 实验 6：定义数据的完整性
数据的完整性是指通过某种规则限制数据的取值范围。数据的完整性又称为完整性约束或完整性规则。根据规则的不同，关系数据库的完整性分为三种：实体完整性，参照完整性，用户自定义完整性。
一、实体完整性
实体完整性用来限制关系中主码的取值不能为空，当然主码也不能取重复值。
MySQL 通过定义 primary key 约束定义主码，则 primary key 约束所包含的列不能取空值，也不能取重复值。
MySQL 的 unique 约束可以限制所包含的列不能取重复值，但默认可以取空值，可以通过添加 NOT NULL 选项限制其不能取空值。
1、primary key（主键）约束
根据主码所包含的列数不同，分为两种：单属性码（主码只包含一个属性），多属性码（主码包含多个属性）。一个表只能定义一个主键，定义 primary key 约束的语法如下：
-- 单属性码的定义
create table table_name(
col_name data_type primary key,
col_name data_type,
... ,
col_name data_type
);
-- 多属性码的定义（单属性码也可以采用该种格式，单属性码可以看做是特殊的多属性码）
create table table_name(
col_name data_type,
col_name data_type,
... ,
col_name data_type,
primary key(col_name, col_name, ...)
);
举例：
create table stu(
s_id char(11) primary key,
s_name char(20),
gender char(2),
birth date
);
create table course(
c_id char(3) primary key,
c_name char(50)
);
create table score(
s_id char(11),
c_id char(3),
score int,
primary key(s_id, c_id)
);
上述三张表的表结构如下：
-- PRI 包含的列为主键
mysql> desc stu;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| s_id | char(11) | NO | PRI | NULL | |
| s_name | char(20) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
-- PRI 包含的列为主键
mysql> desc course;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| c_id | char(3) | NO | PRI | NULL | |
| c_name | char(50) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
-- PRI 包含的列为主键：该表中有两个 PRI，表示一个主键包含两个属性，而不是两个主键
mysql> desc score;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| s_id | char(11) | NO | PRI | | |
| c_id | char(3) | NO | PRI | | |
| score | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
2、unique 约束
unique 约束又称为唯一约束，可以保证 unique 约束所包含的列取值唯一。定义 unique 约束与 primary key 约束格式相近，但一个表可以定义多个 unique 约束。语法如下：
-- 单属性码的定义
create table table_name(
col_name data_type,
col_name data_type unique,
... ,
col_name data_type
);
-- 多属性码的定义（单属性码也可以采用该种格式）
create table table_name(
col_name data_type,
col_name data_type,
... ,
col_name data_type,
unique(col_name, col_name, ...)
);
举例：
create table emp(
e_id int primary key,
e_name char(30),
gender char(2),
salary decimal(10,2),
phone char(20) unique NOT NULL, -- 手机号：不能取重复值，并且不能为空
id_card char(18) unique, -- 身份证号：不能取重复值，可以为空
address varchar(200)
);
-- 查看表结构
mysql> desc emp;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| e_id | int(11) | NO | PRI | NULL | |
| e_name | char(30) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| phone | char(20) | NO | UNI | NULL | |
| id_card | char(18) | YES | UNI | NULL | |
| address | varchar(200) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
二、参照完整性
参照完整性：限制外键（foreign key）的取值。
（1）外键可以取空值。
（2）外键如果不取空值，则必须从与之对应的父表的主码中取值。
1、定义 foreign key 约束
MySQL 定义外键的语法如下：
create table table_name(
col_name data_type,
col_name data_type,
... ,
col_name data_type,
foreign key(col_name) references table_name(col_name)
);
举例：新建数据库，在数据库中创建如下四张表
create database stu_db;
use stu_db;
-- 院系表：dept_id 列为主键
create table dept(
dept_id char(3) primary key,
dept_name char(50)
);
-- 学生表：s_id 列为主键，dept_id 列为外键，与 dept 表的列 dept_id 对应
create table stu(
s_id char(11) primary key,
s_name char(20),
gender char(2),
birth date,
dept_id char(3),
foreign key(dept_id) references dept(dept_id)
);
-- 可成表：c_id 列为主键
create table course(
c_id char(3) primary key,
c_name char(50)
);
-- 成绩表：s_id 与 c_id 两个列为主键
-- s_id 列为外键，与 stu 表的列 s_id 对应
-- c_id 列为外键，与 course 表的列 c_id 对应
create table score(
s_id char(11),
c_id char(3),
score int,
primary key(s_id, c_id),
foreign key(s_id) references stu(s_id),
foreign key(c_id) references course(c_id)
);
2、查看 foreign key 约束
上述四张表的表结构如下：
mysql> desc dept;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| dept_id | char(3) | NO | PRI | NULL | |
| dept_name | char(50) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc stu;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| s_id | char(11) | NO | PRI | NULL | |
| s_name | char(20) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
| birth | date | YES | | NULL | |
| dept_id | char(3) | YES | MUL | NULL | |
+---------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> desc course;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| c_id | char(3) | NO | PRI | NULL | |
| c_name | char(50) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc score;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| s_id | char(11) | NO | PRI | | |
| c_id | char(3) | NO | PRI | | |
| score | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
可以使用命令【show create table 表名】查看 foreign key 约束的详情：
mysql> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`s_id` char(11) NOT NULL,
`s_name` char(20) DEFAULT NULL,
`gender` char(2) DEFAULT NULL,
`birth` date DEFAULT NULL,
`dept_id` char(3) DEFAULT NULL,
PRIMARY KEY (`s_id`),
KEY `dept_id` (`dept_id`),
CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table score\G
*************************** 1. row ***************************
Table: score
Create Table: CREATE TABLE `score` (
`s_id` char(11) NOT NULL DEFAULT '',
`c_id` char(3) NOT NULL DEFAULT '',
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`s_id`,`c_id`),
KEY `c_id` (`c_id`),
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `stu` (`s_id`),
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3、验证 foreign key 约束的生效情况
说明：
（1）插入数据时， foreign key 约束限制子表中数据的取值。
（2）删除和更新数据时， foreign key 约束也会限制父表中数据的取值。
（3）创建表时，需要先创建父表，才能在子表中创建 foreign key 约束。删除表时，必须先删除子表，才能删除父表。
下面以 dept 与 stu 两张表为例，验证 foreign key 约束的生效情况。
在 dept 表中输入如下的数据：
insert into dept values('D01', '管理系');
insert into dept values('D02', '计算机系');
insert into dept values('D03', '机电系');
insert into dept values('D04', '法律系');
-- 查看数据
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| D01 | 管理系 |
| D02 | 计算机系 |
| D03 | 机电系 |
| D04 | 法律系 |
+---------+-----------+
4 rows in set (0.00 sec)
在 stu 表中输入如下的数据（正常数据，满足 foreign key 约束）：
insert into stu values('20230224101', '李刚', '男', '2005-10-8', 'D01');
insert into stu values('20230224102', '张静静', '女', '2005-6-12', 'D01');
insert into stu values('20230224103', '李梅', '女', '2004-12-18', 'D01');
insert into stu values('20230224201', '王大鹏', '男', '2005-6-30', 'D02');
insert into stu values('20230224202', '张九龄', '男', '2004-11-19', 'D02');
-- 查看数据
mysql> select * from stu;
+-------------+--------+--------+------------+---------+
| s_id | s_name | gender | birth | dept_id |
+-------------+--------+--------+------------+---------+
| 20230224101 | 李刚 | 男 | 2005-10-08 | D01 |
| 20230224102 | 张静静 | 女 | 2005-06-12 | D01 |
| 20230224103 | 李梅 | 女 | 2004-12-18 | D01 |
| 20230224201 | 王大鹏 | 男 | 2005-06-30 | D02 |
| 20230224202 | 张九龄 | 男 | 2004-11-19 | D02 |
+-------------+--------+--------+------------+---------+
5 rows in set (0.00 sec)
在 stu 表中输入如下的数据（报错！违反了 foreign key 约束）：
mysql> insert into stu values('20230224601', '李逵', '男', '2005-3-25', 'D06');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`stu_db`.`stu`,