MySQL表的完整性约束

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

约束条件       约束描述
primary key 主键约束,约束字段的值可唯一的标识对应的记录
not null非空约束,约束字段的值不能为空
unique唯一约束,约束字段的值时唯一的
check检查约束,限制某个字段值得取值范围
default默认值约束
auto_increment自动增长的约束,约束字段的值自动递增
foreign key外键约束,约束表与表之间的关系

非外键约束

约束从作用上可以分为两类:
(1)列级约束:包含在列定义中,直接跟在该列的其它定义之后,用空格分隔;不必指定列名;
(2)表级约束:可以约束表中任意一个或多个字段。与列定义相互独立,不包含在列定义中;与定义用‘,’分隔;必须指出要约束的列的名称;

【1】列级约束

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别,年龄、入学日期、班级,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;

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

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


【4】总结  


1.主键约束

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

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

2.非空约束

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

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

为已存在表中的字段添加非空约束
 alter   table student8 modify stu_sex varchar(1) not null;
使用ALTER TABLE语句删除非空约束
alter  table student8 modify stu_sex varchar(1) null;
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;

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

alter   table studen11 modify stu_id int(10);

外键约束

代码示例:


-- 学生表删除:
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'),(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)
);
-- 可以一次性添加多条记录:
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3);
-- 查看班级表和学生表:
select * from t_class;
select * from t_student;
-- 删除班级2:如果直接删除的话肯定不行因为有外键约束:
-- 加入外键策略:
-- 策略1:no action 不允许操作
-- 通过操作sql来完成:
-- 先把班级2的学生对应的班级 改为null 
update t_student set classno = null where classno = 2;
-- 然后再删除班级2:
delete from t_class where cno = 2;
-- 策略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;
-- 策略3: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 = 8 where cno = 1;
-- 注意:
-- 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 ;
-- 2.应用场合:
-- (1)朋友圈删除,点赞。留言都删除  --  级联操作
-- (2)解散班级,对应的学生 置为班级为null就可以了,-- set null

  • 2
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

半路出家的码农小王

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值