DBA MySQL约束条件
约束一览
约束条件与数值类型的显示宽度均为可选参数，其作用在于保证数据的完整性与一致性。
常用完整性约束如下表所示：
| 约束条件 | 描述 |
|---|---|
| DEFAULT | 为该字段设置默认值 |
| NOT NULL | 标识该字段不能为空（非字符串有效） |
| UNIQUE KEY | 标识该字段的值是唯一的 |
| PRIMARY KEY | 标识该字段为该表的主键，可以唯一的标识记录 |
| AUTO_INCREMENT | 标识该字段的值自动增长（整数类型，而且为主键） |
| UNSIGNED | 设定数值类型存入方式为无符号，该约束条件必须紧跟在类型声明后，如INT UNSIGNED |
| ZEROFILL | 使用0对显示宽度进行填充，该约束推荐跟在类型声明或无符号存储约束声明后 |
| FOREIGN KEY | 标识该字段为该表的外键 |
约束条件属于表结构中的一环，所以可以使用DESC 表名;来进行查看。
M > DESC userinfo;
+--------+----------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(12) | NO | | NULL | |
| age | tinyint(3) | NO | | 0 | |
| gender | enum('male','female','unknow') | YES | | unknow | |
| hobby | set('basketball','football','volleyball','unknow') | YES | | unknow | |
+--------+----------------------------------------------------+------+-----+---------+----------------+
DEFAULT
用于在创建表时为字段设置默认值，当插入时可以省略该字段的数据插入而去使用设定的默认值，如不设定默认值为NULL。
示例演示，为gender字段设置默认值：
gender ENUM("male", "female", "unknow") DEFAULT "unknow" COMMENT "用户性别",
在插入数据时，不指定gender字段，则默认插入unknow选项：
M > INSERT INTO
userinfo(name, age, hobby)
VALUES
("Jack", 22, "basketball,football");
M > SELECT * FROM userinfo WHERE id=1 LIMIT 1;
+----+--------------+-----+--------+---------------------+
| id | name | age | gender | hobby |
+----+--------------+-----+--------+---------------------+
| 1 | Jack | 22 | unknow | basketball,football |
+----+--------------+-----+--------+---------------------+
NOT NULL
代表非字符类型的字段不能为空，必须为该字段插入值。
常和DEFAULT一起使用。
当非字符类型的字段约束为NOT NULL且没有默认值的情况下，插入记录时不指定该字段将会抛出1364的异常：
ERROR 1364 (HY000): Field '字段名' doesn't have a default value
UNIQUE
唯一限制
即该字段的记录必须拥有唯一性，如下表中展示的数据就不具有唯一性：
-----------------------------------
| name(UNIQUE) | age | gender |
-----------------------------------
| Jack | 18 | male |
-----------------------------------
| Jack | 19 | male |
-----------------------------------
# name不唯一，实际上第二条记录是插入不了的，强行插入只会抛出UNIQUE异常
# ERROR 1062 (23000): Duplicate entry 'Jack' for key 'name'
单列唯一
单列唯一是指该字段下的值不能重复。
以下示例将展示设置了单列唯一的字段如果出现插入值相同的情况将会抛出异常。
# 单列唯一设置语法
# 01：推荐语法：在字段后直接添加UNIQUE的约束
# 02：其他语法：UNIQUE KEY(字段名) COMMENT "描述信息"
CREATE TABLE userinfo (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "用户编号",
name CHAR(12) NOT NULL UNIQUE COMMENT "用户姓名"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
插入值，如果同一次插入相同的值将抛出异常，本次SQL作废：
M > INSERT INTO
userinfo(name)
VALUES
("Jack"),
("Jack");
ERROR 1062 (23000): Duplicate entry 'Jack' for key 'name'
联合唯一
联合唯一是指多个字段的值不能重复。
比如，世界范围内ip可以有多个重复的，port也可以有多个重复，而ip+port则是全世界唯一的，因此ip+port可以设置联合唯一。
# 联合唯一设置语法：
# 01：推荐语法：UNIQUE KEY 联合唯一的名字(字段1, 字段2) COMMENT "描述信息"
# 02：简单语法：UNIQUE(字段1，字段2) COMMENT "描述信息"
CREATE TABLE serverinfo (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
ip CHAR(19) NOT NULL COMMENT "ip地址",
port INT(5) NOT NULL COMMENT "port端口号",
UNIQUE KEY joint_unique_ip_port(ip, port) COMMENT "联合唯一(ip,port)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
以下示例将展示联合唯一的约束特性：
# 127.0.0.1 和 3306 不允许同时出现
M > INSERT INTO
serverinfo(ip, port)
VALUES
("127.0.0.1", 3306);
# 出现一个3306，没有问题
M > INSERT INTO
serverinfo(ip, port)
VALUES
("192.168.0.1", 3306);
# 出现一个127.0.0.1，没有问题
M > INSERT INTO
serverinfo(ip, port)
VALUES
("127.0.0.1", 3307);
# 同时出现，抛出1062异常
M > INSERT INTO
serverinfo(ip, port)
VALUES
("127.0.0.1", 3306);
ERROR 1062 (23000): Duplicate entry '127.0.0.1-3306' for key 'joint_unique_ip_port'
PRIMARY KEY
主键特性
主键是InnoDB表中必要的组织数据的依据，可以对查找速度进行质的提升，此外它还有非空且唯一的约束性限制。
一张表中至多只有一个主键(约束只能有一个，但可以作用到好几个字段)。
当没有明确指出PRIMAY KEY时，InnoDB存储引擎首先会查找是否具有非空且唯一约束条件的字段
如果有将则将其转变为主键
如果没有则会自动的创建一个6字节的隐藏主键用于组织数据
查看任意一张表的表结构，都能看到其主键信息：
M > DESC serverinfo;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip | char(19) | NO | MUL | NULL | |
| port | int(5) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
非空唯一
主键具有非空且唯一的约束，如果我们没有设置主键但是设置一个非空且唯一的字段时InnoDB存储引擎会自动将他转变为主键。
示例如下：
CREATE TABLE user (
id INT NOT NULL UNIQUE AUTO_INCREMENT COMMENT "用户编号",
name CHAR(12) NOT NULL COMMENT "用户姓名"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
查看这张表的表结构，可以看见id字段字段转变为主键了：
M > DESC user;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(12) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
单列主键
尽管可以用上面的方式设置主键，但这并不是一种常用的手段，我们有更加简便的方式来设置单列主键。
单列主键即为单独的一个字段设置主键。
设置单列主键的字段不应该再参与业务逻辑处理，它的功能只应该有两个：1.标识，2.加速查找
如身份证号这种可能会被引用于业务处理的字段最好不要设置为主键
示例如下：
# 单列主键设置语法
# 01：推荐语法：在字段后直接添加PRIMARY KEY的约束
# 02：其他语法：PRIMARY KEY(字段名) COMMENT "描述信息"
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "用户编号",
name CHAR(12) NOT NULL COMMENT "用户姓名"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
复合主键
复合主键即是将多个字段设置为主键（约束限制作用到多个字段上），与联合唯一效果类似。
示例如下：
# 复合主键设置语法：
# 01：推荐语法：PRIMARY KEY 复合主键的名字(字段1, 字段2) COMMENT "描述信息"
# 02：简单语法：PRIMARY KEY(字段1，字段2) COMMENT "描述信息"
CREATE TABLE admin (
name CHAR(12) NOT NULL COMMENT "管理员",
host CHAR(15) NOT NULL COMMENT "允许登录的地址",
PRIMARY KEY complex_primary_key(name, host) COMMENT "复合主键(name,host)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
AUTO_INCREMENT
自动增长
AUTO_INCREMENT约束为自动增长，被该约束条件所约束的字段必须同时被KEY所约束。
UNIQUER KEY | PRIMARY KEY | FOREIGN KEY
一般来说该约束都与单列PRIMARY KEY一起使用，让其进行自增长。
如下所示：
CREATE TABLE temp (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
data CHAR(15) NOT NULL COMMENT "任意数据"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
插入3条数据后查看自增长：
M > INSERT INTO
temp(data)
VALUES
("first"),
("second"),
("third");
M > SELECT * FROM temp;
+----+--------+
| id | data |
+----+--------+
| 1 | first |
| 2 | second |
| 3 | third |
+----+--------+
记录删除
当某一条记录进行删除后，插入时将继续依照其所记录的增长值进行增长。
示例如下：
M > DELETE FROM temp WHERE id=3;
M > SELECT * FROM temp;
+----+--------+
| id | data |
+----+--------+
| 1 | first |
| 2 | second |
+----+--------+
M > INSERT INTO
temp(data)
VALUES
("new");
M > SELECT * FROM temp;
+----+--------+
| id | data |
+----+--------+
| 1 | first |
| 2 | second |
| 4 | new |
+----+--------+
使用以下命令可查看到其目前记录的增长值：
M > SHOW CREATE TABLE temp\G;
*************************** 1. row ***************************
Table: temp
Create Table: CREATE TABLE `temp` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录编号',
`data` char(15) NOT NULL COMMENT '任意数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
偏移步长
默认的自增长（偏移量）是从1开始，而默认的步长为1即每次都加一个，我们也可以修改这个值。
命令与描述如下所示：
# 针对单表的默认自增长初始值设置，如未设置则为1
ALTER TABLE 表名 AUTO_INCREMENT = 设定起始值
# 本次会话有效，关闭终端后失效，针对所有表
SET SESSION AUTO_INCREMENT_OFFSET = 设定的起始值
SET SESSION AUTO_INCREMENT_INCREMENT = 设定的步长
# 全局设置，所有会话生效，重启服务失效
SET GLOBAL AUTO_INCREMENT_OFFSET = 设定的起始值
SET GLOBAL AUTO_INCREMENT_INCREMENT = 设定的步长
由于偏移步长的使用相对较少，故不再进行演示。
FOREIGN KEY
外键描述
FOREIGN KEY是用于将两个表链接在一起的键。
FOREIGN KEY是一个表中的一个字段或字段集合，它引用另一个表中的PRIMARY KEY。
使用外键进行表关系互联后，可以使用级联操作（外键仅作为约束条件，与查询关系不大）
表类型必须是InnoDB存储引擎
注意，在创建外键时，引用字段和外键字段都需要匹配：
- 引擎应该是相同的，例如InnoDB;
- 数据类型应该相同，并且长度相同;
- 编码应该相同，例如utf8mb4；
- 外键应引用引用表中PRIMARY KEY的字段;
- 如果一个有UNSIGNED，另一个必须也要有.
外键操作
如下，我们有这样的两张表：
可以在学生信息表中创建一个FOREIGN KEY字段FK班级，用于关联班级信息表。
外键关联有好有坏，好处是可以更加方便的进行多表之间的操作如级联操作等，坏处是表关系中耦合度增加，后期扩展不便。
创建表时可一并创建外键，创建方式如下：
# 创建表时外键的方式，别名可以省略。如果省略则自动生成
FOREIGN KEY 外键别名(本表外键字段) REFERENCES 主表名字(主键) ON DELETE 级联操作 ON UPDATE 级联操作
# 根据上图演示创建语法如下：
FOREIGN KEY 外键别名("FK班级") REFERENCES "班级信息表"("班级编号") ON DELETE CASCADE ON UPDATE CASCADE
在一个表创建后新增外键，语法格式如下：
# 可指定外键别名，也可以不指定
ALTER TABLE 从表名 ADD FOREIGN KEY 外键别名(本表外键字段) REFERENCES 主表名字(主键) ON DELETE 级联操作 ON UPDATE 级联操作;
查看外键别名：
SHOW CREATE TABLE 表名;
删除一个表的外键约束：
ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
一对一
一对一关系例举：一个学号只能对应一个学生，一个学生只能对应一个学号。
一对一关系用两张表即可进行表达：
创建表关系示例：
# 学号表
CREATE TABLE student_id_table(
id INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
start_year YEAR NOT NULL COMMENT "入学年份",
end_year YEAR NOT NULL COMMENT "毕业年份",
sanction ENUM("NO", "SMALL", "BIG") NOT NULL DEFAULT "NO" COMMENT "处分情况"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 学生表
CREATE TABLE student_message_table(
id INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT "学生编号",
name CHAR(12) NOT NULL COMMENT "学生姓名",
gender ENUM("MALE", "FEMALE") NOT NULL DEFAULT "MALE" COMMENT "学生性别",
age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "学生年龄",
-- 一对一关系必须为外键字段创建UNIQUE的约束才能是一对一，否则是一对多
fk_sid INT(4) UNIQUE COMMENT "外键字段" ,
-- 创建外键关系
FOREIGN KEY stu_msg_fk_stu_id(fk_sid) REFERENCES student_id_table(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
尝试插入数据：
INSERT INTO
student_id_table(start_year, end_year, sanction)
VALUES
("2018", "2022", "SMALL");
INSERT INTO
student_id_table(start_year, end_year)
VALUES
("2019", "2023"),
("2019", "2023");
INSERT INTO
student_message_table(name, gender, age, fk_sid)
VALUES
("云崖", "MALE", 18, 3),
("杰克", "MALE", 17, 2),
("贝拉", "FEMALE", 17, 1);
查询操作：
SELECT stut.name, sidt.sanction FROM student_id_table AS sidt
INNER JOIN student_message_table AS stut ON stut.fk_sid = sidt.id
LIMIT 5;
+--------+----------+
| name | sanction |
+--------+----------+
| 云崖 | NO |
| 杰克 | NO |
| 贝拉 | SMALL |
+--------+----------+
一对多&多对一
一对多&多对一关系例举：一个班级可以有多个学生，一个学生只能拥有一个班级。
一对多&多对一关系用两张表即可表达，且关系建立在多的一方：
创建表关系示例：
# 班级表
CREATE TABLE class_message_table(
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "班级编号",
name CHAR(12) NOT NULL UNIQUE COMMENT "班级名称",
teacher CHAR(12) NOT NULL COMMENT "班主任"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 学生表
CREATE TABLE student_message_table(
id INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT "学生编号",
name CHAR(12) NOT NULL COMMENT "学生姓名",
gender ENUM("MALE", "FEMALE") NOT NULL DEFAULT "MALE" COMMENT "学生性别",
age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "学生年龄",
-- 相比于一对一，外键字段不用UNIQUE约束即为一对多关系
fk_class INT(4) COMMENT "外键字段" ,
-- 创建外键关系
FOREIGN KEY stu_msg_fk_class_id(fk_class) REFERENCES class_message_table(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
尝试插入数据：
INSERT INTO
class_message_table(name, teacher)
VALUES
("三年级1班", "李老师"),
("二年级1班", "张老师"),
("二年级2班", "王老师");
INSERT INTO
student_message_table(name,gender,age,fk_class)
VALUES
("云崖","MALE",18,1),
("杰克","MALE",17,2),
("贝拉","FEMALE",17,2);
查询操作：
SELECT st.name, ct.name FROM student_message_table AS st
INNER JOIN class_message_table AS ct ON st.fk_class = ct.id
LIMIT 3;
--------+---------------+
| name | name |
+--------+---------------+
| 云崖 | 三年级1班 |
| 杰克 | 二年级1班 |
| 贝拉 | 二年级1班 |
+--------+---------------+
多对多
多对多关系例举：一个班级可以有多位教师，一位教师也可以在多个班级中任教。
多对多关系必须用三张表才可表达：
创建表关系示例，中间表中双向一对一即多对多：
# 老师表
CREATE TABLE teacher_message_table (
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "老师编号",
name CHAR(12) NOT NULL COMMENT "老师姓名",
age TINYINT(3) UNSIGNED NOT NULL COMMENT "老师年龄",
gender ENUM("MALE", "FEMALE") NOT NULL DEFAULT "MALE" COMMENT "老师性别"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 班级表
CREATE TABLE class_message_table(
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "班级编号",
name CHAR(12) NOT NULL COMMENT "班级名称"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 关系表
CREATE TABLE teacher_and_class_table(
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
tid INT(4) NOT NULL COMMENT "老师编号",
cid INT(4) NOT NULL COMMENT "班级编号",
-- 双向一对一
FOREIGN KEY mid_table_tid(tid) REFERENCES teacher_message_table(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY mid_table_cid(cid) REFERENCES class_message_table(id) ON DELETE CASCADE ON UPDATE CASCADE,
-- 设置联合唯一约束，完成一对一关系，否则就是一对多
UNIQUE KEY tid_cid(tid, cid)
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
尝试插入数据：
INSERT INTO
teacher_message_table(name, gender, age)
VALUES
("李老师", "MALE", 30),
("王老师", "FEMALE", 32),
("张老师", "MALE", 28);
INSERT INTO
class_message_table(name)
VALUES
("三年级1班"),
("二年级1班"),
("二年级2班");
INSERT INTO
teacher_and_class_table(tid, cid)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 3);
查询操作：
SELECT mid.id,class.name,teacher.name FROM class_message_table as class
INNER JOIN teacher_and_class_table as mid ON mid.cid = class.id
INNER JOIN teacher_message_table as teacher ON mid.tid = teacher.id
LIMIT 5;
+----+---------------+-----------+
| id | name | name |
+----+---------------+-----------+
| 1 | 三年级1班 | 李老师 |
| 2 | 二年级1班 | 李老师 |
| 3 | 二年级2班 | 李老师 |
| 4 | 三年级1班 | 王老师 |
| 5 | 二年级2班 | 张老师 |
+----+---------------+-----------+
自关联
自关联是特殊的一种关系，即自己关联自己。
一个文章下可以有很多评论，这些评论又分为根评论和子评论，评论发起者也可以自己回复自己，那么这个时候就可以使用自关联建立关系。
CREATE TABLE comment (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
user CHAR(12) NOT NULL COMMENT "评论者",
context CHAR(255) NOT NULL COMMENT "评论内容",
time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "评论时间",
fk_comment INT COMMENT "外键字段",
FOREIGN KEY self_comment(fk_comment) REFERENCES comment(id) ON DELETE CASCADE
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
尝试插入数据：
# 插入根评论
INSERT INTO
comment(user, context, time)
VALUES
("yunya", "这是一条根评论", now());
# 插入子评论
INSERT INTO
comment(user, context, time, fk_comment)
VALUES
("yunya", "这是一条子评论", now(), 1);
INSERT INTO
comment(user, context, time, fk_comment)
VALUES
("Jack", "这是一条子评论", now(), 1);
查询操作，找出yunya的根评论中其下所有的子评论：
SELECT
root_commint.user AS "楼主",
child_commint.user AS "评论者",
child_commint.context AS "评论内容",
child_commint.time AS "评论时间"
FROM
comment AS root_commint
INNER JOIN comment AS child_commint ON child_commint.fk_comment = root_commint.id
WHERE
root_commint.user = "yunya";
+--------+-----------+-----------------------+---------------------+
| 楼主 | 评论者 | 评论内容 | 评论时间 |
+--------+-----------+-----------------------+---------------------+
| yunya | yunya | 这是一条子评论 | 2021-02-17 21:20:10 |
| yunya | Jack | 这是一条子评论 | 2021-02-17 21:23:52 |
+--------+-----------+-----------------------+---------------------+
ON UPDATE
级联更新，不光是对FOREIGN KEY字段有用，对其他字段也同样适用，如DATETIME字段你的自动更新：
| 选项 | 说明 |
|---|---|
| ON UPDATE CASCADE | 更新父表记录时，比如更改父表的主键时，子表记录同时更新 |
| ON UPDATE SET NULL | 更新父表记录时，比如更改父表的主键时，子表记录设置为NULL |
| ON UPDATE NO ACTION | 更新父表记录时，子表不做任何处理，必须把子表处理完才可以更新主表 |
| ON UPDATE RESTRICT | 更新父表记录时，子表不做任何处理，必须把子表处理完才可以更新主表 |
使用方式参照外键操作。
ON DELETE
级联删除，常用处理方式如下表所示：
| 选项 | 说明 |
|---|---|
| ON DELETE CASCADE | 删除父表记录时，子表记录同时删除 |
| ON DELETE SET NULL | 删除父表记录时，子表记录设置为NULL（子表字段要允许NULL） |
| ON DELETE NO ACTION | 删除父表记录时，子表不做任何处理，必须把子表处理完才可以删除主表，否则会抛出异常 |
| ON DELETE RESTRICT | 删除父表记录时，子表不做任何处理，必须把子表处理完才可以删除主表，否则会抛出异常 |
使用方式参照外键操作。
