MySQL-note1 | 表的完整性约束

最新推荐文章于 2024-04-25 15:16:13 发布

原创最新推荐文章于 2024-04-25 15:16:13 发布 · 835 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了MySQL中表的完整性约束，包括非外键约束和外键约束。非外键约束通过列级和表级约束确保数据的正确性，如NULL限制、唯一性约束和主键约束。外键约束则用于实现参照完整性，通过设置不同策略（如NO ACTION、CASCADE和SET NULL）来管理级联操作，确保数据的完整性和一致性。

表的完整性约束

1. 非外键约束

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

实例一：建立一张用来存储学生信息的表

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
约束：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 创建数据库表：
create table t_student(
        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_student;

添加数据：

-- 添加数据：
--  1048 - Column 'sname' cannot be null 不能为null
-- 3819 - Check constraint 't_student_chk_1' is violated. 违反检查约束
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY' 主键重复
-- > 1062 - Duplicate entry 'ls@126.com' for key 't_student.email' 违反唯一约束
insert into t_student values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (3,'露露','男',21,'2023-9-1','java01班','ls@126.com');
-- 如果主键没有设定值，或者用null.default都可以完成主键自增的效果
insert into t_student (sname,enterdate) values ('菲菲','2029-4-5');
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');
insert into t_student values (default,'小刚','男',21,'2023-9-1','java01班','xg@126.com');
-- 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，我们主键也不要求连号的
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','oo@126.com');

常见错误：

–》 1048 - Column ‘sname’ cannot be null 不能为null

–》 819 - Check constraint ‘t_student_chk_1’ is violated. 违反检查约束

– 》1062 - Duplicate（重复的） entry ‘1’ for key ‘t_student.PRIMARY’ 主键重复

– 》1062 - Duplicate entry ‘ls@126.com’ for key ‘t_student.email’ 违反唯一约束

使用自增的时候用null和default都是可以
如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，主键也不要求连号的

- 列级约束和表级约束

(1) 表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

(2) 列级约束：包含在列定义中，直接跟在该列的其它定义之后，用空格分隔；不必指定列名；

实例二

-- 删除表：
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');
-- > 3819 - Check constraint 'ck_stu_sex' is violated.
-- > 3819 - Check constraint 'ck_stu_age' is violated.
-- > 1062 - Duplicate entry 'zs@126.com' for key 't_student.uq_stu_email'
insert into t_student values (3,'李四','男',21,'2023-9-1','java01班','zs@126.com');
-- 查看数据：
select * from t_student;

- 创建表之后添加约束

实例三

-- 删除表：
drop table t_student;
-- 创建数据库表：
create table t_student(
        sno int(6), 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
-- > 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 错误的解决办法：就是auto_increment去掉
-- 在创建表以后添加约束：
alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 修改自增条件
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email);
-- 查看表结构：
desc t_student;

验证约束添加成功：

2. 外键约束

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。
外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。
只有表级约束没有列级约束

实例四

主表（父表）：班级表 - 班级编号 - 主键

从表（子表）：学生表 - 班级编号 - 外键

创建父表—班级表：

cno(主键、自增)；cname（不为空）；room；

-- 先删除表，先删除从表再删除父表
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)
)

创建从表—学生表：

sno(主键、自增)；sname（不为空）；classno（取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义尽量要求相同。);

添加外键约束：

-- 添加学生表，添加外键约束：
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)
    
);

添加数据：

-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3);

查询班级表：

-- 查看班级表和学生表：
select * from t_class;
select * from t_student;

添加外键的效果：

删除班级会出错

-- 尝试删除班级
delete from t_class where cno = 1;

外键策略：

直接删除班级会错误，有时候必须删，可以加入外键策略

策略一：no action 不允许操作

-- 策略1：no action 不允许操作
-- 通过操作sql来完成：
-- 先把班级2的学生对应的班级 改为null 
update t_student set classno = null where classno = 2;
-- 然后再删除班级2：
delete from t_class where cno = 2;

结果：

策略二：使用cascade级联操作

操作主表的时候影响从表的外键

先删除外键约束

-- 策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：
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 置空操作

先删除之前的外键约束

-- 先删除之前的外键约束：
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 = 5 whwere con = 1;

结果：

注：策略2 级联操作和策略2 的删除操作可以混着使用

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