使用CHECK約束限制欄位值-雲原生資料庫 PolarDB-阿里雲

CHECK約束用於限制表中某個欄位的值必須滿足指定條件。當為表添加CHECK約束後，在插入或者更新表的資料時會檢查約束條件是否滿足，只有滿足約束條件才能完成操作。

版本限制

引擎版本需為MySQL 8.0，且執行個體版本為polardb-2.5.0_5.4.20-20241224_xcluster8.4.20-20241213及以上。

說明

執行個體版本的規則說明，請參見版本說明。
執行個體版本的查看方式，請參見查看執行個體版本。

文法

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

說明

CHECK約束文法與MySQL官方文法一致。更多資訊，請參見CHECK Constraints。

參數說明：

參數名稱	說明
CONSTRAINT [symbol]	約束名稱。如沒有顯式設定名稱，系統會自動產生`TableName_chk_(1,2,3...)`格式的名稱。
CHECK (expr)	約束條件。其中`expr`為布林運算式，對於表中的每一行，該運算式的計算結果必須為`TRUE`、`FALSE`或`UNKNOWN`（列值為`NULL`時）中的一個。對於某行資料，當該運算式計算結果為`FALSE`或`UNKNOWN`，表示違反約束條件。
[NOT] ENFORCED	控制CHECK約束是否生效。不指定該參數時，CHECK約束是預設生效的。

樣本

添加CHECK約束

在PolarDB-X中，CREATE TABLE、ALTER TABLE語句中都支援添加CHECK約束，且表和列都支援添加約束。

重要

表級約束：不出現在任何列定義內，可以參考資料表中的任何一個或多個列作為約束條件。
資料行層級條件約束：出現在列定義內，且只能引用該列作為約束條件。

CREATE TABLE語句中添加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)
);

ALTER TABLE語句中添加CHECK約束，樣本如下：

-- 資料行層級條件約束
ALTER TABLE t1 ADD COLUMN c4 INT CHECK (c4 > 0);

-- 表級約束
ALTER TABLE t1 ADD CHECK (c4 < c3);
ALTER TABLE t1 ADD CONSTRAINT `c4_maximize` CHECK (c4 < 20);

查看添加的CHECK約束：

SHOW CREATE TABLE t1;

返回結果如下：

CREATE TABLE `t1` (
	`c1` int DEFAULT NULL,
	`c2` int DEFAULT NULL,
	`c3` int DEFAULT NULL,
	`c4` int DEFAULT NULL,
	CONSTRAINT `c1_nonzero` CHECK (`c1` <> 0),
	CONSTRAINT `c2_positive` CHECK (`c2` > 0),
	CONSTRAINT `c4_maximize` CHECK (`c4` < 20),
	CONSTRAINT `t1_chk_1` CHECK (`c1` <> `c2`),
	CONSTRAINT `t1_chk_2` CHECK (`c1` > 10),
	CONSTRAINT `t1_chk_3` CHECK (`c3` < 100),
	CONSTRAINT `t1_chk_4` CHECK (`c1` > `c3`),
	CONSTRAINT `t1_chk_5` CHECK (`c4` > 0),
	CONSTRAINT `t1_chk_6` CHECK (`c4` < `c3`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_general_ci

CHECK約束的生效與失效

如果指定了NOT ENFORCED，則約束不生效，即插入或更新資料時也不會按照約束條件檢查相關資料。
```
ALTER TABLE t1 ADD CHECK c4_c1_not_equal (c4 <> c1) NOT ENFORCED;
```
如果未指定NOT ENFORCED或指定為ENFORCED，則按照約束條件檢查插入和更新的資料，存量資料如果不涉及更新操作則不會再檢查。

通過ALTER TABLE調整已有CHECK約束是否生效。

ALTER TABLE t1 ALTER CONSTRAINT c1_nonzero NOT ENFORCED;

刪除CHECK約束

刪除CHECK約束時，需要指定需要刪除的約束名，樣本如下：

ALTER TABLE t1 DROP CONSTRAINT t1_chk_1;