📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

Constraints

TiDB 支持几乎与 MySQL 相同的约束。

NOT NULL

TiDB 支持的 NOT NULL 约束与 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 约束限制表中某列的值必须满足你指定的条件。当在表中添加 CHECK 约束时,TiDB 会在插入或更新数据时检查是否满足该约束。如果不满足,则返回错误。

TiDB 中 CHECK 约束的语法与 MySQL 相同:

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

语法说明:

  • []:中括号内的内容为可选。
  • CONSTRAINT [symbol]:指定 CHECK 约束的名称。
  • CHECK (expr):指定约束条件,其中 expr 需要是布尔表达式。对于表中的每一行,该表达式的计算结果必须是 TRUEFALSEUNKNOWN(对于 NULL 值)。如果某行的计算结果为 FALSE,表示违反了约束。
  • [NOT] ENFORCED:指定是否强制执行该约束。可以用来启用或禁用 CHECK 约束。

添加 CHECK 约束

在 TiDB 中,可以使用 CREATE TABLEALTER 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 会报错。

UNIQUE KEY

唯一约束意味着在唯一索引和主键列中,所有非空值都必须是唯一的。

乐观事务

默认情况下,对于乐观事务,TiDB 在执行阶段会【懒惰】(/transaction-overview.md#lazy-check-of-constraints) 地检查唯一约束,在提交阶段会严格检查,这有助于减少网络开销并提升性能。

例如:

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_placeON 来禁用此行为。当 tidb_constraint_check_in_place=ON 时,执行语句时会检查唯一约束。注意,此变量只对乐观事务生效。对于悲观事务,可以使用 tidb_constraint_check_in_place_pessimistic 变量控制。

例如:

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 语句时会检查 UNIQUE 约束。

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 会将唯一索引的唯一性检查延后(到下一次需要锁定索引时或事务提交时),并跳过相应的悲观锁。在使用此变量时,请注意:

  • 由于延迟了唯一性约束检查,TiDB 可能会读取不满足唯一性约束的结果,并在提交悲观事务时返回 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'
  • 当此变量被禁用时,提交需要写入数据的悲观事务可能会返回 Write conflict 错误。当出现此错误时,TiDB 会回滚当前事务。

    例如,两个并发事务需要向同一表插入数据,跳过悲观锁会导致 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]
  • 当此变量被禁用时,如果多个悲观事务之间存在写冲突,悲观锁可能在其他悲观事务提交时被强制回滚,从而导致 Pessimistic lock not found 错误。当出现此错误时,意味着延迟了悲观事务的唯一性约束检查,不适合你的应用场景。此时可以考虑调整应用逻辑以避免冲突,或在发生错误后重试事务。

  • 当此变量被禁用时,在悲观事务中执行 DML 语句可能返回错误 8147: LazyUniquenessCheckFailure

    例如,在执行 INSERT 语句时,TiDB 会跳过锁定;在执行 DELETE 语句时,TiDB 会锁定唯一索引并检查唯一性约束,因此会在 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 错误信息,确认错误实际来源的索引。

PRIMARY KEY

与 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 类型主键的更多细节,请参考 clustered index

FOREIGN KEY

TiDB 支持在 DDL 命令中创建 FOREIGN KEY 约束。

例如:

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 KEYADD 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);

文档内容是否有帮助?