约束
数据完整性(Data Integrity)
1、指数据的精确性(Accuracy)和可靠性(Reliability)
2、防止数据库中存在不符合语义规定的数据,防止因错误信息的输入输出,造成无效操作或错误信息
3、为了保证数据的完整性,SQL 规范以约束的方式对表数据进行额外的条件限制
(1)实体完整性(Entity Integrity) :例如,同一个表中,不能存在两条完全相同无法区分的记录
(2)域完整性(Domain Integrity) :例如:年龄范围 0-120,性别范围 男/女
(3)引用完整性(Referential Integrity) :例如:员工所在部门,在部门表中要能找到这个部门
(4)用户自定义完整性(User-defined Integrity) :例如:用户名唯一、密码不能为空等
约束
1、表级的强制规定,确保数据库的数据满足特定规则
2、规定约束
(1)在创建表时,通过 CREATE TABLE 语句
(2)在表创建之后,通过 ALTER TABLE 语句
约束分类
1、根据约束数据列的限制
(1)单列约束:每个约束只约束一列
(2)多列约束:每个约束可约束多列数据
2、根据约束的作用范围
(1)列级约束:只能作用在一个列上,跟在列的定义后面
(2)表级约束:可以作用在多个列上,不与列一起,而是单独定义
位置 | 支持的约束类型 | 是否可以起约束名 | |
列级约束 | 列的后面 | 语法都支持,但外键没有效果 | 不可以 |
表级约束 | 所有列的下面 | 默认和非空不支持,其他支持 | 可以(主键没有效果) |
3、根据约束起的作用
(1)NOT NULL:非空约束,规定某个字段不能为空
(2)UNIQUE:唯一约束,规定某个字段在整个表中是唯一的
(3)PRIMARY KEY:主键(非空且唯一)约束
(4)FOREIGN KEY:外键约束
(5)CHECK:检查约束,MySQL 不支持 CHECK 约束,但可以使用,而没有任何效果
(6)DEFAULT:默认值约束
非空约束
1、关键字:NOT NULL
2、特点
(1)默认,所有的类型的值都可以是 NULL,包括 INT、FLOAT 等数据类型
(2)非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空
(3)一个表可以有很多列都分别限定非空
(4)空字符串 '' 不等于 NULL,0 也不等于 NULL
3、添加非空约束
(1)建表时
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
(2)建表后
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
4、删除非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
#或
ALTER TABLE 表名称 MODIFY 字段名 数据类型;
唯一性约束
1、作用:用来限制某个字段/某列的值不能重复
2、关键字:UNIQUE
3、特点
(1)同一个表可以有多个唯一约束
(2)唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一
(3)唯一性约束允许列值为空
(4)在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同
(5)MySQL会给唯一约束的列上默认创建一个唯一索引
4、添加唯一约束
(1)建表时
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 UNIQUE,
字段名 数据类型 UNIQUE KEY,
字段名 数据类型
);
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[CONSTRAINT 约束名] UNIQUE KEY(字段名)
);
(2)建表后指定唯一键约束
#字段列表中如果是一个字段,表示该列的值唯一
#字段列表中如果是多个字段,多个字段名用逗号分隔,那么复合唯一,即多个字段的组合是唯一的
ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表);
ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;
5、删除唯一约束
(1)添加唯一性约束的列上也会自动创建唯一索引
(2)删除唯一约束只能通过删除唯一索引的方式删除
(3)删除时需要指定唯一索引名,唯一索引名就和唯一约束名相同
(4)如果创建唯一约束时,未指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和 () 中排在第一个的列名相同,也可以自定义唯一性约束名
ALTER TABLE USER DROP INDEX 唯一约束名;
主键约束
1、作用:唯一标识表中的一行记录
2、关键字:PRIMARY KEY
3、特点
(1)主键约束 <=> 唯一约束 + 非空约束,主键约束列不允许重复,也不允许出现空值
(2)一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建
(3)主键约束对应着表中的一列或者多列(复合主键)
(4)如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复
(5)MySQL 主键名总是 PRIMARY,自定义主键约束名无效
(6)当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高),如果删除主键约束了,则自动删除主键约束对应的索引
(7)注意:不要修改主键字段的值,因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性
4、添加主键约束
(1)建表时指定主键约束
#列级模式
CREATE TABLE 表名称(
字段名 数据类型 PRIMARY KEY,
字段名 数据类型,
字段名 数据类型
);
#表级模式
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[CONSTRAINT 约束名] PRIMARY KEY(字段名)
);
(2)建表后增加主键约束
#字段列表可以是一个字段,也可以是多个字段,如果是多个字段的话,则是复合主键
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);
5、删除主键约束
ALTER TABLE 表名称 DROP PRIMARY KEY;
(1)删除主键约束,不需要指定主键名,因为一个表只有一个主键,删除主键约束后,非空还存在
6、添加主键约束时,往往需要设置字段自动增加属性
自增列
1、作用:某个字段的值自增
2、关键字:AUTO_INCREMENT
3、特点、要求
(1)一个表最多只能有一个自增长列
(2)当需要产生唯一标识符或顺序值时,可设置自增长
(3)自增长列约束的列必须是键列(主键列,唯一键列)
(4)自增约束的列的数据类型必须是整数类型
(5)如果自增列指定 0 和 NULL,会在当前最大值的基础上自增;如果自增列手动指定具体值,直接赋值为具体值
(6)初始值默认从 1 开始,若在插入指定数值,则下一次增长从指定数值开始
4、指定自增约束
(1)建表时
CREATE TABLE 表名称(
字段名 数据类型 PRIMARY KEY AUTO_INCREMENT,
);
CREATE TABLE 表名称(
字段名 数据类型 UNIQUE KEY AUTO_INCREMENT,
);
(2)建表后
ALTER TABLE 表名称 MODIFY 字段名 数据类型 AUTO_INCREMENT;
5、删除自增约束
#去掉AUTO_INCREMENT,相当于删除
ALTER TABLE 表名称 MODIFY 字段名 数据类型;
6、MySQL 8.0 新特性:自增变量的持久化
(1)在 MySQL 8.0 之前,自增主键 AUTO_INCREMENT 的值如果大于 max(primary key)+1,在 MySQL 重启后,会重置 AUTO_INCREMENT =max(primary key)+1,这种现象在某些情况下,会导致业务主键冲突,或其他难以发现的问题
(2)在 MySQL 5.7 中,对于自增主键的分配规则,是由 InnoDB 数据字典内部一个计数器来决定的,而该计数器只在内存中维护 ,并不会持久化到磁盘中,当数据库重启时,该计数器会被初始化
(3)MySQL 8.0 将自增主键的计数器持久化到重做日志中,每次计数器发生改变,都会将其写入重做日志中,如果数据库重启,InnoDB 会根据重做日志中的信息来初始化计数器的内存值
外键约束
1、作用:限定某个表的某个字段的引用完整性
2、关键字:FOREIGN KEY
3、主表和从表 / 父表和子表
(1)主表(父表):被引用的表,被参考的表
(2)从表(子表):引用别人的表,参考别人的表
4、特点
(1)从表的外键列,必须引用主表的主键或唯一约束的列,因为被依赖的值必须是唯一的
(2)在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名,也可以指定外键约束名
(3)若创建表时就指定外键约束,则先创建主表,再创建从表
(4)删表时,先删从表(或先删除外键约束),再删除主表
(5)当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据
(6)在从表中指定外键约束,并且一个表可以建立多个外键约束
(7)从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致,如果类型不一样,创建子表时,就会出现错误:ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)
(8)当创建外键约束时,系统默认会在所在的列上建立对应的普通索引,索引名是外键的约束名(根据外键查询效率高)
(9)删除外键约束后,必须手动删除对应的索引
5、添加外键约束
(1)建表时
CREATE TABLE 主表名称(
字段1 数据类型 PRIMARY KEY,
字段2 数据类型
);
#FOREIGN KEY: 在表级指定子表中的列
#REFERENCES: 标示在父表中的列
CREATE TABLE 从表名称(
字段1 数据类型 PRIMARY KEY,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) REFERENCES 主表名(被参考字段) [ON UPDATE 约束等级][ON DELETE 约束等级]
);
(2)建表后:一般情况下,表与表的关联都是提前设计好的,因此,会在创建表的时候就把外键约束定义好
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [ON UPDATE 约束等级][ON DELETE 约束等级];
6、约束关系针对双方
(1)添加外键约束后,主表的修改和删除数据受约束
(2)添加外键约束后,从表的添加和修改数据受约束
(3)在从表上建立外键,要求主表必须存在
(4)删除主表时,要求从表从表先删除,或将从表中外键引用该主表的关系先删除
7、约束等级
(1)CASCADE 方式:在父表上 UPDATE / DELETE 记录时,同步 UPDATE / DELETE 子表的匹配记录
(2)SET NULL 方式:在父表上 UPDATE / DELETE 记录时,将子表上匹配记录的列设为 NULL,但子表的外键列不能为 NOT NULL
(3)NO ACTION 方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行 UPDATE / DELETE 操作
(4)RESTRICT 方式 :同 NO ACTION, 都是立即检查外键约束
(5)SET DEFAULT 方式:父表有变更时,子表将外键列设置成一个默认的值,但 InnoDB 不能识别(在 SQLyog 中可能显示空白)
(6)如果没有指定等级,就相当于 RESTRICT 方式
(7)对于外键约束,最好是采用:ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
8、删除外键约束
(1)查看约束名和删除外键约束
#查看某个表的约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
(2)查看索引名和删除索引(只能手动删除)
#查看某个表的索引名
SHOW INDEX FROM 表名称;
ALTER TABLE 从表名 DROP INDEX 索引名;
9、开发
(1)不得使用外键与级联,一切外键概念必须在应用层解决
(2)MySQL 允许不使用系统自带的外键约束,在应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性
(3)外键与级联更新适用于单机低并发 ,不适合分布式、高并发集群
(4)级联更新是强阻塞,存在数据库更新风暴的风险
(5)外键影响数据库的插入速度
10、外键约束不能跨引擎使用
检查约束
1、作用:检查某个字段的值是否符合要求,一般指的是值的范围
2、关键字:CHECK
3、MySQL 5.7 不支持,只做语法校验,但没有任何作用,添加数据时,没有任何错误或警告
4、MySQL 8.0 可以使用
默认约束
1、作用:给某个字段指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值
2、关键字:DEFAULT
3、默认值约束不与唯一约束、主键约束搭配
4、添加默认值
(1)建表时
CREATE TABLE 表名称(
字段名 数据类型 NOT NULL DEFAULT 默认值,
);
(2)建表后
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值;
#如果这个字段原来有非空约束,若还保留非空约束,那么在加默认值约束时,保留非空约束,否则非空约束被删除;某个字段加非空约束同理
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值 NOT NULL;
5、删除默认值约束
#删除默认值约束,也不保留非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型;
#删除默认值约束,保留非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
6、NOT NULL DEFAULT 或 DEFAULT 0
(1)不让表中出现 NULL 值
(2)不易比较,NULL 是一种特殊值,比较时只能用专门的 IS NULL 和 IS NOT NULL 来比较,碰到运算符,通常返回 NULL
(3)效率不高,影响提高索引效果
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战