Enforce Data Integrity with PolarDB CHECK Constraints - PolarDB

CHECK constraints enforce conditions on column values. Only rows that satisfy all active constraints can be inserted or updated.

Supported versions

Database engine version: MySQL 8.0
Minimum instance version: polardb-2.5.0_5.4.20-20241224_xcluster8.4.20-20241213

For version details, see Release notes. To check your current version, see View and update the version of an instance.

Syntax

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

Items in square brackets ([]) are optional. The syntax matches the MySQL CHECK constraint syntax. For details, see CHECK Constraints.

Parameter	Description
`CONSTRAINT [symbol]`	Name of the constraint. If omitted, the system generates a name in the `TableName_chk_N` format, for example, `t1_chk_1`.
`CHECK (expr)`	The constraint condition. `expr` is a Boolean expression that returns `TRUE`, `FALSE`, or `UNKNOWN` for each row. `UNKNOWN` means the column value is `NULL`. If `expr` evaluates to `FALSE` or `UNKNOWN`, the row violates the constraint and the insert or update is rejected.
`[NOT] ENFORCED`	Whether the constraint is active:
	- If omitted or set to `ENFORCED`: the constraint is created and enforced. The system checks all inserted and updated data. Existing rows are not checked unless they are updated.
	- If set to `NOT ENFORCED`: the constraint is created but not checked on insert or update.

Add CHECK constraints

PolarDB-X supports two types of CHECK constraints:

Table constraint: defined outside of a column definition; can reference one or more columns.
Column constraint: defined inline within a column definition; applies only to that column.

Add constraints with CREATE TABLE

CREATE TABLE t1
(
  CHECK (c1 <> c2),                             -- Table constraint
  c1 INT CHECK (c1 > 10),                       -- Column constraint
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);

Add constraints with ALTER TABLE

-- Column constraint
ALTER TABLE t1 ADD COLUMN c4 INT CHECK (c4 > 0);

-- Table constraints
ALTER TABLE t1 ADD CHECK (c4 < c3);
ALTER TABLE t1 ADD CONSTRAINT `c4_maximize` CHECK (c4 < 20);

View constraints

Run SHOW CREATE TABLE to list all constraints on a table:

SHOW CREATE TABLE t1;

Sample output:

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

Auto-generated names follow the TableName_chk_N pattern.

Enable or disable CHECK constraints

Use ALTER TABLE ... ALTER CONSTRAINT to change the enforcement status of an existing constraint:

-- Disable a constraint
ALTER TABLE t1 ALTER CONSTRAINT c1_nonzero NOT ENFORCED;

To add a new constraint without enforcing it immediately:

ALTER TABLE t1 ADD CHECK c4_c1_not_equal (c4 <> c1) NOT ENFORCED;

When a constraint is set to NOT ENFORCED, insert and update operations are not validated against it. Existing data is not checked unless it is updated.

Drop a CHECK constraint

Specify the constraint name when dropping:

ALTER TABLE t1 DROP CONSTRAINT t1_chk_1;