Willie Ramey
0%

MySQL数据库表的完整性约束

发表于 分类于
本文字数： 7.5k 阅读时长 ≈ 7 分钟
  • 非外键约束
  • 外键约束
  • 外键策略

表的完整性约束

为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。MySQL中主要支持以下几种种完整性约束，如表所示。 其中Check约束是MySQL8中提供的支持。

约束条件 约束描述
PRIMARY KEY 主键约束，约束字段的值可唯一地标识对应的记录
NOT NULL 非空约束，约束字段的值不能为空
UNIQUE 唯一约束，约束字段的值是唯一的
CHECK 检查约束，限制某个字段的取值范围，mysql8才有
DEFAULT 默认值约束，约束字段的默认值
AUTO_INCREMENT 自动增加约束，约束字段的值自动递增
FOREIGN KEY 外键约束，约束表与表之间的关系

非外键约束

约束从作用上可以分为两类

  1. 表级约束：
    可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 删除表：
DROP TABLE t_student;

-- 创建数据库表：
create table t_student(
        sno int(6) auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15),
        constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字
        constraint ck_stu_sex check (sex = '男' || sex = '女'),
        constraint ck_stu_age check (age >= 18 and age <= 50),
        constraint uq_stu_email unique (email)
);

-- 添加数据：
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');

-- 查看数据：
select * from t_student;
  1. 列级约束：
    包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
约束：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录
【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 删除表：
DROP TABLE t_student1;

-- 创建数据库表
CREATE TABLE t_student1(
    sno INT(6) PRIMARY KEY auto_increment,
    sname VARCHAR(5) NOT NULL,
    sex CHAR(1) DEFAULT '男' CHECK(sex='男' || sex='女'),
    age INT(3) CHECK(age>=18 AND age<=50),
    enterdate date,
    classname VARCHAR(10),
    email VARCHAR(15) UNIQUE
);

-- 查看数据：
SELECT * FROM t_student1;

-- 添加数据
-- insert into t_student1 values (1,NULL,'男',21,'2023-9-1','java01班','zs@126.com');
-- 1048 - Column 'sname' cannot be null 不能为null

-- insert into t_student1 values (1,'张三','有',21,'2023-9-1','java01班','zs@126.com');
-- 3819 - Check constraint 't_student_chk_1' is violated. 违反检查约束  经验：后续可给检查约束起个名，定位错误

insert into t_student1 values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
SELECT * FROM t_student1;

-- insert into t_student1 values (1,'李四','男',21,'2023-9-1','java01班','zs@126.com');
-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY' 主键重复

-- insert into t_student1 values (2,'李四','男',21,'2023-9-1','java01班','zs@126.com');
-- 1062 - Duplicate entry 'ls@126.com' for key 't_student.email' 违反唯一约束

insert into t_student1 values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');
SELECT * FROM t_student1;

-- 如果主键没有设定值，或者用null.default都可以完成主键自增的效果
insert into t_student1 (sname,enterdate) values ('菲菲','2029-4-5');
insert into t_student1 values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');
insert into t_student1 values (default,'小刚','男',21,'2023-9-1','java01班','xg@126.com');
SELECT * FROM t_student1;

-- insert into t_student1 values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');
-- 1062 - Duplicate entry 'xm@126.com' for key 't_student1.email'
-- 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，我们主键也不要求连号的
insert into t_student1 values (null,'小明','男',21,'2023-9-1','java01班','oo@126.com');
SELECT * FROM t_student1;

-- 删除一条数据
DELETE FROM t_student1 WHERE email="oo@126.com";
  1. 在创建表后添加约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 删除表：
drop table t_student2;

-- 创建数据库表：
create table t_student2(
        sno int(6), 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
-- sno int(6) auto_increment,
-- > 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 错误的解决办法：就是auto_increment去掉

-- 在创建表以后添加约束：
alter table t_student2 add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student2 modify sno int(6) auto_increment; -- 修改自增条件
alter table t_student2 add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student2 add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student2 add constraint uq_stu_email unique (email);

-- 查看表结构：
desc t_student2;

外键约束

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

举个例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- 外键约束

-- 学生表删除,班级表删除：
drop table t_student;
drop table t_class;
-- 注意：先删除从表，再删除主表

-- 先创建父表，班级表：
create table t_class(
        cno int(4) primary key auto_increment,
        cname varchar(10) not null,
        room char(4)
)

-- 添加班级数据：
/*
insert into t_class values (null,'java001','r803');
insert into t_class values (null,'java002','r416');
insert into t_class values (null,'大数据001','r103');
SELECT * FROM t_class;
*/

-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');


-- 添加学生表，添加外键约束：
-- 注意：外键约束只有表级约束，没有列级约束：

-- 方法一：表级约束添加外键
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
        constraint fk_stu_classno foreign key (classno) references t_class (cno)
);

-- 方法二：创建表以后添加外键约束
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)
);
-- 在创建表以后添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)


-- 添加学生信息：
insert into t_student values (null,'张三',1),(null,'李四',3),(null,'王五',2);

-- 查询班级表，学生表：
select * from t_class;
select * from t_student;


-- 测试外键是否添加成功

-- 1.添加一个学生对应的班级编码为4：
insert into t_student values (null,'丽丽',4);
/*
问：班级表中没有4，可以执行？
答：不可以，报错
1452 - Cannot add or update a child row: a foreign key constraint fails (`excepenxi`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
*/

-- 2.删除班级2：
delete from t_class where cno = 2;
/*
问：删除班级2，可以执行？
答：报错，外键添加成功
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`excepenxi`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
*/

班级表

学生表

外键策略

以上无法删除班级2等操作，需使用外键策略

策略一：no action 不允许操作

1
2
3
4
5
6
7
-- 通过操作sql来完成：先把班级2的学生对应的班级 改为null 或者其他班级
update t_student set classno = null where classno = 2;
select * from t_student;

-- 然后再删除班级2：
delete from t_class where cno = 2;
select * from t_class;

学生表

班级表

策略二：cascade 级联操作

1
2
3
4
5
6
7
8
9
10
11
12
-- 操作主表的时候影响从表的外键信息：将班级表3班改为5班，那么学生表3班也会变为5班；删除班级也一样
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;

-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;

-- 试试更新：
update t_class set cno = 5 where cno = 3;

-- 试试删除：
delete from t_class where cno = 5;

更新班级表

学生表也更新了

删除班级

策略三：set null 置空操作

1
2
3
4
5
6
7
8
9
10
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;

-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;

-- 试试更新：
update t_class set cno = 8 where cno = 1;
select * from t_class;
select * from t_student;

学生表置空

  • 策略2 的 级联操作 和 策略3 的 删除操作 可以混着使用：cascade && set null
1
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null ;

应用场景

  1. 朋友圈删除，点赞。留言都删除 – 级联操作
  2. 解散班级，对应的学生 置为班级为null就可以了，– set null


----------- 本文结束 -----------




Buy me a coffee.