CHECK constraint evaluation

A developer approached me with the question “Are check constraints only evaluated when the columns that they apply to are modified and not some other column on the row?”. An unusual question perhaps but as it turned out they were creating a number of check constraints and wanted to assess the overhead this might introduce.

My response was “yes, a constraint it only evaluated when the columns(s) associated with the constraint are modified”… but then I had to think about how to prove this. After a minute or two I came up with the following.

Let’s take a table with two columns and insert a single row:

SQL> CREATE TABLE chk_test
  2     (col_1     NUMBER(2) NOT NULL
  3     ,col_2     NUMBER(2) NOT NULL)
  4  /

Table created.

SQL> INSERT INTO chk_test
  2  VALUES (1, -1)
  3  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

Onto this table we’ll add a CHECK constraint such that COL_2 must be greater than 0. However, because our table already has a value in COL_2 that violates this constraint, we’ll create the constraint in an ENABLE NOVALIDATE state, meaning that existing values do not need to abide by it but any new data changes must:

SQL> ALTER TABLE chk_test
  2     ADD CONSTRAINT chk_test_ch1
  3     CHECK (col_2 > 0)
  4     ENABLE NOVALIDATE
  5  /

Table altered.

We can verify the constraint is working by trying to update COL_2:

SQL> UPDATE chk_test
  2  SET col_2 = 0
  3  /
UPDATE chk_test
*
ERROR at line 1:
ORA-02290: check constraint (DEVELOPER.CHK_TEST_CH1) violated

However, we are allowed to modify COL_1 even though the existing value of COL_2 violates the constraint:

SQL> UPDATE chk_test
  2  SET col_1 = 0
  3  /

1 row updated.

So, a change to the row that did not modify COL_2 did not trigger the evaluation of the CHECK constraint… proof that CHECK constraints are only evaluated when the column(s) they relate to are manipulated.