Check constraints for column values

You can use check constraints to ensure that only values from the domain for the column or attribute are allowed.

By using check constraints, programmers can avoid developing, testing, and maintaining application code that performs these checks.

You can choose to define check constraints by using the SQL CREATE TABLE statement or ALTER TABLE statement. For example, you might want to ensure that each value in the SALARY column of the EMP table contains more than a certain minimum amount.

Db2 enforces a check constraint by applying the relevant search condition to each row that is inserted, updated, or loaded. An error occurs if the result of the search condition is false for any row.

Check constraints that insert table rows

When you use the INSERT statement or the MERGE statement to add a row to a table, Db2 automatically enforces all check constraints for that table. If the data violates any check constraint that is defined on that table, Db2 does not insert the row.

Begin general-use programming interface information.

Example 1
Assume that the NEWEMP table has the following two check constraints:
  • Employees cannot receive a commission that is greater than their salary.
  • Department numbers must be between '001' to '100,' inclusive.

Consider this INSERT statement, which adds an employee who has a salary of $65,000 and a commission of $6000: 

INSERT INTO NEWEMP
  (EMPNO, FIRSTNME, LASTNAME, DEPT, JOB, SALARY, COMM)
  VALUES ('100125', 'MARY', 'SMITH','055', 'SLS', 65000.00, 6000.00);

The INSERT statement in this example succeeds because it satisfies both constraints.

Example 2
Consider this INSERT statement: 
INSERT INTO NEWEMP
  (EMPNO, FIRSTNME, LASTNAME, DEPT, JOB, SALARY, COMM)
  VALUES ('120026', 'JOHN', 'SMITH','055', 'DES', 5000.00, 55000.00 );

The INSERT statement in this example fails because the $55,000 commission is higher than the $5,000 salary. This INSERT statement violates a check constraint on NEWEMP.

End general-use programming interface information.

Check constraints that update tables

Db2 automatically enforces all check constraints for a table when you use the UPDATE statement or the MERGE statement to change a row in the table. If the intended update violates any check constraint that is defined on that table, Db2 does not update the row.

Begin general-use programming interface information.

Example 1
Assume that the NEWEMP table has the following two check constraints:
  • Employees cannot receive a commission that is greater than their salary.
  • Department numbers must be between '001' to '100,' inclusive.

Consider this UPDATE statement: 

UPDATE NEWEMP
  SET DEPT = '011'
  WHERE FIRSTNME = 'MARY' AND LASTNAME= 'SMITH';

This update succeeds because it satisfies the constraints that are defined on the NEWEMP table.

Example 2
Consider this UPDATE statement:
UPDATE NEWEMP
  SET DEPT = '166'
  WHERE FIRSTNME = 'MARY' AND LASTNAME= 'SMITH';

This update fails because the value of DEPT is '166,' which violates the check constraint on NEWEMP that DEPT values must be between '001' and '100.'

End general-use programming interface information.