19MySQL约束

文档中心

文章目录

💒 约束
- 🚏 1、约束(constraint)概述
- - 🚀 1.1为什么需要约束
  - 🚄 1.2 什么是约束
  - 🚒 1.3 约束的分类
- 🚏 2、非空约束 not null 列级约束
- - 🚀 2.1 作用
  - 🚄 2.2 关键字
  - 🚒 2.3 特点
  - 🚤 2.4 添加非空约束
  - - 🚬在CREATE TABLE时添加约束
    - 🚬在ALTER TABLE时添加约束
  - 🚗 2.5 删除非空约束
  - - 🚬在ALTER TABLE时删除约束
- 🚏 3、唯一性约束 unqiue
- - 🚀3.1 作用
  - 🚄 3.2 关键字
  - 🚒 3.3 特点
  - 🚤 3.4 添加唯一约束
  - - 🚬在CREATE TABLE时添加约束
    - 🚬在ALTER TABLE时添加约束
  - 🚗 3.5 关于复合唯一约束
  - - 🚬关于复合唯一约束
    - 🚬关于复合唯一约束: 实际案例
    - - 🚭复合唯一约束没有指明约束名时默认约束名为一个字段 sid
  - 🚲3.6 删除唯一约束
  - - 🚬注意：可以通过 `show index from 表名称; `查看表的索引
- 🚏 4、PRIMARY KEY 约束
- - 🚀4.1 作用
  - 🚄 4.2 关键字
  - 🚒 4.3 特点
  - 🚤 4.4 添加主键约束
  - - 🚬在CREATE TABLE时添加约束
    - 🚬在ALTER TABLE时添加约束(建表后增加主键约束)
  - 🚗 4.5 关于复合主键
  - - 🚬复合主键：案例
  - 🚲 删除主键约束
- 🚏 5、自增列：AUTO_INCREMENT
- - 🚀5.1 作用
  - 🚄 5.2 关键字
  - 🚒 5.3 特点和要求
  - 🚤 5.4 如何指定自增约束
  - - 🚬在CREATE TABLE时添加
    - 🚬在ALTERTABLE时添加
  - 🚗 5.5 如何删除自增约束
  - 🚲 5.6 MySQL 8.0新特性—自增变量的持久化
- 🚏 6、FOREIGN KEY 约束
- - 🚀 6.1 作用
  - 🚄 6.2 关键字
  - 🚒 6.3 主表和从表/父表和子表
  - 🚤 6.4 特点
  - 🚗 6.5 添加外键约束
  - - 🚬在CREATE TABLE 时添加
    - 🚬在ALTER TABLE时添加外键约束
  - 🚀6.6 约束等级
  - - 🚬（1）演示1：on update cascade on delete set null
    - 🚬（2）演示2：on update set null on delete cascade
    - 🚬（3）演示：on update cascade on delete cascade
  - 🚄 6.7 删除外键约束
  - - 🚬删除外键约束
  - 🚒 6.8 开发场景
  - 🚤 6.9 阿里开发规范（结论）
  - - 🚬【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
- 🚏 7、CHECK 约束
- - 🚀 7.1 作用
  - 🚄 2、关键字
  - 🚒3、说明：MySQL 5.7 不支持
  - - 🚬check约束
- 🚏 8、DEFAULT约束
- - 🚀 8.1 作用
  - 🚄 8.2 关键字
  - 🚒 8.3 如何给字段加默认值
  - - 🚬在CREATE TABLE添加约束
    - 🚬在ALTERTABLE添加约束
  - 🚤 8.4 如何删除默认值约束
- 🚏 9、面试

😹 作者: gh-xiaohe
😻 gh-xiaohe的博客
😽 觉得博主文章写的不错的话，希望大家三连（✌关注，✌点赞，✌评论），多多支持一下！！

💒 约束

🚏 1、约束(constraint)概述

🚀 1.1为什么需要约束

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

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

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

🚄 1.2 什么是约束

对表中字段的限制。

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

🚒 1.3 约束的分类

角度1 根据约束数据列的限制：
- 单列约束：每个约束只约束一列
- 多列约束：每个约束可约束多列数据
角度2 根据约束的作用范围：
- 列级约束：只能作用在一个列上，跟在列的定义后面
- 表级约束：可以作用在多个列上，不与列一起，而是单独定义

位置支持的约束类型是否可以起约束名列级约束：列的后面语法都支持，但外键没有效果不可以表级约束：所有列的下面   默认和非空不支持，其他支持   可以（主键没有效果）

角度3 根据约束起的作用（或功能）：
- NOT NULL 非空约束，规定某个字段不能为空
- UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
- PRIMARY KEY 主键(非空且唯一)约束
- FOREIGN KEY 外键约束
- CHECK 检查约束
- DEFAULT 默认值约束
如何添加/删除约束？
- CREATE TABLE时添加约束
- ALTER TABLE 时增加约束、删除约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

如何查看表中的约束

# 格式#information_schema数据库名（系统库）#table_constraints表名称（专门存储各个表的约束）SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

SELECT * FROM information_schema.table_constraints WHERE table_name = 'employees';

在这里插入图片描述

🚏 2、非空约束 not null 列级约束

🚀 2.1 作用

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

在这里插入图片描述

🚄 2.2 关键字

NOT NULL

🚒 2.3 特点

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

🚤 2.4 添加非空约束

🚬在CREATE TABLE时添加约束

# 格式CREATE TABLE 表名称(字段名  数据类型,    字段名  数据类型 NOT NULL,      字段名  数据类型 NOT NULL);

CREATE TABLE test1(id INT NOT NULL,last_name VARCHAR(15) NOT NULL,email VARCHAR(25),salary DECIMAL(10,2) );DESC test1;INSERT INTO test1(id,last_name,email,salary)VALUES(1,'Tom','tom@126.com',3400);#错误：Column 'last_name' cannot be nullINSERT INTO test1(id,last_name,email,salary)VALUES(2,NULL,'tom1@126.com',3400);#错误：Column 'id' cannot be nullINSERT INTO test1(id,last_name,email,salary)VALUES(NULL,'Jerry','jerry@126.com',3400);# Field 'last_name' doesn't have a default value# 错误 last_name 没有默认值  why？ 为什么会报此错误# 当我们没有去给这个字段赋值的时候，一般是先来找一下 没有没有 默认值# 如果是有默认值得，就先 赋值 考虑默认值，没有默认值，就显示用 null # 此时还不可以 使用 null     报 没有默认值# 所以 报错 。。。。。INSERT INTO test1(id,email)VALUES(2,'abc@126.com');UPDATE test1SET last_name = NULLWHERE id = 1; # 修改不成功 有约束的限制

在这里插入图片描述

🚬在ALTER TABLE时添加约束

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

DESC test1;UPDATE test1SET email = NULLWHERE id = 1;# Invalid use of NULL value# 出错 刚才修改了 email 中 有个null的 存在  此时修改 不成功 如# 果就想给 email 添加 非空约束 就需要把 email 中存在的null值 给修改了# 表中有多条记录  如果此时有一个是null 就不能添加成功ALTER TABLE test1MODIFY email VARCHAR(25) NOT NULL;# 需要把 email 中 null 的字段修改了才能修改成功UPDATE test1SET email = 'abc@126.com'WHERE id = 1;ALTER TABLE test1MODIFY email VARCHAR(25) NOT NULL;DESC test1;

在这里插入图片描述

🚗 2.5 删除非空约束

🚬在ALTER TABLE时删除约束

# 格式alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null，相当于修改某个非注解字段，该字段允许为空或 alter table 表名称 modify 字段名 数据类型;#去掉not null，相当于修改某个非注解字段，该字段允许为空

DESC test1;ALTER TABLE test1MODIFY email VARCHAR(25) NULL;DESC test1;

在这里插入图片描述

🚏 3、唯一性约束 unqiue

🚀3.1 作用

用来限制某个字段/某列的值不能重复。

在这里插入图片描述

🚄 3.2 关键字

UNIQUE

🚒 3.3 特点

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

🚤 3.4 添加唯一约束

🚬在CREATE TABLE时添加约束

# 格式create table 表名称(字段名  数据类型,    字段名  数据类型  unique,      字段名  数据类型  unique key,    字段名  数据类型);create table 表名称(字段名  数据类型,    字段名  数据类型,      字段名  数据类型,    [constraint 约束名] unique key(字段名));

CREATE TABLE test2(id INT UNIQUE, #列级约束last_name VARCHAR(15) ,email VARCHAR(25),salary DECIMAL(10,2),#表级约束CONSTRAINT uk_test2_email UNIQUE(email));DESC test2;SELECT * FROM information_schema.table_constraints WHERE table_name = 'test2';#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。INSERT INTO test2(id,last_name,email,salary)VALUES(1,'Tom','tom@126.com',4500);#错误：Duplicate entry '1' for key 'test2.id'  id相同INSERT INTO test2(id,last_name,email,salary)VALUES(1,'Tom1','tom1@126.com',4600);#错误：Duplicate entry 'tom@126.com' for key 'test2.uk_test2_email'  email相同INSERT INTO test2(id,last_name,email,salary)VALUES(2,'Tom1','tom@126.com',4600);#可以向声明为unique的字段上添加null值。而且可以多次添加nullINSERT INTO test2(id,last_name,email,salary)VALUES(2,'Tom1',NULL,4600);INSERT INTO test2(id,last_name,email,salary)VALUES(3,'Tom2',NULL,4600);SELECT * FROM test2;

在这里插入图片描述

🚬在ALTER TABLE时添加约束

DESC test2;SELECT * FROM test2# Duplicate entry '4600.00' for key 'uk_test2_sal'ALTER TABLE test2ADD CONSTRAINT uk_test2_sal UNIQUE(salary);UPDATE test2SET salary = 5000WHERE id = 3;SELECT * FROM test2#方式1：ALTER TABLE test2ADD CONSTRAINT uk_test2_sal UNIQUE(salary); # add 后面的写法 和 表级 约束的写法一致#方式2：ALTER TABLE test2MODIFY last_name VARCHAR(15) UNIQUE; # modify 后面的写法 和 列级 约束的写法一致 类型 varchar(15) 不能丢DESC test2;

在这里插入图片描述

🚗 3.5 关于复合唯一约束

# 格式create table 表名称(字段名  数据类型,    字段名  数据类型,      字段名  数据类型,    unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的);

🚬关于复合唯一约束

CREATE TABLE USER(id INT,`name` VARCHAR(15),`password` VARCHAR(25),#表级约束CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`));INSERT INTO USERVALUES(1,'Tom','abc');#可以成功的：INSERT INTO USERVALUES(1,'Tom1','abc');SELECT *FROM USER;

在这里插入图片描述

🚬关于复合唯一约束: 实际案例

#学生表create table student(sid int,#学号    sname varchar(20),#姓名    tel char(11) unique key,  #电话    cardid char(18) unique key #身份证号);#课程表create table course(cid int,  #课程编号    cname varchar(20)     #课程名称);#选课表create table student_course(    id int,sid int,    cid int,    score int,    unique key(sid,cid)  #复合唯一);

insert into student values(1,'张三','13710011002','101223199012015623');#成功insert into student values(2,'李四','13710011003','101223199012015624');#成功insert into course values(1001,'Java'),(1002,'MySQL');#成功

在这里插入图片描述

insert into student_course values(1, 1, 1001, 89),(2, 1, 1002, 90),(3, 2, 1001, 88),(4, 2, 1002, 56);#成功

在这里插入图片描述

INSERT INTO student_course VALUES(5,2,1002,67);  # 失败#error：Duplicate entry '2-1002' for key 'student_course.sid'   违反sid-cid的复合唯一

🚭复合唯一约束没有指明约束名时默认约束名为一个字段 sid

在这里插入图片描述

🚲3.6 删除唯一约束

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

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

删除唯一性约束-- 添加唯一性约束的列上也会自动创建唯一索引。-- 删除唯一约束只能通过删除唯一索引的方式删除。-- 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。-- 如果创建唯一约束时未指定名称，-- 如果是单列，就默认和列名相同；-- 如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。SELECT * FROM information_schema.table_constraints WHERE table_name = 'student_course';SELECT * FROM information_schema.table_constraints WHERE table_name = 'test2';DESC test2;#如何删除唯一性索引ALTER TABLE test2DROP INDEX last_name;ALTER TABLE test2DROP INDEX uk_test2_sal;SELECT * FROM information_schema.table_constraints WHERE table_name = 'test2';DESC test2;

在这里插入图片描述

🚬注意：可以通过 `show index from 表名称;` 查看表的索引

SHOW INDEX FROM test2

在这里插入图片描述

🚏 4、PRIMARY KEY 约束

🚀4.1 作用

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

🚄 4.2 关键字

primary key

🚒 4.3 特点

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

在这里插入图片描述

一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的==主键索引（能够根据主键查询的，就根据主键查询，效率更高==）。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

🚤 4.4 添加主键约束

🚬在CREATE TABLE时添加约束

# 格式create table 表名称(字段名  数据类型  primary key, #列级模式    字段名  数据类型,      字段名  数据类型  );create table 表名称(字段名  数据类型,    字段名  数据类型,      字段名  数据类型,    [constraint 约束名] primary key(字段名) #表级模式);

#一个表中最多只能有一个主键约束。#错误：Multiple primary key definedCREATE TABLE test3(id INT PRIMARY KEY, #列级约束last_name VARCHAR(15) PRIMARY KEY,salary DECIMAL(10,2),email VARCHAR(25));# 主键约束特征：非空且唯一，用于唯一的标识表中的一条记录。CREATE TABLE test4(id INT PRIMARY KEY, #列级约束last_name VARCHAR(15),salary DECIMAL(10,2),email VARCHAR(25));#MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。CREATE TABLE test5(id INT , last_name VARCHAR(15),salary DECIMAL(10,2),email VARCHAR(25),#表级约束CONSTRAINT pk_test5_id PRIMARY KEY(id)  #没有必要起名字。);SELECT * FROM information_schema.table_constraints WHERE table_name = 'test5';INSERT INTO test4(id,last_name,salary,email)VALUES(1,'Tom',4500,'tom@126.com');#错误：Duplicate entry '1' for key 'test4.PRIMARY'INSERT INTO test4(id,last_name,salary,email)VALUES(1,'Tom',4500,'tom@126.com');#错误：Column 'id' cannot be nullINSERT INTO test4(id,last_name,salary,email)VALUES(NULL,'Tom',4500,'tom@126.com');

在这里插入图片描述

🚬在ALTER TABLE时添加约束(建表后增加主键约束)

# 格式ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

CREATE TABLE test6(id INT ,last_name VARCHAR(15),salary DECIMAL(10,2),email VARCHAR(25));DESC test6;ALTER TABLE test6ADD PRIMARY KEY (id);DESC test6;

在这里插入图片描述

🚗 4.5 关于复合主键

# 格式create table 表名称(字段名  数据类型,    字段名  数据类型,      字段名  数据类型,    primary key(字段名1,字段名2)  #表示字段1和字段2的组合是唯一的，也可以有更多个字段);

🚬复合主键：案例

CREATE TABLE user1(id INT,NAME VARCHAR(15),PASSWORD VARCHAR(25),PRIMARY KEY (NAME,PASSWORD) # 表级约束);#如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。INSERT INTO user1VALUES(1,'Tom','abc');INSERT INTO user1VALUES(1,'Tom1','abc');# 错误：Column 'name' cannot be null# 复合主键：其中一个为 null 就会 报错INSERT INTO user1VALUES(1,NULL,'abc');

在这里插入图片描述

🚲 删除主键约束

# 格式alter table 表名称 drop primary key;

# 如何删除主键约束 (在实际开发中，不会去删除表中的主键约束！)DESC test6;ALTER TABLE test6DROP PRIMARY KEY;DESC test6;

在这里插入图片描述

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

🚏 5、自增列：AUTO_INCREMENT

🚀5.1 作用

某个字段的值自增

🚄 5.2 关键字

auto_increment

🚒 5.3 特点和要求

（1）一个表最多只能有一个自增长列

（2）当需要产生唯一标识符或顺序值时，可设置自增长

（3）自增长列约束的列必须是键列（主键列，唯一键列）

（4）自增约束的列的数据类型必须是整数类型

（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

错误演示：

create table employee(eid int auto_increment,    ename varchar(20));# 只能有一个自增列，并且必须将其定义其为主键  # ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

create table employee(eid int primary key,    ename varchar(20) unique key auto_increment);# ERROR 1063 (42000): Incorrect column specifier for column 'ename'  因为ename不是整数类型

🚤 5.4 如何指定自增约束

🚬在CREATE TABLE时添加

# 格式create table 表名称(字段名  数据类型  primary key auto_increment,    字段名  数据类型  unique key not null,      字段名  数据类型  unique key,    字段名  数据类型  not null default 默认值, );create table 表名称(字段名  数据类型 default 默认值 ,    字段名  数据类型 unique key auto_increment,      字段名  数据类型 not null default 默认值,,    primary key(字段名));

CREATE TABLE test7(id INT PRIMARY KEY AUTO_INCREMENT,last_name VARCHAR(15) );#开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们在添加数据时，就不要给主键#对应的字段去赋值了。INSERT INTO test7(last_name)VALUES('Tom');SELECT * FROM test7;#当我们向主键（含AUTO_INCREMENT）的字段上添加0 或 null时，实际上会自动的往上添加指定的字段的数值INSERT INTO test7(id,last_name)VALUES(0,'Tom');INSERT INTO test7(id,last_name)VALUES(NULL,'Tom');SELECT * FROM test7;

在这里插入图片描述

# 特殊情况 一般不会出现SELECT * FROM test7;INSERT INTO test7(id,last_name)VALUES(10,'Tom');INSERT INTO test7(id,last_name)VALUES(-10,'Tom');SELECT * FROM test7;# 此时再加入数据 id 是从 何数 开始的INSERT INTO test7(id,last_name)VALUES(0,'Tom');SELECT * FROM test7;

在这里插入图片描述

🚬在ALTERTABLE时添加

# 格式alter table 表名称 modify 字段名 数据类型 auto_increment;

CREATE TABLE test8(id INT PRIMARY KEY ,last_name VARCHAR(15) );DESC test8;ALTER TABLE test8MODIFY id INT AUTO_INCREMENT;DESC test8;

在这里插入图片描述

🚗 5.5 如何删除自增约束

#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

DESC test8;ALTER TABLE test8MODIFY id INT ;DESC test8;

在这里插入图片描述

🚲 5.6 MySQL 8.0新特性—自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。下面通过案例来对比不同的版本中自增变量是否持久化。

在MySQL 5.7版本中

#在MySQL 5.7中演示#创建的数据表中包含自增主键的id字段CREATE TABLE test10(id INT PRIMARY KEY AUTO_INCREMENT);# 插入4个空值INSERT INTO test10VALUES(0),(0),(0),(0);# 查询数据表test10中的数据SELECT * FROM test10;# 删除 id = 4 的数据DELETE FROM test10WHERE id = 4;INSERT INTO test10VALUES(0);# 再次查询 SELECT * FROM test10;# 删除id = 5DELETE FROM test10WHERE id = 5;# 再次查询 SELECT * FROM test10;#重启服务器# 再次插入数据 看看id 插入的是什么数值INSERT INTO test10VALUES(0); # 插入的数据是 4SELECT * FROM test10;

在这里插入图片描述

从结果可以看出，新插入的0值分配的是4，按照重启前的操作逻辑，此处应该分配6。出现上述结果的主要原因是自增主键没有持久化。在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

在MySQL 8.0版本中：

CREATE TABLE test10(id INT PRIMARY KEY AUTO_INCREMENT);# 插入4个空值INSERT INTO test10VALUES(0),(0),(0),(0);# 查询数据表test10中的数据SELECT * FROM test10;# 删除 id = 4 的数据DELETE FROM test10WHERE id = 4;INSERT INTO test10VALUES(0);# 再次查询 SELECT * FROM test10;# 删除id = 5DELETE FROM test10WHERE id = 5;# 再次查询 SELECT * FROM test10;#重启服务器# 再次插入数据 看看id 插入的是什么数值INSERT INTO test10VALUES(0); # 插入的数据是 6SELECT * FROM test10;

在这里插入图片描述

从结果可以看出，自增变量已经持久化了。

MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

🚏 6、FOREIGN KEY 约束

🚀 6.1 作用

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

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

在这里插入图片描述

🚄 6.2 关键字

FOREIGN KEY

🚒 6.3 主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表

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

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

例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

🚤 6.4 特点

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

为什么？因为被依赖/被参考的值必须是唯一的

（2）在创建外键约束时，如果不给外键约束命名，==默认名不是列名，而是自动产生一个外键名==也可以指定外键约束名。

（3）==创建(CREATE)==表时就指定外键约束的话，先创建主表，再创建从表

（4）删表时，先删从表（或先删除外键约束），再删除主表

（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。

（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）

（9）删除外键约束后，必须手动删除对应的索引

（10）一个表可以声明多个外键约束

🚗 6.5 添加外键约束

🚬在CREATE TABLE 时添加

# 格式create table 主表名称(字段1  数据类型  primary key,    字段2  数据类型);create table 从表名称(字段1  数据类型  primary key,    字段2  数据类型,    [CONSTRAINT ] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段));#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样-- FOREIGN KEY: 在表级指定子表中的列-- REFERENCES: 标示在父表中的列

# ①先创建主表CREATE TABLE dept1(dept_id INT,dept_name VARCHAR(15));# ②再创建从表CREATE TABLE emp1(emp_id INT PRIMARY KEY AUTO_INCREMENT,emp_name VARCHAR(15),department_id INT,#表级约束CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id));# Failed to add the foreign key constraint. Missing index for constraint 'fk_empl_dept_id' in the referenced table 'deptl'#上述操作报错，因为主表中的dept_id上没有主键约束或唯一性约束。# ③ 添加ALTER TABLE dept1ADD PRIMARY KEY (dept_id);DESC dept1;#④ 再创建从表CREATE TABLE emp1(emp_id INT PRIMARY KEY AUTO_INCREMENT,emp_name VARCHAR(15),department_id INT,#表级约束CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id));DESC emp1;SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp1';

在这里插入图片描述

#添加失败# 我们不能够添加 或者 修改一个 child row  从表 中的一个行，因为外键约束INSERT INTO emp1VALUES(1001,'Tom',10);INSERT INTO dept1VALUES(10,'IT');#在主表dept1中添加了10号部门以后，我们就可以在从表中添加10号部门的员工INSERT INTO emp1VALUES(1001,'Tom',10);SELECT * FROM dept1;SELECT * FROM emp1;#删除失败# 我们不能够添加 或者 修改一个 parent row  主表 中的一个行，因为外键约束DELETE FROM dept1WHERE dept_id = 10;#更新失败UPDATE dept1SET dept_id = 20WHERE dept_id = 10;

在这里插入图片描述

🚬在ALTER TABLE时添加外键约束

一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

# 格式ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

CREATE TABLE dept2(dept_id INT PRIMARY KEY,dept_name VARCHAR(15));CREATE TABLE emp2(emp_id INT PRIMARY KEY AUTO_INCREMENT,emp_name VARCHAR(15),department_id INT);ALTER TABLE emp2ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp2';

在这里插入图片描述

总结：约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

🚀6.6 约束等级

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

如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT（更新同步更新删除从表有记录不可以删除）的方式。

🚬（1）演示1：on update cascade on delete set null

create table dept(did int primary key,#部门编号    dname varchar(50)#部门名称);create table emp(eid int primary key,  #员工编号    ename varchar(5),     #员工姓名    deptid int,#员工所在的部门    foreign key (deptid) references dept(did)  on update cascade on delete set null    #把修改操作设置为级联修改等级，把删除操作设置为set null等级);

insert into dept values(1001,'教学部');insert into dept values(1002, '财务部');insert into dept values(1003, '咨询部');insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门insert into emp values(2,'李四',1001);insert into emp values(3,'王五',1002);

mysql> select * from dept;mysql> select * from emp;

#修改主表成功，从表也跟着修改，修改了主表被引用的字段1002为1004，从表的引用字段就跟着修改为1004了mysql> update dept set did = 1004 where did = 1002;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from dept;+------+--------+| did  | dname  |+------+--------+| 1001 | 教学部 || 1003 | 咨询部 || 1004 | 财务部 | #原来是1002，修改为1004+------+--------+3 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |+-----+-------+--------+|   1 | 张三  |   1001 ||   2 | 李四  |   1001 ||   3 | 王五  |   1004 | #原来是1002，跟着修改为1004+-----+-------+--------+3 rows in set (0.00 sec)

#删除主表的记录成功，从表对应的字段的值被修改为nullmysql> delete from dept where did = 1001;Query OK, 1 row affected (0.01 sec)mysql> select * from dept;+------+--------+| did  | dname  | #记录1001部门被删除了+------+--------+| 1003 | 咨询部  || 1004 | 财务部  |+------+--------+2 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |+-----+-------+--------+|   1 | 张三  |   NULL | #原来引用1001部门的员工，deptid字段变为null|   2 | 李四  |   NULL ||   3 | 王五  |   1004 |+-----+-------+--------+3 rows in set (0.00 sec)

🚬（2）演示2：on update set null on delete cascade

create table dept(did int primary key,#部门编号    dname varchar(50)#部门名称);create table emp(eid int primary key,  #员工编号    ename varchar(5),     #员工姓名    deptid int,#员工所在的部门    foreign key (deptid) references dept(did)  on update set null on delete cascade    #把修改操作设置为set null等级，把删除操作设置为级联删除等级);

insert into dept values(1001,'教学部');insert into dept values(1002, '财务部');insert into dept values(1003, '咨询部');insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门insert into emp values(2,'李四',1001);insert into emp values(3,'王五',1002);

mysql> select * from dept;+------+--------+| did  | dname  |+------+--------+| 1001 | 教学部 || 1002 | 财务部 || 1003 | 咨询部 |+------+--------+3 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |+-----+-------+--------+|   1 | 张三  |   1001 ||   2 | 李四  |   1001 ||   3 | 王五  |   1002 |+-----+-------+--------+3 rows in set (0.00 sec)

#修改主表，从表对应的字段设置为nullmysql> update dept set did = 1004 where did = 1002;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from dept;+------+--------+| did  | dname  |+------+--------+| 1001 | 教学部 || 1003 | 咨询部 || 1004 | 财务部 | #原来did是1002+------+--------+3 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |+-----+-------+--------+|   1 | 张三  |   1001 ||   2 | 李四  |   1001 ||   3 | 王五  |   NULL | #原来deptid是1002，因为部门表1002被修改了，1002没有对应的了，就设置为null+-----+-------+--------+3 rows in set (0.00 sec)

#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了mysql> delete from dept where did=1001;Query OK, 1 row affected (0.00 sec)mysql> select * from dept;+------+--------+| did  | dname  | #部门表中1001部门被删除+------+--------+| 1003 | 咨询部 || 1004 | 财务部 |+------+--------+2 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |#原来1001部门的员工也被删除了+-----+-------+--------+|   3 | 王五  |   NULL |+-----+-------+--------+1 row in set (0.00 sec)

🚬（3）演示：on update cascade on delete cascade

create table dept(did int primary key,#部门编号    dname varchar(50)#部门名称);create table emp(eid int primary key,  #员工编号    ename varchar(5),     #员工姓名    deptid int,#员工所在的部门    foreign key (deptid) references dept(did)  on update cascade on delete cascade    #把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级);

insert into dept values(1001,'教学部');insert into dept values(1002, '财务部');insert into dept values(1003, '咨询部');insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门insert into emp values(2,'李四',1001);insert into emp values(3,'王五',1002);

mysql> select * from dept;+------+--------+| did  | dname  |+------+--------+| 1001 | 教学部 || 1002 | 财务部 || 1003 | 咨询部 |+------+--------+3 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |+-----+-------+--------+|   1 | 张三  |   1001 ||   2 | 李四  |   1001 ||   3 | 王五  |   1002 |+-----+-------+--------+3 rows in set (0.00 sec)

#修改主表，从表对应的字段自动修改mysql> update dept set did = 1004 where did = 1002;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from dept;+------+--------+| did  | dname  |+------+--------+| 1001 | 教学部 || 1003 | 咨询部 || 1004 | 财务部 | #部门1002修改为1004+------+--------+3 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |+-----+-------+--------+|   1 | 张三  |   1001 ||   2 | 李四  |   1001 ||   3 | 王五  |   1004 | #级联修改+-----+-------+--------+3 rows in set (0.00 sec)

#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了mysql> delete from dept where did=1001;Query OK, 1 row affected (0.00 sec)mysql> select * from dept;+------+--------+| did  | dname  | #1001部门被删除了+------+--------+| 1003 | 咨询部 || 1004 | 财务部 | +------+--------+2 rows in set (0.00 sec)mysql> select * from emp;+-----+-------+--------+| eid | ename | deptid |  #1001部门的员工也被删除了+-----+-------+--------+|   3 | 王五  |   1004 |+-----+-------+--------+1 row in set (0.00 sec)

🚄 6.7 删除外键约束

🚬删除外键约束

(1)第一步先查看约束名和删除外键约束SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;（2）第二步查看索引名和删除索引。（注意，只能手动删除）SHOW INDEX FROM 表名称; #查看某个表的索引名ALTER TABLE 从表名 DROP INDEX 索引名;

#一个表中可以声明有多个外键约束USE atguigudb;SELECT * FROM information_schema.table_constraints WHERE table_name = 'employees';USE dbtest13;SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp1';#删除外键约束ALTER TABLE emp1DROP FOREIGN KEY fk_emp1_dept_id;SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp1';#再手动的删除外键约束对应的普通索引SHOW INDEX FROM emp1;ALTER TABLE emp1DROP INDEX fk_emp1_dept_id;SHOW INDEX FROM emp1;

在这里插入图片描述

🚒 6.8 开发场景

问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？

答：不是的

问题2：建和不建外键约束有什么区别？

答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。

问题3：那么建和不建外键约束和查询有没有关系？

答：没有

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

🚤 6.9 阿里开发规范（结论）

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

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

🚏 7、CHECK 约束

🚀 7.1 作用

检查某个字段的值是否符号xx要求，一般指的是值的范围

🚄 2、关键字

UNIQUE

🚒3、说明：MySQL 5.7 不支持

MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告

但是**MySQL 8.0中可以使用check约束了**。

🚬check约束

CREATE TABLE test10(id INT,last_name VARCHAR(15),salary DECIMAL(10,2) CHECK(salary > 2000));INSERT INTO test10VALUES(1,'Tom',2500);SELECT * FROM test10# 添加失败# Check constraint 'test10_chk_1' is violated.INSERT INTO test10VALUES(2,'Tom1',1500);SELECT * FROM test10;

在这里插入图片描述

🚏 8、DEFAULT约束

🚀 8.1 作用

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

🚄 8.2 关键字

DEFAULT

🚒 8.3 如何给字段加默认值

🚬在CREATE TABLE添加约束

# 格式create table 表名称(字段名  数据类型  primary key,    字段名  数据类型  unique key not null,      字段名  数据类型  unique key,    字段名  数据类型  not null default 默认值, );create table 表名称(字段名  数据类型 default 默认值 ,    字段名  数据类型 not null default 默认值,      字段名  数据类型 not null default 默认值,    primary key(字段名),    unique key(字段名));说明：默认值约束一般不在唯一键和主键列上加

CREATE TABLE test11(id INT,last_name VARCHAR(15),salary DECIMAL(10,2) DEFAULT 2000);DESC test11;INSERT INTO test11(id,last_name,salary)VALUES(1,'Tom',3000);INSERT INTO test11(id,last_name)VALUES(2,'Tom1');SELECT * FROM test11;

在这里插入图片描述

🚬在ALTERTABLE添加约束

# 格式alter table 表名称 modify 字段名 数据类型 default 默认值;#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

CREATE TABLE test12(id INT,last_name VARCHAR(15),salary DECIMAL(10,2));DESC test12;ALTER TABLE test12MODIFY salary DECIMAL(8,2) DEFAULT 2500;

在这里插入图片描述

🚤 8.4 如何删除默认值约束

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

alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除alter table employee modify tel char(11)  not null;#删除tel字段默认值约束，保留非空约束

DESC test12;ALTER TABLE test12MODIFY salary DECIMAL(8,2);DESC test12;

在这里插入图片描述

🚏 9、面试

面试1、为什么建表时，加 not null default ‘’ 或 default 0

答：不想让表中出现null值。

面试2、为什么不想要 null 的值

答:
（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。

（2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？
在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

面试4、并不是每个表都可以任意选择存储引擎？
外键约束（FOREIGN KEY）不能跨引擎使用。

MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

19MySQL约束

文章目录

💒 约束

🚏 1、约束(constraint)概述

🚀 1.1为什么需要约束

🚄 1.2 什么是约束

🚒 1.3 约束的分类

🚏 2、非空约束 not null 列级约束

🚀 2.1 作用

🚄 2.2 关键字

🚒 2.3 特点

🚤 2.4 添加非空约束

🚬在CREATE TABLE时添加约束

🚬在ALTER TABLE时添加约束

🚗 2.5 删除非空约束

🚬在ALTER TABLE时删除约束

🚏 3、唯一性约束 unqiue

🚀3.1 作用

🚄 3.2 关键字

🚒 3.3 特点

🚤 3.4 添加唯一约束

🚬在CREATE TABLE时添加约束

🚬在ALTER TABLE时添加约束

🚗 3.5 关于复合唯一约束

🚬关于复合唯一约束

🚬关于复合唯一约束: 实际案例

🚭复合唯一约束 没有指明 约束名时 默认约束名 为 一个字段 sid

🚲3.6 删除唯一约束

🚬注意：可以通过 show index from 表名称; 查看表的索引

🚏 4、PRIMARY KEY 约束

🚀4.1 作用

🚄 4.2 关键字

🚒 4.3 特点

🚤 4.4 添加主键约束

🚬在CREATE TABLE时添加约束

🚬在ALTER TABLE时添加约束(建表后增加主键约束)

🚗 4.5 关于复合主键

🚬复合主键：案例

🚲 删除主键约束

🚏 5、自增列：AUTO_INCREMENT

🚀5.1 作用

🚄 5.2 关键字

🚒 5.3 特点和要求

🚤 5.4 如何指定自增约束

🚬在CREATE TABLE时添加

🚬在ALTERTABLE时添加

🚗 5.5 如何删除自增约束

🚲 5.6 MySQL 8.0新特性—自增变量的持久化

🚏 6、FOREIGN KEY 约束

🚀 6.1 作用

🚄 6.2 关键字

🚒 6.3 主表和从表/父表和子表

🚤 6.4 特点

🚗 6.5 添加外键约束

🚬在CREATE TABLE 时添加

🚬在ALTER TABLE时添加外键约束

🚀6.6 约束等级

🚬（1）演示1：on update cascade on delete set null

🚬（2）演示2：on update set null on delete cascade

🚬（3）演示：on update cascade on delete cascade

🚄 6.7 删除外键约束

🚬删除外键约束

🚒 6.8 开发场景

🚤 6.9 阿里开发规范（结论）

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

🚏 7、CHECK 约束

🚀 7.1 作用

🚄 2、关键字

🚒3、说明：MySQL 5.7 不支持

🚬check约束

🚏 8、DEFAULT约束

🚀 8.1 作用

🚄 8.2 关键字

🚒 8.3 如何给字段加默认值

🚬在CREATE TABLE添加约束

🚬在ALTERTABLE添加约束

🚤 8.4 如何删除默认值约束

🚏 9、面试

相关问题

公告

🚭复合唯一约束没有指明约束名时默认约束名为一个字段 sid

🚬注意：可以通过 `show index from 表名称;` 查看表的索引

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