Constraints

A constraint is a rule enforced by the database manager to limit the values that can be inserted, deleted, or updated in a table.

DB2® for i supports the following constraints:

• Unique constraints

  A unique constraint is the rule that the values of the key are valid only if they are unique. You can create a unique constraint using the CREATE TABLE or ALTER TABLE statement. Although the CREATE INDEX statement can create a unique index that also guarantees uniqueness, such an index is not a constraint.

  Unique constraints are enforced during the execution of INSERT and UPDATE statements. A PRIMARY KEY constraint is a form of the UNIQUE constraint. The difference is that a PRIMARY KEY cannot contain any nullable columns.

• Referential constraints

  A referential constraint is the rule that the values of the foreign key are valid only if one of the following conditions is met:

  • They appear as values of a parent key.
  • Some component of the foreign key is null.

  Referential constraints are enforced during the execution of INSERT, UPDATE, and DELETE statements.

• Check constraints

  A check constraint is the rule that limits the values allowed in a column or group of columns. You can create a check constraint using the CREATE TABLE or ALTER TABLE statement. Check constraints are enforced during the execution of INSERT and UPDATE statements. To satisfy the constraint, each row of data inserted or updated in the table must make the specified condition either TRUE or unknown (because of a null value).