The CHECK integrity constraint is defined on a column or a set of columns. This constraint requires that the data rows satisfy a user-defined check condition or have the condition evaluation result as unknown. If a Data Manipulation Language (DML) statement causes the check condition of a CHECK constraint to evaluate to false, the statement will be rolled back and an error will be raised.
The following example shows the features of the syntax of a CHECK constraint:
obclient> CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT t1_cst_c1_equals_c2 CHECK(c1 = c2));
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 row affected
obclient> INSERT INTO t1 VALUES(2, 3);
OBE-02290: check constraint violated
You can use CHECK constraints to define check conditions to enforce specific integrity rules. When you define the check condition for a CHECK constraint, note that:
The check condition must be a Boolean expression that is used to evaluate values inserted to or updated in the row.
The check condition cannot contain subqueries, sequences, pseudocolumns such as ROWNUM, or SQL functions that may have different results in different environments or scenarios, such as SYSDATE(), UID(), USER(), and USERENV().
A column can be referenced in the check conditions of multiple CHECK constraints. You can define an unlimited number of CHECK constraints on a column. If you create multiple CHECK constraints on a column, make sure that the check conditions of the constraints do not conflict with each other. The check order of the CHECK constraints is uncertain, and OceanBase Database does not check whether the CHECK constraints are mutually exclusive.