一、数据完整性
前面我们学习了表的创建、删除和修改。对于已经创建好的表，虽然字段的数据类型决定了所能存储的数据类型，但是表中所存储的数据是否合法并没有进行检查。在具体使用Mysql软件时，如果想针对表中的数据做一些完整性检查操作，可以通过表的约束来完成。
所谓完整性是指数据的准确性和一致性，而完整性检查就是指检查数据的准确性和一致性。MySQL 数据库管理系统提供了一致机制来检査数据库表中的数据是否满足规定的条件，以保证数据库表中数据的准确性和一致性，这种机制就是约束。
MySQL数据库管理系统除了支持标准SQL的完整性约束外，还进行了相应扩展。扩展后增加AUTO_INCREMENT约束。
1. 实体完整性(Entity Integrity)
- 通过主键(Primary Key)约束实现
- 确保表中每行数据都能被唯一标识
- 主键列不允许NULL值且值必须唯一
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
2. 参照完整性(Referential Integrity)
- 通过外键(Foreign Key)约束实现
- 确保表之间的关系有效性
- 防止"孤儿记录"(没有父记录的子记录)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
3. 域完整性(Domain Integrity)
-
确保列中只包含有效数据
-
实现方式包括：
-
- 数据类型约束，包含ENUM和SET类型
- NOT NULL约束
- CHECK约束
- DEFAULT值
CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL(10,2) CHECK (price > 0),
status ENUM('active', 'inactive') DEFAULT 'active'
);
4. 用户自定义完整性(User-defined Integrity)
- 通过存储过程、触发器等实现业务规则
- 例如：确保订单总额不超过客户信用额度
二、操作表的约束
1、强化建表语法
之前语法：
CREATE TABLE 表名(
列名1 数据类型,
列名2 数据类型,
....
列名n 数据类型
);
现在的语法：
CREATE TABLE 表名(
列名1 数据类型 [约束1 约束2 ...],
列名2 数据类型 [约束1 约束2 ...],
....
列名n 数据类型 [约束1 约束2 ...]
);
1、设置非空约束（NOT NULL）
当数据库表中的某个字段上的内容不希望设置为 NULL 时，则可以使用非空约束进行设置。即NK约束在创建数据库表时为某些字段加上“NOT NULL”约束条件，保证所有记录中该字段都有值。如果用户插入的记录中，该字段为空值，则数据库管理系统会报错。设置表中某字段的 非空约束非常简单，在MySQL数据库管理系统中通过SQL语句NOT NULL来实现，其语法形式如下:
CREATE TABLE table_name(
列名 数据类型 NOT NULL,
....
);
例如，创建t_dept表，设置deptno字段非空
CREATE TABLE t_dept(
deptno INT NOT NULL,
dname VARCHAR(20),
loc VARCHAR(40)
);
可以看到deptno的NULL变成了NO，我们尝试添加数据
2、设置唯一约束（UNIQUE）
当我们需要保证某列的数据内容不允许重复的时候，选择使用唯一约束（UK），当我们尝试添加重复记录的时候，会添加失败（报错），可以加快数据查询的速度。
语法
字段名 数据类型 UNIQUE
重新创建t_dept表
CREATE TABLE t_dept(
deptno INT NOT NULL UNIQUE,
dname VARCHAR(20) UNIQUE,
loc VARCHAR(40)
);
尝试在表中添加两条数据，deptno都是1
验证：
唯一约束的列是否可以允许空值？
可以为空，原理是Mysql中NULL是不参与运算
3、使用主键约束
主键，又称主码，是表中一列或多列的组合。主键约束(Primary Key Constraint)要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型:单字段主键和多字段联合主键。
每一张表中最多有1个主键约束，但是，这个主键约束可以用来约束1列，也可以同时约束多列。
3.1 单字段主键
主键由一个字段组成，SQL语句格式分为以下三种情况。
① 在定义列的同时指定主键
语法
字段名 数据类型 PRIMARY KEY
例如
CREATE TABLE tb_emp2(
id INT PRIMARY KEY,
name VARCHAR(25),
deptId INT,
salary FLOAT
);
② 在定义所有列之后指定主键
语法
PRIMARY KEY (字段名)
例如
CREATE TABLE tb_emp3(
id INT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
PRIMARY KEY (id)
);
3.2 复合主键
一个约束作用于多个字段上，有些地方也叫联合主键
语法
只能在定义所有列之后定义复合主键
PRIMARY KEY (列1,列2,...,列n)
CREATE TABLE tb_emp3(
id INT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
PRIMARY KEY (id,name)
);
复合主键作用的多个字段，每个字段的数据都不能为空
复合主键作用的多列同时相同，才认定为相同数据
3.3 什么时候使用主键
主键的意义是保证数据的实体完整性，也就是每一个实体可以被唯一的区分。
今后我们创建表，每张表都设置一个主键，大部分情况我们使用单列主键，只有单列主键不能被唯一区分的时候，我们选择复合主键。
建议主键使用非业务字段，通常添加一个新列叫“id”，这个id列通常没有特殊含义，只是作为主键列，唯一的区分每一行数据。
id列通常这么设置：
CREATE TABLE 表名( id INT PRIMARY KEY, ..... );
4、自动递增（AUTO_INCREMENT)
这个约束我们只能在Mysql中使用，不是标准SQL。配合主键约束使用，给主键列新插入的数据自动编号。
这个约束不能单独使用，必须在PRMARY KEY的列上使用。
语法
CREATE TABLE 表名(
id INT PRIMARY KEY AUTO_INCREMENT,
.....
);
CREATE TABLE tb_emp4(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
deptId INT,
salary FLOAT
);
4.1 调整起始数字
语法
CREATE TABLE 表名(
id INT PRIMARY KEY AUTO_INCREMENT,
...
) AUTO_INCREMENT=起始值;
例如
CREATE TABLE tb_emp4(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
deptId INT,
salary FLOAT
) AUTO_INCREMENT = 100;
5、默认值约束（DEFAULT）
当我们添加数据的时候，没有给这个列设置值，我们可以通过默认值约束，为这个字段初始化一个指定值。
语法
字段名 数据类型 DEFAULT 默认值
CREATE TABLE tb_emp5(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT DEFAULT 100,
salary FLOAT
);
以上语句默认deptId的值为100
注意：DEFAULT 后的默认值的数据类型要和当前字段的数据类型保持一致。即使不一致，也要支持自动的隐式转换。
CREATE TABLE tb_emp6(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT DEFAULT '研发部',
salary FLOAT
);
6、检查约束（CHECK）
检查约束的作用是对插入的字段值做判断，满足检查约束的条件，则允许插入，否则不允许
如果你的Mysql版本低于8，没有CHECK约束
语法
CREATE TABLE 表名(
字段名 数据类型 CHECK(判断),
...
CHECK(判断)
);
tb_emp7添加一个age字段，加上检查约束，要求年龄在20~35岁之间
CREATE TABLE tb_emp7(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT DEFAULT 100,
age int CHECK(age<=35 and age >= 20),
salary FLOAT
);
CREATE TABLE tb_emp7(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT DEFAULT 100,
age int,
CHECK(age<=35 and age >= 20),
salary FLOAT
);
7、外键约束（FOREIGN KEY）
外键约束主要保证参照完整性，外键约束通常涉及两张表（A，B表）
A：员工表 tb_emp8（从表/子表）
CREATE TABLE tb_emp8(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
deptId INT,
salary FLOAT
);
B：部门表 t_dept（主表/父表）
CREATE TABLE t_dept1(
id INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
左侧的员工表和右侧的部门表没有关联关系，左侧的deptId数据不准确
添加外键
语法
FOREIGN KEY (列名) REFERENCES 被关联表(被关联列) [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT] [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]
重新创建tb_emp8
CREATE TABLE tb_emp8(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
FOREIGN KEY (deptId) REFERENCES t_dept1(id)
);
7.1 主从表的创建与删除
-
创建的时候
- 先创建主表
- 再创建从表
-
删除表的时候
- 先删除从表
- 再删除主表
7.2 主从表的数据的删除与修改【了解】
RESTRICT【限制】（默认）
CASCADE【级联】
SET NULL【设置为NULL值】
NO ACTION【不做处理】
SET DEFAULT【设置为默认值】
① RESTRICT
当我们尝试删除主表的某行数据，而这个数据在从表中有关联
报错，因为外键设置的级别是RESTRICT【限制】
② CASCADE
CREATE TABLE tb_emp8(
id INT ,
name VARCHAR(25),
deptId INT,
salary FLOAT,
FOREIGN KEY (deptId) REFERENCES t_dept1(id) ON DELETE CASCADE
);
当我们删除主表中的数据时，从表中关联的数据同时被删除！
③ SET NULL
CREATE TABLE tb_emp8(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
FOREIGN KEY (deptId) REFERENCES t_dept1(id) ON DELETE SET NULL
);
当我们删除主表中的数据时，从表中关联的数据的关联字段被设置成NULL！
7.3 其他细节
-
外键列是否可以为空（NULL）？
-
- 可以
-
主表中的关联列必须设置为唯一
-
外键的名字如何设置
-
- CONSTRAINT 外键名 FOREIGN KEY (从表关联列) REFERENCES 主表(主表关联列)
- 外键名的规范：FK_主表_从表_从表关联列
t_dept2
CREATE TABLE t_dept2(
id INT,
dname VARCHAR(20)
);
tb_emp9
CREATE TABLE tb_emp9(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
FOREIGN KEY (deptId) REFERENCES t_dept2(id)
);
创建从表的时候报错，主表的关联列必须保持唯一性，必须有唯一约束或者主键约束
8、约束的修改
约束是添加到表结构上的，所以关于约束的修改实际上是表结构的修改。
8.1 创建表之后，追加主键
CREATE TABLE tb_emp10(
id INT,
name VARCHAR(25),
deptId INT,
salary FLOAT
);
表已经创建了，发现主键没有添加。
ALTER TABLE 表名 ADD PRIMARY KEY (列1,列2,..)
ALTER TABLE tb_emp10 ADD PRIMARY KEY (id);
8.2 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
8.3 追加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表关联列) REFERENCES 主表(主表关联列);
8.4 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
8.5 更改其他约束
ALTER TABLE 表名 MODIFY 列名 新数据类型 新约束;
9.练习
1. 学生表 (students)
| 列名 | 说明 | 需求 |
|---|---|---|
| student_id | 学生ID | 唯一标识每个学生 |
| name | 学生姓名 | 不能为空 |
| birth_date | 出生日期 | 确保是合理日期 |
| gender | 性别 | 只能接受有限的几个值（如’男’,‘女’,‘其他’） |
| enrollment_date | 入学日期 | 应有默认值（如当前日期） |
| contact_phone | 联系电话 | 确保格式正确（如11位手机号） |
2. 课程表 (courses)
| 列名 | 说明 | 需求 |
|---|---|---|
| course_id | 课程ID | 唯一标识每门课程 |
| course_name | 课程名称 | 不能为空且应唯一 |
| credit_hours | 学分 | 应有合理的范围限制（如0.5-10） |
| department | 所属院系 | |
| description | 课程描述 |
3. 成绩表 (scores)
| 列名 | 说明 | 需求 |
|---|---|---|
| record_id | 记录ID | 唯一标识每条记录 |
| student_id | 学生ID | 引用学生表中的学生 |
| course_id | 课程ID | 引用课程表中的课程 |
| score | 分数 | 应有合理的范围限制（如0-100） |
| exam_date | 考试日期 |
每个学生在同一门课程只能有一条成绩记录
create table student(
student_id int primary key,
name varchar(30) not null,
birth_date date,
gender enum('男','女','其他'),
enrollment_date datetime default now(),
contact_phone char(11),
check(birth_date > '1980-01-01' and birth_date < '2005-01-01')
);
create table courses(
course_id int primary key,
course_name varchar(50) not null unique,
credit_hours int check(credit_hours > 0 and credit_hours < 50),
department int,
description varchar(255)
);
create table scores(
record_id int unique,
student_id int,
course_id int,
score double check(score >= 0 and score <= 100),
exam_date date,
primary key(student_id,course_id),
foreign key (student_id) references student(student_id),
foreign key (course_id) references courses(course_id)
);
喜欢的同学们请关注我哦，创作不易，给个三连表示鼓励~~~
扫一扫
1788
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
