Adding and using check constraints

 

A check constraint ensures the validity of data during insert and update operations by limiting the allowed values in a column or group of columns.

Use the SQL CREATE TABLE and ALTER TABLE statements to add or drop check constraints.

In this example, the following statement creates a table with three columns and a check constraint over COL2 that limits the values allowed in that column to positive integers:

CREATE TABLE T1 (COL1 INT, COL2 INT CHECK (COL2>0), COL3 INT)

Given this table, the following statement:

INSERT INTO T1 VALUES (-1, -1, -1)

fails because the value to be inserted into COL2 does not meet the check constraint; that is, -1 is not greater than 0.

The following statement is successful:

INSERT INTO T1 VALUES (1, 1, 1)

Once that row is inserted, the following statement fails:

ALTER TABLE T1 ADD CONSTRAINT C1 CHECK (COL1=1 AND COL1<COL2)

This ALTER TABLE statement attempts to add a second check constraint that limits the value allowed in COL1 to 1 and also effectively rules that values in COL2 be greater than 1. This constraint is not allowed because the second part of the constraint is not met by the existing data (the value of '1' in COL2 is not less than the value of '1' in COL1).

 

Parent topic:

Constraints

 

Related reference

ALTER TABLE
CREATE TABLE