title

aliases

summary

约束

/docs-cn/dev/constraints/

/docs-cn/dev/reference/sql/constraints/

TiDB 支持的约束与 MySQL 基本相同，包括非空约束和 CHECK 约束。非空约束规则与 MySQL 相同，而 CHECK 约束需要在 tidb_enable_check_constraint 设置为 ON 后才能开启。可以通过 CREATE TABLE 或 ALTER TABLE 语句添加 CHECK 约束。唯一约束和主键约束也与 MySQL 相似，但 TiDB 目前仅支持对 NONCLUSTERED 的主键进行添加和删除操作。外键约束从 v6.6.0 开始支持，可以使用 CREATE TABLE 和 ALTER TABLE 命令来添加和删除外键。

约束

TiDB 支持的约束与 MySQL 的基本相同。

非空约束

TiDB 支持的非空约束规则与 MySQL 支持的一致。例如：

CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 age INT NOT NULL,
 last_login TIMESTAMP
);

INSERT INTO users (id,age,last_login) VALUES (NULL,123,NOW());

Query OK, 1 row affected (0.02 sec)

INSERT INTO users (id,age,last_login) VALUES (NULL,NULL,NOW());

ERROR 1048 (23000): Column 'age' cannot be null

INSERT INTO users (id,age,last_login) VALUES (NULL,123,NULL);

Query OK, 1 row affected (0.03 sec)

第一条 INSERT 语句成功，因为对于定义为 AUTO_INCREMENT 的列，允许 NULL 作为其特殊值。TiDB 将为其分配下一个自动值。
第二条 INSERT 语句失败，因为 age 列被定义为 NOT NULL。
第三条 INSERT 语句成功，因为 last_login 列没有被明确地指定为 NOT NULL。默认允许 NULL 值。

`CHECK` 约束

注意：

CHECK 约束功能默认关闭，需要将变量 tidb_enable_check_constraint 设置为 ON 后才能开启。

CHECK 约束用于限制表中某个字段的值必须满足指定条件。当为表添加 CHECK 约束后，在插入或者更新表的数据时，TiDB 会检查约束条件是否满足，如果不满足，则会报错。

TiDB 中 CHECK 约束的语法如下，与 MySQL 中一致：

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

语法说明：

[] 中的内容表示可选项。
CONSTRAINT [symbol] 表示 CHECK 约束的名称。
CHECK (expr) 表示约束条件，其中 expr 需要为一个布尔表达式。对于表中的每一行，该表达式的计算结果必须为 TRUE、FALSE 或 UNKNOWN (对于 NULL 值) 中的一个。对于某行数据，如果该表达式计算结果为 FALSE，则表示违反约束条件。
[NOT] ENFORCED 表示是否执行约束，可以用于启用或者禁用 CHECK 约束。

添加 `CHECK` 约束

在 TiDB 中，你可以在 CREATE TABLE 或者 ALTER TABLE 语句中为表添加 CHECK 约束。

在 CREATE TABLE 语句中添加 CHECK 约束的示例：

CREATE TABLE t(a INT CHECK(a > 10) NOT ENFORCED, b INT, c INT, CONSTRAINT c1 CHECK (b > c));

在 ALTER TABLE 语句中添加 CHECK 约束的示例：
```
ALTER TABLE t ADD CONSTRAINT CHECK (1 < c);
```

在添加或者启用 CHECK 约束时，TiDB 会对表中的存量数据进行校验。如果存在违反约束的数据，添加 CHECK 约束操作将失败并且报错。

在添加 CHECK 约束时，可以指定约束名，也可以不指定约束名。如果不指定约束名，那么 TiDB 会自动生成一个格式为 <tableName>_chk_<1, 2, 3...> 的约束名。

查看 `CHECK` 约束

你可以通过 SHOW CREATE TABLE 查看表中的约束信息。例如：

SHOW CREATE TABLE t;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
CONSTRAINT `c1` CHECK ((`b` > `c`)),
CONSTRAINT `t_chk_1` CHECK ((`a` > 10)) /*!80016 NOT ENFORCED */,
CONSTRAINT `t_chk_2` CHECK ((1 < `c`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除 `CHECK` 约束

删除 CHECK 约束时，你需要指定需要删除的约束名。例如：

ALTER TABLE t DROP CONSTRAINT t_chk_1;

启用或禁用 `CHECK` 约束

在为表添加 CHECK 约束的时候，可以指定当插入或者更新数据时 TiDB 是否执行约束检查。

如果指定了 NOT ENFORCED，当插入或者更新数据时，TiDB 不会检查约束条件。
如果未指定 NOT ENFORCED 或者指定了 ENFORCED，当插入或者更新数据时，TiDB 会检查约束条件。

除了在添加约束时候指定 [NOT] ENFORCED，你还可以在 ALTER TABLE 语句中启用或者禁用 CHECK 约束。例如：

ALTER TABLE t ALTER CONSTRAINT c1 NOT ENFORCED;

与 MySQL 的兼容性

不支持在添加列的同时添加 CHECK 约束（例如，ALTER TABLE t ADD COLUMN a CHECK(a > 0))），否则只有列会被添加成功，TiDB 会忽略 CHECK 约束但不会报错。
不支持使用 ALTER TABLE t CHANGE a b int CHECK(b > 0) 添加 CHECK 约束，使用该语句时 TiDB 会报错。

唯一约束

唯一约束是指唯一索引和主键列中所有的非空值都是唯一的。

乐观事务

在 TiDB 的乐观事务中，默认会对唯一约束进行惰性检查。通过在事务提交时再进行批量检查，TiDB 能够减少网络开销、提升性能。例如：

DROP TABLE IF EXISTS users;
CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(60) NOT NULL,
 UNIQUE KEY (username)
);
INSERT INTO users (username) VALUES ('dave'), ('sarah'), ('bill');

乐观事务模式下且 tidb_constraint_check_in_place=OFF：

BEGIN OPTIMISTIC;
INSERT INTO users (username) VALUES ('jane'), ('chris'), ('bill');

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

INSERT INTO users (username) VALUES ('steve'),('elizabeth');

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

COMMIT;

ERROR 1062 (23000): Duplicate entry 'bill' for key 'users.username'

在以上乐观事务的示例中，唯一约束的检查推迟到事务提交时才进行。由于 bill 值已经存在，这一行为导致了重复键错误。

你可通过设置 tidb_constraint_check_in_place 为 ON 停用此行为（该变量仅适用于乐观事务，悲观事务需通过 tidb_constraint_check_in_place_pessimistic 设置）。当 tidb_constraint_check_in_place 设置为 ON 时，TiDB 会在执行语句时就对唯一约束进行检查。例如：

DROP TABLE IF EXISTS users;
CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(60) NOT NULL,
 UNIQUE KEY (username)
);
INSERT INTO users (username) VALUES ('dave'), ('sarah'), ('bill');

SET tidb_constraint_check_in_place = ON;

Query OK, 0 rows affected (0.00 sec)

BEGIN OPTIMISTIC;

Query OK, 0 rows affected (0.00 sec)

INSERT INTO users (username) VALUES ('jane'), ('chris'), ('bill');

ERROR 1062 (23000): Duplicate entry 'bill' for key 'users.username'

第一条 INSERT 语句导致了重复键错误。这会造成额外的网络通信开销，并可能降低插入操作的吞吐量。

悲观事务

在 TiDB 的悲观事务中，默认在执行任何一条需要插入或更新唯一索引的 SQL 语句时都会进行唯一约束检查：

DROP TABLE IF EXISTS users;
CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(60) NOT NULL,
 UNIQUE KEY (username)
);
INSERT INTO users (username) VALUES ('dave'), ('sarah'), ('bill');

BEGIN PESSIMISTIC;
INSERT INTO users (username) VALUES ('jane'), ('chris'), ('bill');

ERROR 1062 (23000): Duplicate entry 'bill' for key 'users.username'

对于悲观事务，你可以设置变量 tidb_constraint_check_in_place_pessimistic 为 OFF 来推迟唯一约束检查，到下一次对该唯一索引项加锁时或事务提交时再进行检查，同时也跳过对该悲观锁加锁，以获得更好的性能。此时需要注意：

由于推迟了唯一约束检查，TiDB 可能会读取到不满足唯一约束的结果，执行 COMMIT 语句时可能返回 Duplicate entry 错误。返回该错误时，TiDB 会回滚当前事务。

下面这个例子跳过了对 bill 的加锁，因此 TiDB 可能读到不满足唯一性约束的结果：

SET tidb_constraint_check_in_place_pessimistic = OFF;
BEGIN PESSIMISTIC;
INSERT INTO users (username) VALUES ('jane'), ('chris'), ('bill'); -- Query OK, 3 rows affected
SELECT * FROM users FOR UPDATE;

TiDB 读到了不满足唯一性约束的结果：有两个 bill。

+----+----------+
| id | username |
+----+----------+
| 1  | dave     |
| 2  | sarah    |
| 3  | bill     |
| 7  | jane     |
| 8  | chris    |
| 9  | bill     |
+----+----------+

此时，如果提交事务，TiDB 将进行唯一约束检查，报出 Duplicate entry 错误并回滚事务。

COMMIT;

ERROR 1062 (23000): Duplicate entry 'bill' for key 'users.username'

关闭该变量时，如果在事务中写入数据，执行 COMMIT 语句可能会返回 Write conflict 错误。返回该错误时，TiDB 会回滚当前事务。

在下面这个例子中，当有并发事务写入时，跳过悲观锁导致事务提交时报出 Write conflict 错误并回滚。

DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(60) NOT NULL,
UNIQUE KEY (username)
);

SET tidb_constraint_check_in_place_pessimistic = OFF;
BEGIN PESSIMISTIC;
INSERT INTO users (username) VALUES ('jane'), ('chris'), ('bill'); -- Query OK, 3 rows affected

然后另一个会话中写入了 bill：

INSERT INTO users (username) VALUES ('bill'); -- Query OK, 1 row affected

在第一个会话中提交时，TiDB 会报出 Write conflict 错误。

COMMIT;

ERROR 9007 (HY000): Write conflict, txnStartTS=435688780611190794, conflictStartTS=435688783311536129, conflictCommitTS=435688783311536130, key={tableID=74, indexID=1, indexValues={bill, }} primary={tableID=74, indexID=1, indexValues={bill, }}, reason=LazyUniquenessCheck [try again later]

关闭该变量时，如果多个悲观事务之间存在写冲突，悲观锁可能会在其它悲观事务提交时被强制回滚，因此产生 PessimisticLockNotFound 错误。发生该错误时，说明该业务不适合推迟悲观事务的唯一约束检查，应考虑调整业务避免冲突，或在发生错误后重试事务。

关闭该变量会导致悲观事务中可能报出错误 8147: LazyUniquenessCheckFailure。

注意：

返回 8147 错误时当前事务回滚。

下面的例子在 INSERT 语句执行时跳过了一次加锁后，在 DELETE 语句执行时对该唯一索引加锁并检查，即会在该语句报错：

SET tidb_constraint_check_in_place_pessimistic = OFF;
BEGIN PESSIMISTIC;
INSERT INTO users (username) VALUES ('jane'), ('chris'), ('bill'); -- Query OK, 3 rows affected
DELETE FROM users where username = 'bill';

ERROR 8147 (23000): transaction aborted because lazy uniqueness check is enabled and an error occurred: [kv:1062]Duplicate entry 'bill' for key 'users.username'

关闭该变量时，1062 Duplicate entry 报错不一定是当前执行的 SQL 语句所发生的错误。因此，在一个事务操作多个表，且这些表有同名索引时，请注意 1062 报错信息中提示的是哪个表的哪个索引发生了错误。

主键约束

与 MySQL 行为一样，主键约束包含了唯一约束，即创建了主键约束相当于拥有了唯一约束。此外，TiDB 其他的主键约束规则也与 MySQL 相似。例如：

CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY);

Query OK, 0 rows affected (0.12 sec)

CREATE TABLE t2 (a INT NULL PRIMARY KEY);

ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

CREATE TABLE t3 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL PRIMARY KEY);

ERROR 1068 (42000): Multiple primary key defined

CREATE TABLE t4 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b));

Query OK, 0 rows affected (0.10 sec)

分析：

表 t2 创建失败，因为定义为主键的列 a 不能允许 NULL 值。
表 t3 创建失败，因为一张表只能有一个主键。
表 t4 创建成功，因为虽然只能有一个主键，但 TiDB 支持定义一个多列组合作为复合主键。

除上述规则外，TiDB 目前仅支持对 NONCLUSTERED 的主键进行添加和删除操作。例如：

CREATE TABLE t5 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b) CLUSTERED);
ALTER TABLE t5 DROP PRIMARY KEY;

ERROR 8200 (HY000): Unsupported drop primary key when the table is using clustered index

CREATE TABLE t5 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b) NONCLUSTERED);
ALTER TABLE t5 DROP PRIMARY KEY;

Query OK, 0 rows affected (0.10 sec)

要了解关于 CLUSTERED 主键的详细信息，请参考聚簇索引。

外键约束

注意：

TiDB 从 v6.6.0 开始支持外键约束。在 v6.6.0 之前，TiDB 支持创建和删除外键约束，但外键约束并不生效。升级到 v6.6.0 或更高版本后，可以先删除不生效的外键后再创建外键使外键约束生效。外键约束在 v8.5.0 成为正式功能。

TiDB 支持创建外键约束。例如：

CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 doc JSON
);
CREATE TABLE orders (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 user_id INT NOT NULL,
 doc JSON,
 FOREIGN KEY fk_user_id (user_id) REFERENCES users(id)
);

SELECT table_name, column_name, constraint_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage WHERE table_name IN ('users', 'orders');

+------------+-------------+-----------------+-----------------------+------------------------+
| table_name | column_name | constraint_name | referenced_table_name | referenced_column_name |
+------------+-------------+-----------------+-----------------------+------------------------+
| users      | id          | PRIMARY         | NULL                  | NULL                   |
| orders     | id          | PRIMARY         | NULL                  | NULL                   |
| orders     | user_id     | fk_user_id      | users                 | id                     |
+------------+-------------+-----------------+-----------------------+------------------------+
3 rows in set (0.00 sec)

TiDB 也支持使用 ALTER TABLE 命令来删除外键 (DROP FOREIGN KEY) 和添加外键 (ADD FOREIGN KEY)：

ALTER TABLE orders DROP FOREIGN KEY fk_user_id;
ALTER TABLE orders ADD FOREIGN KEY fk_user_id (user_id) REFERENCES users(id);

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

constraints.md

constraints.md

约束

非空约束

`CHECK` 约束

添加 `CHECK` 约束

查看 `CHECK` 约束

删除 `CHECK` 约束

启用或禁用 `CHECK` 约束

与 MySQL 的兼容性

唯一约束

乐观事务

悲观事务

主键约束

外键约束

Files

constraints.md

Latest commit

History

constraints.md

File metadata and controls

约束

非空约束

CHECK 约束

添加 CHECK 约束

查看 CHECK 约束

删除 CHECK 约束

启用或禁用 CHECK 约束

与 MySQL 的兼容性

唯一约束

乐观事务

悲观事务

主键约束

外键约束

`CHECK` 约束

添加 `CHECK` 约束

查看 `CHECK` 约束

删除 `CHECK` 约束

启用或禁用 `CHECK` 约束