制約
TiDB は MySQL とほぼ同じ制約をサポートします。
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 はシーケンス番号を自動的に生成します。 - 2 番目の
INSERTステートメントは、age列がNOT NULLとして定義されているため失敗します。 - 3 番目の
INSERTステートメントは、last_login列がNOT NULLとして明示的に定義されていないため成功します。デフォルトでは NULL 値が許可されます。
チェック
注記：
CHECK制約機能はデフォルトで無効になっています。有効にするには、tidb_enable_check_constraint変数をONに設定する必要があります。
CHECK制約は、テーブル内の列の値を、指定した条件を満たすように制限します。3 制約CHECKテーブルに追加されると、TiDB はテーブルへのデータの挿入または更新中に制約が満たされているかどうかを確認します。制約が満たされていない場合は、エラーが返されます。
TiDB のCHECK制約の構文は、MySQL の構文と同じです。
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
構文の説明:
[]:[]内の内容はオプションです。CONSTRAINT [symbol]:CHECK番目の制約の名前を指定します。CHECK (expr): 制約条件を指定します。2exprブール式である必要があります。テーブルの各行について、この式の計算結果は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(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) 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 互換性
- 列 (たとえば、
ALTER TABLE t ADD COLUMN a CHECK(a > 0)) を追加するときにCHECK制約を追加することはサポートされていません。この場合、列のみが正常に追加され、TiDB はエラーを報告せずにCHECK制約を無視します。 ALTER TABLE t CHANGE a b int CHECK(b > 0)使用してCHECK制約を追加することはサポートされていません。このステートメントを実行すると、TiDB はエラーを報告します。
ユニークキー
一意制約とは、一意のインデックスと主キー列内のすべての NULL 以外の値が一意であることを意味します。
楽観的な取引
デフォルトでは、楽観的トランザクションの場合、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=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ステートメントで重複キー エラーが発生しました。これにより、追加のネットワーク通信オーバーヘッドが発生し、挿入操作のスループットが低下する可能性があります。
悲観的な取引
悲観的トランザクションでは、一意のインデックスの挿入または更新を必要とする SQL ステートメントが実行されると、TiDB はデフォルトで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 のクエリ結果には 2 つの
bills含まれており、一意性制約を満たしていません。+----+----------+ | 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 は現在のトランザクションをロールバックします。次の例のように、2 つの同時トランザクションが同じテーブルにデータを挿入する必要がある場合、悲観的ロックをスキップすると、トランザクションをコミットしたときに 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が返される可能性があります。注記：
8147エラーが発生すると、TiDB は現在のトランザクションをロールバックします。次の例のように、
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エラー メッセージをチェックして、実際にエラーの原因となっているインデックスを見つける必要があります。
主キー
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)
- 列
aが主キーとして定義されており、NULL 値が許可されないため、テーブルt2を作成できませんでした。 - テーブルには主キーを 1 つしか持てないため、テーブル
t3を作成できませんでした。 - 主キーは 1 つしか存在できませんが、TiDB では複数の列を複合主キーとして定義することがサポートされているため、表
t4正常に作成されました。
上記のルールに加えて、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型の主キーの詳細についてはクラスター化インデックスを参照してください。
外部キー
注記：
v6.6.0 以降、TiDB はFOREIGN KEY制約を実験的機能としてサポートします。v6.6.0 より前の TiDB では、外部キー制約の作成と削除がサポートされていましたが、制約は実際には有効ではありませんでした。TiDB を v6.6.0 にアップグレードした後、無効な外部キーを削除して新しい外部キーを作成し、外部キー制約を有効にすることができます。
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 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);