MySQL 约束

1.简介

在数据库中，约束是对表中数据的一种限制条件，能够确保数据的完整性和一致性。

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

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

2.分类

根据约束数据列的限制， 约束可分为：

• 单列约束：每个约束只约束一列(字段)
• 多列约束：每个约束可约束多列数据

根据约束的作用范围，约束可分为：

• 列级约束：只能作用在一个列上，跟在列的定义后面
• 表级约束：可以作用在多个列上，不与列一起，而是单独定义

根据约束所起的作用，约束可分为：

• 主键约束

主键约束确保表中的每一行都具有唯一标识符，能够唯一标识该表中的每条记录。

例如，学生信息表中的学号是唯一的。

• 唯一约束

唯一约束用于保证指定列或指定列组合不允许出现重复值。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

• 外键约束

外键约束用于建立表与表之间的关系，确保引用另一个表中的值时的完整性。

外键约束经常和主键约束一起使用，用来确保数据的完整性，即保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。

例如，在员工信息表中，员工所属部门是一个外键，因为该字段是部门表的主键。

• 检查约束

检查约束允许你定义满足特定条件值的范围或规则，用于检查字段值是否有效。

例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的。如果是小学生，年龄不低于 6 岁才可入学。在设置字段的检查约束时要根据实际情况设置，这样能够减少无效数据的输入。

• 默认值约束

默认约束规定了在未提供值时，某一列应采用的默认值。

例如，在录入商品信息，如果不输入上架状态“上架”或“下架”，那么会默认设置状态为“未上架”。

• 非空约束

指定某列的值不为空，在插入数据的时候必须非空。

例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

注意，上述所有约束中，一个数据表中，无论是单一主键还是复合主键，只能有一个主键约束，其它约束可以有多个。

3.创建约束

创建主键约束

建表时在字段后添加 PRIMARY KEY 表明是主键。

如果某个数据列的类型是整型，而且该列作为主键列，则可指定该列为具有自增长功能。指定自增长功能通常用于逻辑主键列，该列没有任何物理意义，仅仅为了标识每一行。MySQl 使用 AUTO_INCREMENT 设置自增长。

CREATE TABLE users(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);
1
2
3
4

或者是在定义完所有字段之后指定主键，语法格式如下：

[CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] 'string'
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CONSTRAINT [symbol]：这是一个可选部分。你可以为主键约束指定一个名称，以便在将来引用它。symbol 是主键约束的名称，可以根据你的喜好为其指定，如果不指定，则系统会为主键自动生成一个名称。

PRIMARY KEY：这是关键字，指示这是一个主键约束。

index_type：这是可选的部分，用于指定主键的索引类型。主键索引可以是 BTREE（B树索引，通常用于普通主键）或 HASH（哈希索引，通常用于自动递增主键）。大多数情况下，不需要显式指定索引类型，系统会根据上下文自动选择适当的索引类型。

(key_part,…)：这是主键的列列表。在括号中列出了构成主键的一个或多个列。主键是用于唯一标识表中每一行的一个或多个列的组合。这些列的值必须唯一且不为空。

index_option：这是可选的部分，用于指定主键索引的选项。这些选项可以包括 USING（指定索引类型）、KEY_BLOCK_SIZE（指定索引块大小）、COMMENT（为索引添加注释）等。

以下是一个示例，演示如何在定义完所有字段之后指定主键：

CREATE TABLE users(
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    CONSTRAINT pk_users PRIMARY KEY (id)
);
1
2
3
4
5

在上述示例中，我们创建了一个名为 users 的表，然后定义了一个名为 pk_users 的主键约束，它由 id 列组成。这意味着 id 列将唯一标识表中每一行。

创建唯一约束

建表时在字段后使用 UNIQUE 创建唯一约束。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

CREATE TABLE users (
    id INT,
    name VARCHAR(255) UNIQUE
);
1
2
3
4

可以创建一个多列唯一约束，以确保多个列的组合值在表中是唯一的。这种约束可以用于确保表中不会出现重复的组合。

以下是创建多列唯一约束的示例：

CREATE TABLE example_table (
    id INT,
    name VARCHAR(255),
    email VARCHAR(255),
    UNIQUE KEY (name, email)
);
1
2
3
4
5
6

创建外键约束

建表时使用 FOREIGN KEY 引用主表创建外键。

例如，在员工信息表中，员工所属部门是一个外键，因为该字段是部门表的主键。

-- 主表：部门表
CREATE TABLE dept(
    id INT AUTO_INCREMENT PRIMARY KEY,		-- 部门编号
    name varchar(64)			-- 部门名称
);

-- 从表：员工表
CREATE TABLE emp(
    id INT AUTO_INCREMENT PRIMARY KEY,  		-- 员工编号
    name varchar(16),     						-- 员工姓名
    dept_id int,		   						-- 员工所在部门
    FOREIGN KEY (dept_id) REFERENCES dept (id)	-- 在从表中指定外键约束
);
1
2
3
4
5
6
7
8
9
10
11
12
13

创建检查约束

在 MySQL 8.0.16 之前，CREATE TABLE 仅允许以下有限版本的表 CHECK 约束语法，该语法将被解析并忽略：

CHECK (expr)
1

从 MySQL 8.0.16 开始，CREATE TABLE 允许所有存储引擎使用表和列 CHECK 约束的核心功能。 对于表约束和列约束，CREATE TABLE 允许使用以下 CHECK 约束语法：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
1

可选 symbol 指定约束的名称。 如果省略，MySQL 会根据表名、_chk_ 和序数 (1, 2, 3, …) 生成一个名称。约束名称的最大长度为 64 个字符。 它们区分大小写，但不区分重音符号。

expr 将约束条件指定为布尔表达式，对于表的每一行，该表达式的计算结果必须为 TRUE 或 UNKNOWN（对于 NULL 值）。 如果条件计算结果为 FALSE，则失败并发生约束冲突。

可选的强制执行子句指示是否强制执行约束：

• 如果省略或指定为 ENFORCED，则创建并强制执行约束。这意味着在插入、更新或删除数据时，MySQL 将检查约束条件（expr）是否满足，如果条件不满足，则不允许进行相应的操作。
• 如果指定为 NOT ENFORCED，则创建约束但不强制执行。这意味着约束冲突将被记录下来，但不会影响插入、更新或删除数据的操作。

CHECK 约束可指定为表约束或列约束：

• 表约束不会出现在列定义中，并且可以引用任何表列。允许对表定义中稍后出现的列进行前向引用。
• 列约束出现在列定义中，并且只能引用该列。

CREATE TABLE t1(
  CHECK (c1 <> c2),
  c1 INT CHECK (c1 > 10),
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);
1
2
3
4
5
6
7
8

上面的检查约束定义中使用了有名和无名的定义方式。

CHECK (c1 <> c2) 是表约束：它出现在任何列定义之外，因此它可以（并且确实）引用多个表列。 此约束包含对尚未定义的列的前向引用。没有指定约束名称，因此 MySQL 生成一个名称。

接下来的三个约束是列约束：每个约束都出现在列定义中，因此只能引用正在定义的列。 其中一项约束是明确命名的。 MySQL 为另外两个分别生成一个名称。

最后两个约束是表约束。 其中之一已被明确命名。 MySQL 为另一个生成一个名称。

创建默认值约束

建表时在字段后使用 DEFAULT 添加默认值可创建默认值约束。

例如，在录入商品信息，如果不输入上架状态“上架”或“下架”，那么会默认设置状态为“未上架”。

CREATE TABLE products(
	id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    sale_status TINYINT DEFAULT 0 -- 0 未上架 1 上架 2 下架
);
1
2
3
4
5
6

创建非空约束

建表时用 NOT NULL 约束的字段不能为 NULL 值，必须给定具体的数据。

例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

CREATE TABLE students(
    id INT AUTO_INCREMENT PRIMARY KEY,	-- 学生学号
    name varchar(64) NOT NULL			-- 学生姓名
);
1
2
3
4

4.查看约束

通过 SHOW CREATE TABLE 语句可以查看表的创建语句，结果包含了表的所有约束。

SHOW CREATE TABLE students\G

*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1
2
3
4
5
6
7
8
9

在 MySQL 的 information_schema 数据库里的 table_constraints 表保存了该数据库实例中所有的约束信息，用户可以通过查询该表获取该数据库的约束信息。

SELECT * FROM information_schema.table_constraints WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='tbl_name';
1

结果列说明如下：

• CONSTRAINT_CATALOG：这是约束所属的目录（catalog）的名称。在 MySQL 中，通常情况下，这个值通常为 def，因为 MySQL 不使用目录的概念。
• CONSTRAINT_SCHEMA：约束所属的数据库。
• CONSTRAINT_NAME：约束名称。对于主键约束、唯一键约束、外键约束和检查约束，它将是一个用户定义的名称。
• TABLE_SCHEMA：受约束表所在数据库。
• TABLE_NAME：受约束表的名称。
• CONSTRAINT_TYPE：约束类型，取值如下：
  • “PRIMARY KEY”：主键约束
  • “UNIQUE”：唯一键约束
  • “FOREIGN KEY”：外键约束
  • “CHECK”：检查约束

5.删除约束

要删除 MySQL 表中的约束，可以使用 ALTER TABLE 语句并指定要删除的约束类型和名称。

1. 删除主键约束

ALTER TABLE table_name DROP PRIMARY KEY;
1

2. 删除唯一约束

ALTER TABLE table_name DROP INDEX unique_constraint_name;
1

3. 删除外键约束

ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;
1

4. 删除检查约束

ALTER TABLE table_name DROP CHECK check_constraint_name;
1

5. 删除默认值约束

要删除列上的默认值约束，可以使用 ALTER TABLE 语句并使用 ALTER COLUMN 子句将列的默认值更改为 NULL 或其他适当的默认值。

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT NULL;
1
2

6. 删除非空约束

删除非空约束表示列允许为空。

ALTER TABLE table_name
MODIFY COLUMN column_name data_type NULL;
1
2

6.修改约束

在 MySQL 中，要修改约束，通常需要使用 ALTER TABLE 语句，所需的更改具体取决于要修改的约束类型。以下是一些常见的约束类型以及如何修改它们的示例。

1. 修改主键约束

如果要修改表的主键约束，首先需要删除原来的主键约束，然后再添加新的主键约束。

-- 添加新的主键约束
ALTER TABLE table_name ADD PRIMARY KEY (new_primary_key_column);
1
2

2. 修改唯一约束

修改唯一约束类似于修改主键约束，首先删除原来的唯一约束，然后添加新的唯一约束。

-- 添加新的唯一约束
ALTER TABLE table_name ADD UNIQUE (new_unique_column);
1
2

3. 修改外键约束

若要修改外键约束，通常需要删除原来的外键约束，然后再添加新的外键约束。确保新的外键约束与原始表的关联列和引用表的关联列匹配。

-- 添加新的外键约束
ALTER TABLE table_name
ADD FOREIGN KEY (new_foreign_key_column)
REFERENCES referenced_table(referenced_column);
1
2
3
4

4. 修改检查约束

修改检查约束通常需要删除原来的检查约束，然后添加新的检查约束。确保新的检查约束表达式满足你的需求。

-- 添加新的检查约束
ALTER TABLE table_name ADD CHECK (new_check_expression);
1
2

5. 修改默认值约束

ALTER TABLE table_name
ALTER COLUMN col_name SET DEFAULT new_default_value;
1
2

6. 修改非空约束

如果要修改非空约束，可以将列从允许为空更改为不允许为空，或者从不允许为空更改为允许为空。

ALTER TABLE table_name
MODIFY COLUMN column_name data_type [NOT] NULL;
1
2

---

参考文献

1.6.3 How MySQL Deals with Constraints
13.1.20 CREATE TABLE Statement
13.1.20.5 FOREIGN KEY Constraints
13.1.20.6 CHECK Constraints
MySQL - 花粥之间
MySQL入门教程 - 不剪发的Tony老师