A CHECK constraint on a column or set of columns requires that a user-defined check condition be evaluated as true or unknown for each row. If the check condition of a CHECK constraint is evaluated as false after a DML statement is executed, the statement is rolled back and an error message is returned.
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);
ORA-02290: check constraint violated
You can use CHECK constraints to define check conditions to enforce specific integrity rules. Take note of the following limits when you define the check condition for a CHECK constraint:
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.