MySQL约束

一、约束概述

查看某个表已有的约束

SELECT * FROM 
information_schema.table_constraints 
WHERE table_name = '表名称';

1. 什么是约束

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

2. 为什么需要约束

数据完整性是指数据的精确性和可靠性，它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制

• 实体完整性：如同一个表中不能出现两条完全相同的记录
• 域完整性：如性别范围（男女）
• 引用完整性：如员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性：如用户名唯一，密码不能为空

3. 约束分类

3.1 根据约束数据列的限制

• 单列约束：每个约束只约束一列
• 多列约束：每个约束可以约束多列数据

3.2 根据约束的作用范围

• 列级约束：只能作用在一个列上，跟在列的定义后面
• 表级约束：可以作用在多个列上，不与列一起，单独定义

3.3 根据约束起的作用

• NOT NULL：非空约束，规定某个字段不能为空
• UNIQUE：唯一约束，规定某个字段在整个表中是唯一的
• PRIMARY KEY：主键（非空且唯一）
• FOREIGN KEY：外键
• CHECK：检查约束
• DEFAULT：默认值约束

二、约束详解

1. 非空约束

1.1 作用

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

1.2 关键字

NOT NULL

1.3 特点

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

1.4 非空约束的操作实例

CREATE TABLE c_student (
	id INT NOT NULL, 
	NAME VARCHAR (20) NOT NULL 
);

2. 唯一性约束

2.1 作用

用来限制某个字段/列的值不能重复（允许出现多个空值：NULL）

2.2 关键字

UNIQUE

2.3 特点

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

2.4 唯一性约束操作实例

# 创建唯一索引
CREATE TABLE u_course(
	cid INT UNIQUE,
	cname VARCHAR(100) UNIQUE,
	description VARCHAR(200)
);

# 创建唯一复合索引
CREATE TABLE u_user( 
	id INT NOT NULL,
	name VARCHAR(25),
	password VARCHAR(16),
	CONSTRAINT uk_name_pwd UNIQUE(name,password)
);

3. PRIMARY KEY约束

3.1 作用

用来唯一标识表中的一行记录

3.2 关键字

PRIMARY KEY

3.3 特点

• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许空值
• 一个表中最多只能有一个主键，建立主键可以在列级建立，也可以在表级建立
• 主键约束对应者表中的一列或多列（复合主键）
• 如果是多列组合的复合主键，那么这些列都不能为空，并且组合的值不能重复
• MySQL主键名总是PRIMARY KEY，就算自己命名了主键约束也没用
• 当创建主键约束时，系统默认会在所在列或组合上建立对应的主键索引
• 注意：不要修改主键字段的值，因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能破坏数据的完整性

3.4 主键操作实例

create table p_temp(
	id int primary key,
	name varchar(20)
);

4. AUTO_INCREMENT（自增）

4.1 作用

某个字段的值自增

4.2 关键字

auto_increment

4.3 特点

• 一个表最多只能有一个自增列
• 当需要产生唯一标识符或顺序值时，可设置自增长
• 自增长的列必须是键列（主键列，唯一键列）
• 自增约束的列必须是整数类型
• 如果自增列指定了0和null，会在当前最大的值的基础上自增，如果自增列手动指定了具体的值，直接赋值为具体值

4.4 自增约束实例

create table a_employee(
	eid int primary key auto_increment,
	ename varchar(20)
);

5. FOREIGN KEY约束

5.1 作用

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

5.2 关键字

FOREIGN KEY

5.3 主从（父子）表

• 主（父）表：被引用的表
• 从（子）表：引用别人的表

举例说明：员工表的员工所在的部门这个字段的值要引用部门表，部门表则是主表，员工表为从表

5.4 特点

• 从表的外键列必须引用主表的主键或唯一约束的列
• 创建外键约束时，如果不给外键命名，会自动生成一个外键名（不是列名），也可以指定外键名
• 创建表时就创建外键约束时，需先创建主表，再创建从表
• 删表时，先删从表（或先删除外键约束），再删除主表
• 当主表的数据被从表引用时，主表的数据不允许删除，如果要删除数据，需先删除从表依赖的数据
• 一个表中可以建立多个外键约束
• 从表的外键列必须与主表被引用的列的数据类型保持一致，逻辑意义保持一致，
• 删除外键约束后，需手动删除对应的索引

5.5 外键操作实例

create table f_dept(
	did int primary key,
	dname varchar(50)
);

create table f_emp(
	eid int primary key,
	ename varchar(5),
	deptid int,
	foreign key (deptid) references f_dept(did)
);

5.6 阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决

说明：学生表中的student_id是主键，那么成绩表中的student_id则为外键，如果更新学生表中的student_id，同时需要更新成绩表中的student_id，即为级联更新。外键与级联更适合与单机低并发，不适合分布式，高并发集群；级联更新是强阻塞，存在数据库更新风暴的危险，外键影响数据库的插入速度

6. CHECK

6.1 作用

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

6.2 关键字

CHECK

6.3 CHECK约束操作实例

create table check_user(
	age int check(age>18)
);

6.4 版本说明

MySQL5.7不支持CHECK约束，可以使用，但是CHECK约束对数据验证没有效果
MySQL8.0之后可以使用CHECK约束

7. DEFAULT

7.1 作用

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

7.2 关键字

DEFAULT

7.3 DEFAULT约束操作实例

1. 给字段添加默认值（建表时添加）

# 默认值约束
create table d_employee( 
	eid int primary key,
	ename varchar(20) not null,
	egender char default '男',
	etel char(11) not null default ''
);

2. 删除默认值约束
   alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束alter table 表名称
   alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

#删除gender字段默认值约束，如果有非空约束，也一并删除
alter table d_employee modify egender char;

#删除tel字段默认值约束，保留非空约束
alter table d_employee modify etel char(11)  not null;

3. 给字段添加默认值（建表后添加）

#给gender字段增加默认值约束
alter table d_employee modify egender char default '男';

#给tel字段增加默认值约束，并保留非空约束
alter table d_employee modify etel char(11) default ''  not null;