CHECK constraints - PolarDB - Alibaba Cloud Documentation Center

CHECK constraints are used to enforce specific conditions on the values of a column or a combination of columns in a table. If you define a CHECK constraint for a table, only values that meet the constraint can be added to the table by performing insert or update operations.

Supported versions

The database engine version is MySQL 8.0, and the instance version is polardb-2.5.0_5.4.20-20241224_xcluster8.4.20-20241213 or later.

Note

For more information about instance versions, see Release notes.
For more information about how to view the instance version, see View and update the version of an instance.

Syntax

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

Note

The syntax is the same as that of CHECK constraints provided by MySQL. For more information, see CHECK Constraints.

Parameters:

Parameter	Description
CONSTRAINT [symbol]	The name of the constraint. If you do not specify a name, the system automatically generates one in the `TableName_chk_(1,2,3...)` format.
CHECK (expr)	The constraint condition. `expr` is a Boolean expression that returns `TRUE`, `FALSE`, or `UNKNOWN` for each table row. UNKNOWN means that the column value is `NULL`. If `expr` is `FALSE` or `UNKNOWN`, the row does not meet the condition and violates the constraint.
[NOT] ENFORCED	Specifies whether to disable CHECK constraints. By default, CHECK constraints are enabled.

Examples

Add CHECK constraints

PolarDB-X allows you to use CREATE TABLE and ALTER TABLE statements to add CHECK constraints on tables or columns.

Important

Table-level constraints: defined outside of individual column definitions and can apply to one or more columns.
Column-level constraints: defined within the individual column definitions and apply only to the values of that specific column.

Add CHECK constraints to the CREATE TABLE statement. Example:

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 CHECK constraints to the ALTER TABLE statement. Example:

-- 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 CHECK constraints.

SHOW CREATE TABLE t1;

Sample result:

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

Enable or disable CHECK constraints

If you specify the NOT ENFORCED option for a CHECK constraint, the constraint does not take effect. When you insert or update data, the system does not check it against the constraint. Example:
```
ALTER TABLE t1 ADD CHECK c4_c1_not_equal (c4 <> c1) NOT ENFORCED;
```
If you specify the ENFORCED option or do not specify the NOT ENFORCED option, the system checks all inserted and updated data against the constraint. Existing data is not checked unless it is updated.
You can use the ALTER TABLE statement to adjust the status of a CHECK constraint. Example:

ALTER TABLE t1 ALTER CONSTRAINT c1_nonzero NOT ENFORCED;

Delete a CHECK constraint

When you delete a CHECK constraint, you must specify its name. Example:

ALTER TABLE t1 DROP CONSTRAINT t1_chk_1;