表的完整性约束

素颜马尾好姑娘i 2023-09-27 14:55 92阅读 0赞

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

MySQL中主要支持以下几种种完整性约束，如表所示。 其中Check约束是MySQL8中提供的支持。

![e0e643a7f3a09494c3f282fea77feb49.png][]

非外键约束

/*
    建立一张用来存储学生信息的表
    字段包含学号、姓名、性别，年龄、入学日期、班级，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
    );
    -- 添加数据：
    --  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');
    -- 查看数据：
    select * from t_student;

![1ba9a75fa4c67d8f18826b75063d2977.gif][]

![0a838603755cb6a498afe7ca9ca68c5c.gif][]

【2】约束从作用上可以分为两类：

(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;

![097c5808949cf9bd0eadfe028f7fb1e5.gif][]

![96d475b3d973482410f1ac781ac7eb1f.gif][]

【3】在创建表以后添加约束：

-- 删除表：
    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;

![7d5f0dae51d3a401d7c56894839779b6.gif][]

![4f26d798888488d24a81ad52edb56a19.gif][]

验证约束添加成功：查看表结构：

![d65544c2ae4567c85ef0d8b297580aa3.png][]

【4】总结：

1.主键约束

主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

主键可以是单个字段，也可以是多个字段组合。对于单字段主键的添加可使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。

2.非空约束

非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）

为已存在表中的字段添加非空约束

alter   table student8 modify stu_sex varchar(1) not null;

![642a45b83783a2fb15c72d6897b1302f.gif][]

![ea1820e28eeb8626d2e4f64cfb2815a2.gif][]

使用ALTER TABLE语句删除非空约束

alter  table student8 modify stu_sex varchar(1) null;

![Image 1][]

![55c6bde0e08ec64470eb82fdf1f05949.gif][]

3. 唯一约束

唯一约束（UNIQUE，缩写UK）比较简单，它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。如果想要某个字段的值不重复，那么就可以为该字段添加为唯一约束。

无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束

4. 检查约束

检查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。

5. 默认值约束

默认值约束（DEFAULT）用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

默认值约束的设置与非空约束一样，也只能使用列级约束。

6. 字段值自动增加约束

自增约束（AUTO\_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。

由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

为已存在表中的字段添加自增约束

/*创建表student11*/
    
     create   table student11 (
    
           stu_id int(10) primary key,
    
           stu_name varchar(3),
    
           stu_sex varchar (1)
    
    );
    
    /*为student11表中的主键字段添加自增约束*/
    
    alter   table student11 modify stu_id int(10) auto_increment;

![4e489756bbd51b76b54bfe265c4a901a.gif][]

![a6b3038d3be054c53c47f5790090f69d.gif][]

使用ALTER TABLE语句删除自增约束

alter   table studen11 modify stu_id int(10);

![ef16770bffcbabc56b07b47d0579d4e1.gif][]

![67cd12bfffd218af35a1f588414093f2.gif][]

[e0e643a7f3a09494c3f282fea77feb49.png]: https://img-blog.csdnimg.cn/img_convert/e0e643a7f3a09494c3f282fea77feb49.png
[1ba9a75fa4c67d8f18826b75063d2977.gif]: https://img-blog.csdnimg.cn/img_convert/1ba9a75fa4c67d8f18826b75063d2977.gif
[0a838603755cb6a498afe7ca9ca68c5c.gif]: https://img-blog.csdnimg.cn/img_convert/0a838603755cb6a498afe7ca9ca68c5c.gif
[097c5808949cf9bd0eadfe028f7fb1e5.gif]: https://img-blog.csdnimg.cn/img_convert/097c5808949cf9bd0eadfe028f7fb1e5.gif
[96d475b3d973482410f1ac781ac7eb1f.gif]: https://img-blog.csdnimg.cn/img_convert/96d475b3d973482410f1ac781ac7eb1f.gif
[7d5f0dae51d3a401d7c56894839779b6.gif]: https://img-blog.csdnimg.cn/img_convert/7d5f0dae51d3a401d7c56894839779b6.gif
[4f26d798888488d24a81ad52edb56a19.gif]: https://img-blog.csdnimg.cn/img_convert/4f26d798888488d24a81ad52edb56a19.gif
[d65544c2ae4567c85ef0d8b297580aa3.png]: https://img-blog.csdnimg.cn/img_convert/d65544c2ae4567c85ef0d8b297580aa3.png
[642a45b83783a2fb15c72d6897b1302f.gif]: https://img-blog.csdnimg.cn/img_convert/642a45b83783a2fb15c72d6897b1302f.gif
[ea1820e28eeb8626d2e4f64cfb2815a2.gif]: https://img-blog.csdnimg.cn/img_convert/ea1820e28eeb8626d2e4f64cfb2815a2.gif
[Image 1]: 
[55c6bde0e08ec64470eb82fdf1f05949.gif]: https://img-blog.csdnimg.cn/img_convert/55c6bde0e08ec64470eb82fdf1f05949.gif
[4e489756bbd51b76b54bfe265c4a901a.gif]: https://img-blog.csdnimg.cn/img_convert/4e489756bbd51b76b54bfe265c4a901a.gif
[a6b3038d3be054c53c47f5790090f69d.gif]: https://img-blog.csdnimg.cn/img_convert/a6b3038d3be054c53c47f5790090f69d.gif
[ef16770bffcbabc56b07b47d0579d4e1.gif]: https://img-blog.csdnimg.cn/img_convert/ef16770bffcbabc56b07b47d0579d4e1.gif
[67cd12bfffd218af35a1f588414093f2.gif]: https://img-blog.csdnimg.cn/img_convert/67cd12bfffd218af35a1f588414093f2.gif