MySql基础篇——约束

一、约束概述

1.1 为什么需要约束

数据完整性是指数据的精确性和可靠性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
（1） 实体完整性：例如，同一个表中，不能存在两条完全相同无法区分的记录。
（2） 域完整性：例如，年龄范围0~120，性别范围“男/女”。
（3） 引用完整性：例如，员工所在部门，在部门表中要能找到这个部门。
（4） 用户自定义完整性：例如：用户名唯一，密码不能为空。

1.2 约束的分类

约束可以在创建表时规定约束（CREATE TABLE语句），或者在表创建之后通过（ALTER TABLE语句）规定约束。

• 根据约束数据列的限制，约束可分为：单列约束和多列约束
• 根据约束的作用范围，约束可分为：列级约束和表级约束

• 根据约束的作用，约束可分为：

（1） not null 非空约束，规定某个字段不能为空。
表级约束和单列约束。
（2） unique 唯一约束。
表级约束，列级约束，单列约束，多列约束。
如果不给唯一约束命名，就默认和列名相同。
（3） primary key  主键约束。
（4） foreign key  外键约束。
（5） check 检查约束，mysql不支持check约束，没效果。
（6） default 默认值约束。

1.3 information_schema.table_constraints

CONSTRAINT_CATALOG: 约束所属的目录的名称。此值始终为def
CONSTRAINT_SCHEMA: 约束所属的架构（数据库）的名称
TABLE_SCHEMA: 表所属的模式（数据库）的名称。
TABLE_NAME: 表名
ENFORCED： 对于CHECK约束，值是 YES或NO表示是否强制执行约束。对于其他约束，该值始终为YES。

二、约束详解

2.1 唯一性约束

（1）在创建时添加：

CREATE TABLE test1(
	id INT UNIQUE, # 列级约束
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2),
	CONSTRAINT ownname unique(email,id) # 表级约束,多列约束
);

唯一性约束的字段可以添加多个null值。
（2）在修改时添加：

ALTER TABLE test1 ADD CONSTRAINT 约束名 unique(salary);
ALTER TABLE test1 modify last_name varchar(100) unique;

（3）删除唯一性约束
添加唯一性约束的列上也会自动创建索引。
删除唯一性约束只能通过删除索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果时单列，就默认和列名相同。如果是组合列，那么默认和排在第一个列名相同。

ALTER TABLE 表名 DROP INDEX 索引名

2.2 主键约束

主键约束相当于唯一约束+非空约束，如果是多列组合则要求每一列都不能未null。

• 一个表最多只能有一个主键约束。
• 表级约束和列级约束，单列约束和多列约束。
• MySql主键约束名总是PRIMARY，就算自己命名了也没用。
• 当创建主键约束时，系统默认会再所在列或列组合上创建对应的主键索引

（1）在创建时添加：

CREATE TABLE test1(
	id INT primary key, # 列级约束
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2),
	CONSTRAINT pk PRIMARY KEY(id) # 表级约束,多列约束,但是执行不成功，应为一个表中最多只能有一个主键约束。
);

（2）在修改时添加：

ALTER TABLE test1 PRIMARY KEY(salary);

（3）删除：

ALTER TABLE test1 drop PRIMARY KEY;

2.3 自增列（AUTO_INCREMENT）

可以实现某个列自增长

• 一个表最多只能有一个自增长列。
• 自增长列约束的列必须是主键列或唯一约束键列。
• 自增长列约束的列必须是整数类型。
• 如果自增长指定了0和null，会在当前最大值的基础上自增。如果指定了具体值，直接赋值为具体值。

（1）在创建时添加：

CREATE TABLE test1(
	id INT primary key auto_increment, # 列级约束
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2)
);

(2) 在修改时添加

ALTER TABLE test1 modify id int auto_increment;

(3) 在修改时删除

ALTER TABLE test1 modify id int;

(4) mysql8.0新特性——自增变量的持久化
在mysql8.0之前，如果将当前自增的值10删除，如果没重启下一个自增值为11，如果重启了下一个自增值为10。在mysql8.0之后，重启后下一个自增值也为11。

2.4 外键约束（FOREIGN KEY）

• 外键列必须引用/参考主表的主键或唯一约束的列。
• 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名。
• 创建表时先创建主表，再创建从表。
• 删除表时，先删除从表（或先删除外键约束），再删除主表。
• 当主表记录被从表参照时，主表的记录将不允许删除。
• 从表的外键列和主表被参照的列可以名字不同，但是数据类型必须一样。
• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引，索引名是列名。
• 删除外键约束后，必须手动删除对于索引

（1）在创建时添加：

CREATE TABLE test1(
	id INT, # 列级约束
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2),
	constraint 外键名 foreign key (外键列名) REFERENCES 主键表名(主键列名)
);

(2) 在修改时添加

ALTER TABLE test1 ADD CONSTRAINT constraint 外键名 foreign key (外键列名) REFERENCES 主键表名(主键列名);

(3) 约束等级

对于外键约束，最好采用：ON UPDATE CASCADE ON DELETE RESTRICT的方式。

CREATE TABLE test1(
	id INT, # 列级约束
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2),
	foreign key (外键列名) REFERENCES 主键表名(主键列名) ON UPDATE CASCADE ON DELETE RESTRICT
);

(4) 在修改时删除

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
ALTER TABLE 从表名 DROP INDEX 索引名;

(5) 阿里开发规范
不得使用外键和几联，一切外键概念必须在应用层解决。

2.5 CHECK约束

用来检查某个字段的值是否符合xx要求，一般指的是值的范围。Mysql5.7可以使用check约束，但是没有任何作用。

（1）在创建时添加：

CREATE TABLE test1(
	id INT, # 列级约束
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2) CHECK(salary > 2000)
);

2.6 DEFAULT约束

（1）在创建时添加：

CREATE TABLE test1(
	id INT, # 列级约束
	last_name VARCHAR(15),
	email VARCHAR(25),
	salary DECIMAL(10,2) DEFAULT 2000
);

（2）在修改时添加：

ALTER TABLE 从表名 modify 字段名 数据类型 default 默认值;

（3）注意事项

• 建表时加not null default “” 或 default 0是不想让表中出现null值。应为null值不好比较，参与什么运算都是null，还影响效率。

• 外键约束不能夸引擎使用。