Column Check Constraints

Column check constraints can be set to limit the values to which a column can be set. This provides a

simple way to validate data without affecting your application code.

Example:

Current valid C_JOB are 001, 003, 004, and 005

Use of application code:

Program(s) would have a validation check for each of valid job codes prior to inserts and update.

Use of check constraints:

Program(s) would not need to do any validation checking prior to inserts and updates. When the program

attempt to update or insert a row where C_JOB is not one of the valid values, DB2 will pass a SQL

code of –545 (The requested operation is not allowed because a row does not satisfy the check constraint).

CREATE TABLE TCO88001

(M_EMPLOYEE CHAR(30) NOT NULL

,N_EMPLOYEE CHAR(10) NOT NULL

,C_JOB CHAR(03) NOT NULL

,PRIMARY KEY

(N_EMPLOYEE)

,CONSTRAINT C_JOB CHECK (C_JOB IN (‘001, ‘003’, ‘004’, ‘005’)))

New value for C_JOB is to be added to the system: 010

Use of application code:

Each program, which does any updating of C_JOB or inserting of a row into the table, would need to

be changed to accommodate the new code.

Use of check constraints:

No program changes are required:

ALTER TABLE TCO88001

ADD CONSTRAINT C_JOB CHECK (C_JOB IN (‘001’, ‘003’, ‘004’, ‘005’, ‘010’))

Restriction on check constraints:

It can refer only to columns of the table (i.e. can not refer to columns of another table)
It can only be up to 3800 bytes
It must not contain any of the following