MySQL 实验 6:定义数据的完整性
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`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))
-- 违反了 foreign key 约束,应为院系编号【D06】在父表中不存在。
删除父表(dept)中的数据:
-- 院系编号为【D04】的记录可以删除,因为【D04】在子表中没有对应的记录,删除之后也不会违反 foreign key 约束
mysql> delete from dept where dept_id='D04';
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| D01 | 管理系 |
| D02 | 计算机系 |
| D03 | 机电系 |
+---------+-----------+
3 rows in set (0.00 sec)
-- 删除失败:院系编号为【D02】的记录不能被删除,因为【D02】在子表中有对应的记录,删除之后会违反 foreign key 约束
mysql> delete from dept where dept_id='D02';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`stu_db`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| D01 | 管理系 |
| D02 | 计算机系 |
| D03 | 机电系 |
+---------+-----------+
3 rows in set (0.00 sec)
更新父表(dept)中的数据:更新父表中的数据时,只有更新主码才可能影响 foreign key 约束,更新其他列的取值对 foreign key 约束没有影响。
-- 院系编号为【D03】的记录可以更新院系编号的取值,因为【D03】在子表中没有对应的记录,更新之后也不会违反 foreign key 约束
mysql> update dept set dept_id='D13' where dept_id='D03';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| D01 | 管理系 |
| D02 | 计算机系 |
| D13 | 机电系 |
+---------+-----------+
3 rows in set (0.00 sec)
-- 更新失败:院系编号为【D02】的记录不能更改院系编号的值,因为【D02】在子表中有对应的记录,更新之后会违反 foreign key 约束
mysql> update dept set dept_id='D12' where dept_id='D02';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`stu_db`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| D01 | 管理系 |
| D02 | 计算机系 |
| D13 | 机电系 |
+---------+-----------+
3 rows in set (0.00 sec)
三、非空约束(NOT NULL)
非空约束用来限制某个列的取值不能为空。格式如下:
create table table_name(
col_name data_type,
col_name data_type NOT NULL|NULL,
... ,
col_name data_type
);
-- 说明:如果定义表时,某个列后面既没有 NOT NULL,也没有 NULL 选项,则该列默认可以取空值。
例如:
create table emp(
e_id int primary key,
e_name char(30) NOT NULL,
gender char(2),
birth date,
phone char(20) NOT NULL,
address varchar(200)
);
-- 查看表结构
mysql> desc emp;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| e_id | int(11) | NO | PRI | NULL | |
| e_name | char(30) | NO | | NULL | |
| gender | char(2) | YES | | NULL | |
| birth | date | YES | | NULL | |
| phone | char(20) | NO | | NULL | |
| address | varchar(200) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
-- gender, birth,address 三个表可以取空值。e_id(主键)、e_name 与 phone 三个列不能取空值。
四、定义自增列(auto_increment)
MySQL 的自增列不是一个约束,但定义自增列语法格式与约束的定义相似。
自增列的数据类型必须是整数类型,其他数据类型的列不能定义为自增列。
定义为自增的列必须是一个 key(primary key、unique、key、index),一般把一个表的主键(primary key)定义为自增列。
定义自增列的语法格式如下:
create table table_name(
col_name data_type primary key auto_increment,
col_name data_type,
... ,
col_name data_type
);
例如:
create table t1(
id int primary key auto_increment,
name char(20)
);
-- 查看表结构
mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
如果一个列为自增列,在输入数据时,如果没有给自增列指定数据,则自动生成一个不重复的数据。MySQL 允许为自增列指定数据。例如:
-- 不指定数据
insert into t1(name) values('tom');
-- 查看数据
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | tom |
+----+------+
1 row in set (0.00 sec)
-- 为自增列指定数据
insert into t1 values(10, 'jerry');
-- 查看数据
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 10 | jerry |
+----+-------+
2 rows in set (0.00 sec)
-- 再次插入数据
insert into t1(name) values('rose');
-- 查看数据
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 10 | jerry |
| 11 | rose |
+----+-------+
3 rows in set (0.00 sec)
五、默认值(default)
MySQL 的默认值不是一个约束,但定义默认值的语法格式与约束的定义相似。如果为一个列指定了默认值,则输入数据时如果没有为该列指定数据,则把默认值作为该列的取值。定义默认值的语法格式如下:
create table table_name(
col_name data_type,
col_name data_type default value,
... ,
col_name data_type
);
例如:
create table emp01(
e_id int primary key auto_increment,
e_name char(20) NOT NULL,
gender char(2) default '男',
birth date,
address varchar(200) default '地址未知'
);
-- 查看表结构
mysql> desc emp01;
+---------+--------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+----------+----------------+
| e_id | int(11) | NO | PRI | NULL | auto_increment |
| e_name | char(20) | NO | | NULL | |
| gender | char(2) | YES | | 男 | |
| birth | date | YES | | NULL | |
| address | varchar(200) | YES | | 地址未知 | |
+---------+--------------+------+-----+----------+----------------+
5 rows in set (0.01 sec)
插入数据:
-- 为所有的列指定数据
insert into emp01 values(11, '李华', '女', '1989-1-23', '河南新乡');
-- 默认值用 default 表示
insert into emp01 values(12, '张军', default, '1986-10-8', default);
-- 默认值不指定数据
insert into emp01(e_name, birth) values('李静','1991-1-1');
-- 查看数据
mysql> select * from emp01;
+------+--------+--------+------------+----------+
| e_id | e_name | gender | birth | address |
+------+--------+--------+------------+----------+
| 11 | 李华 | 女 | 1989-01-23 | 河南新乡 |
| 12 | 张军 | 男 | 1986-10-08 | 地址未知 |
| 13 | 李静 | 男 | 1991-01-01 | 地址未知 |
+------+--------+--------+------------+----------+
3 rows in set (0.00 sec)