MySQL约束

约束介绍

数据完整性(Data Integrity) 是指数据的准确性(Accuracy) 和可靠性 (Reliability) 它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或信息而提出的.

为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制

实体完整性 (Entity Integrity)如:同一个表中,不能存在两条完全相同无法区分的记录

域完整性 (Domain Integrity)如:年龄范围0-120;性别范围”男/女”

引用完整性 (Referential Integrity)如:员工所在部门,在部门表中要能找到这个部门

用户自定义完整性 (User-defined Integrity)如:用户名唯一,密码不能为空等

什么是约束

约束是表级的强制规定

可以在创建表时候规定约束 (通过 CREATE TABLE 语句)或者在表创建之后 (通过 ALTER TABLE 语句)规定约束

约束的分类

根据约束数据的列限制;分为
- 单列约束: 每个约束之约束一列
- 多列约束: 每个约束可约束多列数据
根据约束的作用范围;分为
- 列级约束: 只能作用在一个列上,跟在列定义后面
- 表级约束: 可以作用在多个列上,不与列一起,但是单独定义
根据约束起的作用;分为
- NOT NULL非空约束,规定某个字段不能为空
- UNIQUE唯一约束,规定某个字段在整个表中是唯一的
- PRIMARY KEY主键(非空且唯一)约束
- FOREIGN KEY外键约束
- CHECK检查约束
- DEFAULT默认值约束

注意: MySQL不支持check 约束,但可以使用check,而没有效果

查看某个表已有的约束

-- information_schema 数据库名(系统库)
-- table_constraints (专门存储各个表的约束)
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

非空约束

限制某个字段/某列的值不允许为空

关键字

NOT NULL

特点

默认,所有的类型的值都可以还是NULL,包括INT,FLOAT 等数据类型
非空约束只能出现在表对象的列上,只能是某个列单独限定非空,不能组合非空
一个表可以有很多列都分别限定了非空
空字符串不等于NULL,0也不等于NULL

-- 建表时添加非空约束
CREATE TABLE 表名称 (
	字段名 数据类型,
    字段名 数据类型 NOT NULL,
    字段名 数据类型 NOT NULL
);

-- 建表后添加非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

-- 删除非空约束
ALTER TABLE 表名称	MODIFY 字段名 数据类型 NULL (或者 去掉 NOT NULL);

唯一性约束

用来限制某个字段/某列的值不能重复

关键字

UNIQUE

特点

同一个表可以有多个唯一约束
唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一
唯一性约束允许列值为空
在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同
MySQL会给唯一约束的列上默认创建一个唯一索引

-- 创建表时添加
-- 方式一
CREATE TABLE 表名称(
	字段名 数据类型,
    字段名 数据类型 UNIQUE,
    字段名 数据类型 UNIQUE KEY
);
-- 方式二
CREATE TABLE 表名称(
	字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    [CONSTRAINT 约束名] UNIQUE KEY (字段名,[字段名])
);

-- 建表后添加约束
-- 方式一
ALTER TABLE 表名称 ADD UNIQUE KEY (字段列表);
-- 方式二
ALTER TABLE 表名称 MODIFY 字段名 数据类型 UNIQUE;

删除唯一约束

添加唯一约束的列也会自动创建唯一索引
删除唯一约束只能通过删除唯一索引的方式删除
删除时需要自定唯一索引名,唯一索引名就和唯一约束名一样
如果创建唯一约束时没有指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和()中排在第一个列名相同.也可以自定义唯一性约束名.

-- 查看一个数据表都有哪些约束
SELECT * FROM information_schame.table_constraints WHERE table_name = '表名';
-- 也可以通过第二种方式查看表的索引
show index from 表名称;

-- 删除唯一约束
ALTER TABLE 表名称
DROP INDEX 索引名;

主键约束(PRIMARY KEY)

用来唯一标识表中的一行数据

关键字

PRIMARY KEY

特点

主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值.
一个表最多只能有一个主键约束,建立主键约束可在列级别创建,也可以在表级别上创建
主键约束对应着表中的一列或多列
如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复
MySQL的主键名总是PRIMARY就算自己命名了主键约束也不生效
当创建主键约束时,系统默认会在所在的列或者列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高).如果删除主键约束了,主键约束对应的索引就自动删除了
需要注意一点的,不要修改主键字段的值.因为主键是数据记录的唯一标识,如果修改了主键的值,就可能会破坏数据的完整性.

-- 建表时添加主键约束
-- 方式一(列级模式)
CREATE TABLE 表名称(
	字段名 数据类型 PRIMARY KEY,
    字段名 数据类型,
    字段名 数据类型
);
-- 方式二(表级模式)
CREATE TABLE 表名称(
	字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    [CONSTRAINT 约束名] PRIMARY KEY(字段名)
);

-- 建表后添加主键约束
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); # 字段列表可以是一个字段,也可以是多个字段,如果是多个字段的话,就是复合主键

关于复合主键

-- 创建方式
CREATE TABLE 表名称(
	字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    PRIMARY KEY (字段名1,字段名2)
);

删除主键约束
1
ALTER TABLE 表名称 DROP PRIMARY KEY;
注意: 删除主键约束,不需要指定主键名,因为一个表只有一个主键,删除主键约束后,非空还存在.

自增列( AUTO_INCREMENT)

某个字段的值自增

关键字

AUTO_INCREMENT
特点和要求
- 一个表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时,可设置自增长
- 自增长列约束的列必须是键列(主键列,唯一键列)
- 自增约束的列的数据类型必须是整数类型
- 如果自增列指定了0和NULL,会在当前最大值的基础上自增;如果自增列手动指定了具体值,直接赋值为具体值.

如何指定自增约束

-- 建表时指定自增约束
CREATE TABLE 表名称(
	字段名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    字段名 数据类型 UNIQUE KEY NOT NULL,
    字段名 数据类型 UNIQUE KEY,
    字段名 数据类型 NOT NULL DEFAULT 默认值
);

-- 建表后指定自增约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 AUTO_INCREMENT;

-- 删除自增约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型; ## 去掉 auto_increment 相当于删除

MySQL8.0新特性-自增变化的持久化

在MySQL 8.0之前,自增主键auto_increment的值如果大于max(primary key)+1,在MySQL重启后,会重置auto_increment=max(primary key)+1,这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题.

demo

-- 创建的数据表中包含自增主键的id字段
-- 基于MySQL 5.7服务测试
-- 1
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);
-- 2
INSERT INTO test1
VALUES(0),(0),(0),(0);
-- 3
SELECT * FROM test1; -- 结果: 1,2,3,4
-- 4
DELETE FROM test1 WHERE id = 4;
-- 5
INSERT INTO test1 VALUES(0);
-- 6
SELECT * FROM test1; -- 结果: 1,2,3,5
-- 7
DELETE FROM test1 where id=5;
-- 重启数据库,重新插入一个新值
-- 8
INSERT INTO test1 values(0);
-- 9
SELECT * FROM test1; -- 结果: 1,2,3,4


-- 基于MySQL 8.0服务测试
-- 重新执行上面的步骤
-- 9
SELECT * FROM test1; -- 结果: 1,2,3,6

结论: 从结果可以看出,自增变量已经持久化了

MySQL 8.0将自增主键的计数器持久化到重做日志中.每次计数器发生变化,都会将其写入重做日志中.如果数据库重启,InooDB会根据重做日志中的信息来初始化计数器的内存值.

外键约束( FOREIGN KEY)

限定某个表的某个字段的应用完整性

关键字

FOREIGN KEY

特点

从表的外键列,必须引用/参考主表的主键或唯一约束的列
在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生的一个外键名
创建表时就指定外键约束的话,要先创建主表,再创建从表
删表时.先删从表,再删主表
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
在“从表”中指定外键约束，并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名
删除外键约束后，必须手动删除对应的索引

-- 创建表时指定外键约束
create table 主表名称(
	字段1 数据类型 primary key,
	字段2 数据类型
);
create table 从表名称(
	字段1 数据类型 primary key,
	字段2 数据类型,
	[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

-- 创建表后指定外键约束
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

约束等级
- Cascade方式: 在父表上update/delete纪录时,同步update/delete掉子表的匹配记录
- Set NULL方式: 在父表上update/delete记录时,将子表上匹配记录的列设为null;但是主要子表的外键不能为NOT NULL;
- No action方式: 如果子表中有匹配记录,则不允许父表对应候选键进行update/delete操作.
- Restrict方式: 同no action,都是立即检查外键约束
- Set dedault方式:父表有变更时,子表将外键列设置成一个默认的值,但InnoDB不能识别
如果没有指定等级,就相当于Restrict方式.

对于外键约束,最好是采用 ON UPDATE CASCADE ON DELETE RESTRICT的方式

删除外键约束

-- 第一步先查看约束名和删除外键约束
-- 查看某个表的约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
-- 删除外键约束
ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;

-- 第二步查看索引名和删除索引。（注意，只能手动删除）
-- 查看某个表的索引名
SHOW INDEX FROM 表名称;
-- 删除索引
ALTER TABLE 表名称 DROP INDEX 索引名;

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适

合。比如大型网站的中央数据库，可能会 因为外键约束的系统开销而变得非常慢 。所以， MySQL 允

许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不

用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

阿里开发规范
- 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
  - 说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

CHECK约束

检查某个字段的值是否符合xx要求,一般指的是值的范围

关键字

CHECK
说明: MySQL 5.7 不支持

MySQL 5.7可以使用check 约束,但check约束对数据验证没有任何作用.添加数据时,没有任何错误或者警告

MySQL 8.0之后可以使用check约束

使用

-- 创建表字段约束
CREATE TABLE 表名称(
	字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型 check('条件')
);

DEFAULT约束

给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值

关键字

DEFAULT

使用

-- 创建表时添加约束
CREATE TABLE 表名称(
	字段名 数据类型 primary key auto_increment,
    字段名 数据类型 unique ket not null,
    字段名 数据类型 unique key,
    字段名 数据类型 not null default '默认值'
);
-- 说明: 默认值约束一般不在唯一键和主键列上加;
-- 字段名 数据类型 not null default ''; # 默认是空字符串

-- 建表后添加默认约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 default 默认值;
-- 如果这个字段原来有非空约束,你还要保留非空约束,那么在加默认值约束时,还得保留非空约束,否则非空约束就被删除了;
-- 同理,再给某个字段加非空约束也一样,如果这个字段原来有默认值约束,你想保留,也要在modify语句中保留默认值约束,否则就删除了
ALTER TABLE 表名称 MODIFY 字段名 数据类型 default '默认值' not null;

删除默认值约束

-- 删除默认值约束
ALTER TBALE 表名称 MODIFY 字段名 数据类型 ; # 删除默认值约束,也不保留非空约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 not null; # 删除默认值约束,保留非空约束

扩展

tip1

a: 为什么建表时,加 NOT NULL DEFAULT '' 或 DEFAULE 0.
b: 不想让表中出现 NULL 值

tip2

a: 为什么不想要 NULL 值
b: 1;不好比较.NULL是一种特殊值,比较时只能用专门的 IS NULL 和 IS NOT NULL 来比较.碰到运算符,通常返回 NULL
2;效率不高.影响提高索引效果.因此,在建表时加入 NOT NULL DEFAULT '' 或者 DEFAULT 0.

tip3

a: 带 AUTO_INCREMENT 约束的字段值是从1开始的嘛?
b: 在MySQL中,默认auto_increment的初始值是1,每新增一条记录,字段值自动加1.设置自增属性(AUTO_INCREMENT)的时候,还可以指定第一条插入记录的自增字段的值,这样新插入的记录的自增字段值从初始值开始递增,如在表中插入第一条记录,同时指定id值为5,则以后插入的记录的id值就会从6开始往上增加.添加主键约束时,往往需要设置字段自动增加属性.

tip4

a: 并不是每个表都可以任意选择存储引擎
外键约束(foreign key) 不能跨引擎使用.在MySQL中,支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,需要注意的是:外键约束是用来保存数据的参照完整性的,如果表之前需要关联外键,却指定了不同的存储引擎,那么这些表之间是不能创建外键约束的.所以说,存储引擎的选择也是不完全随意的.

MySQL约束

约束介绍

什么是约束

约束的分类

非空约束

唯一性约束

主键约束(PRIMARY KEY)

注意: 删除主键约束,不需要指定主键名,因为一个表只有一个主键,删除主键约束后,非空还存在.

自增列( AUTO_INCREMENT)

外键约束( FOREIGN KEY)

CHECK约束

DEFAULT约束

扩展