约束

定义

为什么需要约束

保证数据的完整性

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

实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

是什么

约束是表级的强制规定

非空

是什么

不允许为空

注意：

只可以单列使用

0和空串都不是NULL

使用

添加非空约束

建表前

CREATE TABLE IF NOT EXISTS c1 (
    id INT(11) AUTO_INCREMENT COMMENT 'id',
    `name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '姓名',
    `age` INT(11) NOT NULL COMMENT '年龄',  
    PRIMARY KEY(id)
)

建表后

alter table 表名称 modify 字段名 数据类型 not null;

删除非空约束

ALTER TABLE c1
MODIFY age int;

唯一性约束

是什么

某个字段的值不能重复

可以在多个列
允许为空
如果没指定约束名中则默认和列名相同
同时默认会创建一个唯一索引

使用

添加唯一性约束

CREATE TABLE IF NOT EXISTS c2 (
    id INT(11) AUTO_INCREMENT COMMENT 'id',
    `name` VARCHAR(255) NOT NULL UNIQUE KEY DEFAULT '' COMMENT '姓名',
    `age` INT(11) NOT NULL COMMENT '年龄',  
    PRIMARY KEY(id)
)


CREATE TABLE IF NOT EXISTS c21 (
    id INT(11) AUTO_INCREMENT COMMENT 'id',
    `name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '姓名',
    `age` INT(11) NOT NULL COMMENT '年龄',  
    PRIMARY KEY(id),
        CONSTRAINT AGE_NAME_UNI UNIQUE KEY(name, age)
)

alter table 表名称 add unique key(字段列表);


alter table 表名称 modify 字段名 字段类型 unique;

删除唯一性约束

唯一约束名和索引名相同

如果没指定，则和列名相同（组合列则是第一个列）

#查看都有哪些约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; 



ALTER TABLE USER
DROP INDEX uk_name_pwd;

主键

是什么

唯一表示表中的一条记录唯一+为空

可以是一个列也可以是多个列组合

一个表最多有一个主键

主键名总是PRIMARY

创建了主键自动创建主键索引，同时如果删除主键自动删除主键索引

使用

添加主键约束

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

CREATE TABLE emp5(
  id INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20),
  pwd VARCHAR(15),
  CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

删除主键约束

ALTER TABLE student DROP PRIMARY KEY;

AUTO_INCREMENT

是什么

某个字段的值自增

AUTO_INCREMENT

一个表只能有一个自增列，这个列必须是整数类型+(唯一列 or 主键列)

使用

添加自增

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

alter table employee modify eid int auto_increment;

删除约束

alter table employee modify eid int;

变化

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

外键

是什么

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

比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

主表（父表）：被引用的表，被参考的表从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

特点

从表的外键列，必须引用/参考主表的主键或唯一约束的列

创建表先主表后从表，删除表先从后主插入+删除数据也是一样的

删除外键约束后需要手动删除对应的索引

使用

添加外键约束

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

删除约束

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


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

约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action，都是立即检查外键约束
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

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

check

是什么

定义

校验某个字段是否满足条件

CHECK

mysql5.7 check并不起作用， 8.0起作用

使用

添加check约束

create table employee(
  eid int check(eid > 0) ,
  ename varchar(20)
);

default

是什么

定义

给某个字段指定默认值，如果没有显示赋值则采用默认值

DEFAULT

使用

添加默认值

create table employee(
  eid int primary key,
  ename varchar(20) not null,
  gender char default '男',
  tel char(11) not null default '' #默认是空字符串
);