MySQL约束

约束

概述：约束可以保证 "数据完整性"，"数据的精确性" 和 "可靠性"。它会了防止数据库中会存在不符合语义规定的数据以及因错误输入从而导致数据错乱而提出的。

在 SQL规范中 "约束" 是对表中数据进行额外的条件限制。

实体完整性：同一个表中，不能存在两条完全相同无法区分的记录。
域完整性：例如：年龄范围 0 ~ 120，性别范围 "男/女"。
引用完整性：员工表引用的部门，在部门表中必须存在。
用户自定义完整性：用户名唯一，密码不能为空等.....。

什么是约束？是对表中数据的强制规定（就是对表中的列设定的限制）

1. 约束的分类

约束的列个数：

单列约束。
多列约束。

约束的作用范围：

列级约束：将此约束声明在列的后面，作用于某一个列。
表级约束：在表中所有列声明都后再声明的该约束，作用于整张表。

约束的作用-功能：

not null （非空约束）
unique （唯一性约束）
primary key （主键约束）
foreign key （外键约束）
check （检查约束）
default （默认值约束）

如何操作约束：

在创建表时（create table）规定约束。
在修改表时（alter table）规定约束。

查看表中的约束

# 查看表信息
desc 表名;

# 数据字典查询-约束表
SELECT
	*
FROM
	information_schema.TABLE_CONSTRAINTS
WHERE
	TABLE_NAME = '表名'

# 数据字典查询-列表
SELECT
	*
FROM
	information_schema.COLUMNS
WHERE
	TABLE_NAME = '表名'

2. 非空约束

该约束属于：单列，列级约束。

作用：限定列的值不允许为空。

关键字：NOT NULL

特点：

默认，所有类型的值都不可以是 null，包含数字类型。

"非空约束" 只能出现在表对象的列上，只能为某个列单独限定非空，不能组合非空。

一个表可以有很多列限定非空，但都要分别限定。

注意：空字符串不等于NULL。

为列添加非空约束：

CREATE TABLE
IF NOT EXISTS supconit_not_null (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_name VARCHAR (20) NOT NULL COMMENT '名字'
)
# 修改时添加 非空约束
ALTER TABLE supconit_not_null MODIFY c_name VARCHAR(15) NOT NULL COMMENT '名字';

删除列的非空约束：

# ?
# 提示：这是一个 DDL 语句。
ALTER TABLE supconit_not_null MODIFY c_name VARCHAR (20) NOT NULL COMMENT '名字';

3. 唯一性约束

该约束属于：单列，多列，表级约束。

作用：限定列的值不能重复。

关键字：UNIQUE

特点：

同一个表中可以有多个唯一约束。

唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。

唯一性约束允许列值为空，但只能有一个 null 值。

在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

MySQL 会给 "唯一约束" 的列，默认创建一个 "唯一索引"（MySQL 给你做的优化）。

添加唯一约束：

CREATE TABLE
IF NOT EXISTS supconit_unique (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_code VARCHAR (30) UNIQUE COMMENT '唯一编码',
	c_name VARCHAR(15) NOT NULL COMMENT '名字'
)

CREATE TABLE
IF NOT EXISTS supconit_unique_01 (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_code VARCHAR (30) '唯一编码',
	c_name VARCHAR (15) NOT NULL COMMENT '名字',
    # 多列组合值唯一
	CONSTRAINT uniqun_column UNIQUE(c_code,c_name)
)
# 修改表时添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE (列,列 ,...);

删除唯一约束（删除唯一索引）：

由于在添加唯一约束时，会自动创建唯一索引，所以删除唯一约束只能通过删除 "唯一索引" 的方式删除。

说明：删除时需要指定索引名，"索引名" 和 "约束名" 相同。如果创建唯一约束时未指定名称，单列默认和列名相同，组合列默认为第一列列名。

ALTER TABLE 表名 DROP INDEX 索引名称;
#
ALTER TABLE supconit_unique DROP INDEX c_code;

扩展 - 查看表的索引：
SHOW INDEX FROM 表名;
#
SHOW INDEX FROM supconit_unique_01;

4. 主键约束

该约束属于：单列，多列，表级约束。

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

关键字：PRIMARY KEY

特点：

主键约束是唯一约束 + 非空约束（主键约束不允许重复，也不允许出现空值）。

一个表最多只能有一个主键约束。

主键约束可以对应着表中的一列或者多列（复合主键），那么这些列都不允许为空值，并且组合的值不允许重复。

MySQL 的主键约束名总是 PRIMARY，自定义的名字无效。

当创建主键约束时，系统默认会所在的列或列组合上建立对应的 "主键索引"（能够根据主键查询的，就根据主键查询，效率是最高的）。

主键列的值是不允许修改的。因为主键是数据记录的唯一标识，如果修改了，就有可能破坏 "数据的完整性"。

添加主键约束：

CREATE TABLE
IF NOT EXISTS supconit_primary (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_code VARCHAR (30) COMMENT '编码',
	c_name VARCHAR (15) COMMENT '名字'
)

CREATE TABLE
IF NOT EXISTS supconit_primary_01 (
	id INT UNSIGNED auto_increment COMMENT '主键-1',
	id_ext INT UNSIGNED COMMENT '主键-2',
	c_code VARCHAR (30) COMMENT '编码',
	c_name VARCHAR (15) COMMENT '名字',
	PRIMARY KEY (id, id_ext)
)
# 修改表时添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列,列 ,...);

删除主键约束（这是不允许的）：

ALTER TABLE 表名 DROP PRIMARY KEY;
#
ALTER TABLE supconit_primary_01 DROP PRIMARY KEY;
# 错误：
# [Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
# [Err] 1075 - 表定义不正确；只能有一个自动列，并且必须将其定义为键（主键）

注意：增长列必须为主键，并且在表中只能有一个。删除主键约束之前，要先删除增长约束。

5. 自增列

该约束属于：单列，表级约束。

作用：列的值（按照预设的数）自增长。

关键字：AUTO_INCREMENT

特点：

一个表最多只能有一个自增长列，并且该列必须是主键列。

自增约束的列的数据类型必须是整数类型。

如果自增列指定了 0 和 null，会在当前最大值的基础上自增。如果自增列手动指定了具体的值，则使用指定的值（注意：不能重复）。

添加自增列：

CREATE TABLE
IF NOT EXISTS 表名 (
	id INT PRIMARY KEY auto_increment COMMENT '主键', # 必须在主键约束和唯一约束配合使用
	NAME VARCHAR (10),
	email VARCHAR (25)
);
# 修改表时设置自增列（该列必须主键）
ALTER TABLE 表名 MODIFY id INT auto_increment COMMENT '注释';

删除自增长：

ALTER TABLE 表名 MODIFY id INT;

设置表自增列的初始值：

ALTER TABLE 表名 auto_increment = 0;

MySQL 8.0 新特性 - 自增变量的持久化

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

例如：使用了：1，2，4，5，6 ，此时下一条应该使用的是 7。然后删除了 5，6，此时重启 MySQL 服务器之后，下一条使用的是 5。

说明：自增值是有 InnoDB 的数据字典内部一个计数器决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化为：auto_increment = max(primary key)+1。

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

例如：使用了：1，2，4，5，6 ，此时下一条应该使用的是 7。然后删除了 5，6，此时重启 MySQL 服务器之后，下一条使用的是依然是 7。

6. 检查约束

该约束属于：单列，表级约束。

作用：检查一个列或多个列的值，是否符合预先设定要求（一般指的是值的范围）。

关键字：CHECK

说明：MySQL 8.0 之前不支持检查约束，可以定义，但是不起作用😊

特点：

在创建检查约束时，如果不给检查约束命名，默认自动产生一个约束名，也可以自定义约束名。

一张表中可以有多个检查约束。

在设置 check 条件时可以使用 and，or 等符号。

添加检查约束：

CREATE TABLE
IF NOT EXISTS supconit_check (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	age TINYINT UNSIGNED CHECK (age < 100) COMMENT '年龄必须小于100'
)

CREATE TABLE
IF NOT EXISTS supconit_check_01 (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	age TINYINT UNSIGNED COMMENT '年龄',
	CONSTRAINT age_check CHECK (age > 0 AND age < 100) # 年龄必须大于0并且小于100
)
# 修改表时添加检查约束
ALTER TABLE supconit_check_01 ADD CONSTRAINT age_check CHECK (age > 0 AND age < 100);

删除检查约束：

ALTER TABLE 表名 DROP CHECK 约束名称
#
ALTER TABLE supconit_check_01 DROP CHECK age_check;

扩展 - 使用函数

CREATE TABLE
IF NOT EXISTS supconit_check_02 (
id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
  age TINYINT UNSIGNED CHECK(age > 0 AND age < 100) COMMENT '年龄',
c_name VARCHAR(20) CHECK(LENGTH(c_name)>0 AND LENGTH(c_name) <15) COMMENT '名字'
)
# CHECK(LENGTH(c_name)>0 AND LENGTH(c_name) <15)

扩展 - 多列联合检查

CREATE TABLE
IF NOT EXISTS supconit_check_03 (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	age TINYINT UNSIGNED COMMENT '年龄',
	c_name VARCHAR (20) COMMENT '名字',
	CONSTRAINT table_check CHECK (LENGTH(c_name) > 0 AND age < 100)
)
# CONSTRAINT table_check CHECK (LENGTH(c_name) > 0 AND age < 100)

7. 默认约束

该约束属于：单列，列级约束。

作用：给列指定默认值。

关键字：DEFAULT

添加默认约束

CREATE TABLE
IF NOT EXISTS supconit_default (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	age TINYINT UNSIGNED DEFAULT 0 CHECK (age < 100) COMMENT '年龄'
)

ALTER TABLE supconit_default MODIFY COLUMN age TINYINT UNSIGNED DEFAULT 10 CHECK (age > 10 AND age < 100) COMMENT '年龄';

友情提示：列添加了默认值约束，该列就无需再设置非空约束了。

删除默认约束

# ?
# 提示：这是一个 DDL 语句。

8. 外键约束

该约束属于：单列，表级约束。

作用：限定某个表的谋个列的 "引用完整性"。

关键字：POREIGN KEY

主表和从表（父表和子表）

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表（外键在哪里就从表）

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

特点：

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

在创建外键约束时，如果不给外键约束命名，默认自动产生一个外键名（例如：xld_ibfk_1），也可以自定义外键名。

在创建表时就指定外键约束的话，必须先创建 "主表"，在创建 "从表"。

删除表时，必须先删除 "从表"（或删除外键约束），再删除 "主表"。

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

在 "从表" 中指定外键约束，一个表可以建立多个外键约束。

"从表" 的外键列与 "主表" 被参照的列名可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一致，在创建 "从表" 或 "外键约束" 时报错。

当创建外键约束时，MySQL 会默认为该列建立对应的 "普通索引"，索引名就是外键的约束名（根据外键查询效率很高）。

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

注意：外键的自定义名称不可重复（error：Duplicate foreign key constraint name 'fk_email_id'）。

添加外键约束

# 创建表时创建 表级 外键约束
CREATE TABLE
IF NOT EXISTS 表名 (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR (10) email_id INT,
    # 注意：此时外键的关联的主表必须已经创建好了
	CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段) refernces 主表名 (主表主键字段) 
) 
# 修改表时 添加外键约束。此时外键的关联的主表必须已经创建好了
ALTER TABLE 表名 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段) refernces 主表名 (主表主键字段)

# 创建表时创建 表级 外键约束
CREATE TABLE IF NOT EXISTS xld_foreign_key(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(10),
	email_id INT,
    # 此时外键的关联的主表必须已经创建好了（xld_email）。
	CONSTRAINT fk_email_id FOREIGN KEY(email_id) REFERENCES xld_email(id) 
)
# 修改表时 添加外键约束，此时外键的关联的主表必须已经创建好了（xld_email）
ALTER TABLE xld_foreign_key1 ADD CONSTRAINT fk_email_id FOREIGN KEY (email_id) REFERENCES xld_email(id)

约束等级

Cascade ：在 "主表"上 update/delete 记录时，同步 update/delete "从表" 的匹配记录。

set null ：在 "主表"上 update/delete 记录时，将 "从表"上匹配记录的列设为 null（所以要注意这时："从表" 的外键列不能为 not null）。

(默认) no action：如果 "从表" 中有匹配的记录，则不允许对 "主表" 对应候选键进行 update/delete 操作。

restrict：同 no action 方式。

外键约束最好是采用：no update Cascade on delete Set null ，在语句的最后面设置（约束等级是针对 "主表" 来定义的）。

no update：修改操作，约束等级：Cascade。

on delete：删除操作，约束等级：Set null。

# 创建表时创建表级外键约束
CREATE TABLE
IF NOT EXISTS xld_foreign_key (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR (10),
	email_id INT,
	CONSTRAINT fk_email_id FOREIGN KEY (email_id) REFERENCES xld_email (id) ON UPDATE CASCADE ON DELETE SET NULL
);
# 修改表时添加外键约束 
ALTER TABLE xld_foreign_key ADD CONSTRAINT fk_email_id FOREIGN KEY (email_id) REFERENCES xld_email (id) ON UPDATE CASCADE ON DELETE SET NULL;

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
#
ALTER TABLE xld_foreign_key DROP FOREIGN KEY fk_email_id;

删除外键约束的索引

ALTER TABLE 表名 DROP INDEX 外键约束名;
#
ALTER TABLE xld_foreign_key DROP INDEX fk_email_id

阿里 - 开发规范

外键约束和查询没有关系。

不得使用外键与级联，一切外键概念必须在 "应用层" 解决。

说明：外键与级联更适用于 "单机低并发" 的应用程序。不适合 "分布式" 和 "高并发集群"。级联更新是强阻塞（并发串行），存在数据库 "更新风暴" 的风险。外键影响数据库的 "插入速度"。

9. 补充说明

为什么建表时，要加 not null 约束和 default 约束？

答：不想让表中出现 null 值。
为什么不想要 null 的值？
1. 效率不高。影响提高索引效果。
2. 不好比较。null 是特殊的值，比较时只能使用 is null 或 is not null。
3. 在参与运算时，null 值参与运算的情况下，通常返回 null。
带 auto_increment 约束的字段值是从 1 开始的吗？

答：不是。初始值是1，但不一定从1开始，可以通过插入指定值的方式，通过修改表语句来设置从多少开始。

说明：在 MySQL 中，auto_increment 的初始值是1，每新增一条记录，字段值自动加1。该约束往往配合主键约束一起使用。
每个表都可以任意选择存储引擎？

答：单独的表是可以的，但是外键约束（foreign key）是不能跨引擎使用的。

说明：MySQL 支持多种存储引擎，每张表都可以指定一个不同的存储引擎（8.0 默认的是 InnoDB）。使用外键约束是用来保证数据的参照完整性的，如果表与表之间使用了关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。从这个方面来说：表是不能任意选择存储引擎。

posted @ 2023-11-22 13:42 小林当阅读(19) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

小林当

MySQL约束

约束

1. 约束的分类

2. 非空约束

3. 唯一性约束

4. 主键约束

5. 自增列

6. 检查约束

7. 默认约束

8. 外键约束

9. 补充说明

公告