MySQL常见约束

1、约束基本概念

• 约束（constraint）：对表中字段数据进行限制的条件。

• 约束的作用：为了保证数据的完整性、一致性、有效性…

• 例如：常见的登入账号进行了一些约束，使得每个账号只能被一个人注册且持有！

• 常见的约束：

  1. 非空约束（not null）：被约束的字段在插入数据时该列的值不能为null

  2. 唯一约束（unique）：被约束的字段在表中有且只有一条，即不能重复，但是可以为null。

  3. 主键约束（PK）：主键约束是对唯一约束的增强，主键约束的字段即不能为null、也不能重复。

  4. 外键约束（FK）：外键约束是指用于在两个表之间建立关系；A表中的某个字段被B表引用！

  5. 检查约束（check）：MySQL并不支持检查约束、Oracle支持

2、非空约束（not null）

非空约束是一种列级约束，只能将not null放在列的定义中，不能单独成行。

create table user(
    `id` int comment '编号',
    `username` varchar(20) not null comment '账号',
    `password` varchar(20) not null comment '密码'
)engine = innodb default charset=utf8;

-- 成功执行
insert into user(username, password) values ('admin', '123456')


-- 错误执行
insert into user(id, username) values(1, 'admin')
错误代码： 1364
field 'password' doesn't have a default value

由于对username、password两个字段进行了not null非空约束，那么在插入新数据时比如给定这两个字段的数据，而id字段是可以不给出的。

3、唯一约束（unique）

唯一约束：唯一约束有两种定义形式，列级约束、表级约束。

3.1、列级约束

列级约束：直接在列字段的后面加入unique关键词。

create table user(
    `id` int unique,
    `username` varchar(20),
    `password` varchar(20)
)engine = innodb default charset=utf8;

insert into user(id, username, password) values (1, 'admin', '123456')

insert into user(id, username, password) values (1, 'root', '123456')
-- 错误代码： 1062
-- Duplicate entry '1' for key 'id'

3.2、行级约束

标记约束：使用unique关键词单独成行，可以同时约束多个字段一起作为唯一性约束。

个人比较喜欢行级约束，容易清楚的看到约束情况。

create table user(
    `id` int,
    `username` varchar(20),
    `password` varchar(20),
    unique(`id`)
)engine = innodb default charset=utf8;

这种定义方式与上述表的创建结果是一样的，没有任何区别。

3.3、补充

行级约束可以使用多个字段一起构成约束，这时候表示这两个字段一起构成唯一性约束；

• 可以单个字段重复，但是不能两个字段同时重复！

• id - admin两个字段组合不能完全相同。

create table user(
    `id` int,
    `username` varchar(20),
    `password` varchar(20),
    unique(`id`, `username`)
)engine = innodb default charset=utf8;
-- 正确
insert into user values 
(1, 'admin', '123456')
(2, 'admin', '123456')

-- 错误
insert into user values 
(1, 'admin', '123456')
错误代码： 1062
Duplicate entry '1-admin' for key 'id'

行级约束中指定多个字段与拆分多个列级约束是不一样的效果。

-- 第一种
create table user(
    `id` int,
    `username` varchar(20),
    `password` varchar(20),
    unique(`id`, `username`)
)engine = innodb default charset=utf8;

-- 第二种
create table user(
    `id` int,
    `username` varchar(20) unique,
    `password` varchar(20) unique
)engine = innodb default charset=utf8;

这两种定义的结果是不一样的！

• 第一种：这种表示id - username组合不能重复，也就是两个字段的值不能同时取一样的。只有一个唯一性约束，而这一个唯一性约束是由两个字段共同组合而成

• 第二种：这种定义表示id、username两个字段各自都不能重复，也就是这里有两个唯一性约束

4、主键约束（primary key）

• 主键约束：最重要的约束没有之一，任何一张表都应该都一个主键，且一张表只能有一个主键！

• 主键的作用：

  1. 遵循表的第一范式设计原则，任何一张表都应该都一个主键。

  2. 主键在表中起到唯一标识的作用（类似于身份证）

• 主键类型：

  1. 单一主键：只由一个字段构成。

  2. 复合主键：由多个字段联合起来组成的主键（不推荐使用，违背第三范式）

• 主键特点：

  1. 一定是非空的（not null）

  2. 不能值重复（unique）

create table user(
    `id` int,
    `username` varchar(20),
    `password` varchar(20),
    primary key(`id`)
)engine = innodb default charset=utf8;

-- 正确
insert into user (id,  username, password) values ('1', 'admin', '123456')

-- 错误代码： 1364
-- Field 'id' doesn't have a default value
-- 主键值为null
insert into user (username, password) values ('admin', '123456')

-- 错误代码： 1062
-- Duplicate entry '1' for key 'PRIMARY'
-- 主键值重复
insert into user (id,  username, password) values ('1', 'root', '123456')

补充：主键也可以根据性质来划分

1. 业务主键：主键值和业务挂钩，例如身份证直接当做主键的，这种是极其不推荐的；当业务发生改变时主键值可能也需要修改甚至出现无法修改的局面。

2. 自然主键：经常看到的某个表中的主键都会采用一个id，这个id与业务无关，仅仅作为唯一性标识！

---

自增：大多数情况下主键都会搭配auto_increment自增来使用，这时候在插入数据时主键字段就不需要指定主键值了，主键值会自动给定！

5、外键约束 （foreign key）

• 外键约束：主要是为了规范表与表之间的数据规范而提出的约束，在外键约束中存在严格的父表、子表之分。

• 父子表的顺序问题：

  1. 删除数据的时候，先删除子表，在删除父表。

  2. 添加数据的时候，先添加父表数据，在添加子表子表数据

  3. 创建表的时候，先创建父表，在创建子表。

  4. 删除表的时候，先删除子表，在删除父表。

出现上述要求的原因：

1. 父表中的某个字段成为子表中的外键时，这时候子表的外键值是参考父表数据的！

2. 子表中的外键数据只能是父表中有的数据！

3. 由于子表的外键数据是参考父表的数据，因此父表中的该字段必须保证唯一性，也就说父表中的这个字段必须是一个unique字段，当然也可以是primary key。

4. 子表的外键值可以是null，但是不能出现父表中没有的数据。

总之：围绕引用（references）解决解释上述的问题，就像一颗二叉树；创建的时候必须先有根（父节点），删除的时候必须先剪枝（子节点）！

create table clazz(
    cno int comment '班级编号',
    cname varchar(20) comment '班级名称',
    unique(cno)	-- 也可以是primary key主键
)engine = innodb default charset=utf8


create table student(
    sid int comment '学生编号',
    sname varchar(20) comment '姓名',
    cno int comment '班级编号',
    foreign key(cno) references clazz(`cno`) -- 外键
)engine = innodb default charset=utf8;