Constraints in DB2

The DB2 DBMS offers a useful suite of methods to ensure data consistency.

Primary key constraints forbid duplicate values in one or more columns of a table. Foreign key constraints ensure consistency of tuple (row) references across tables. Table check constraints are conditions that are defined as part of the table definition that restrict the values used in one or more columns. Unfortunately, general SQL assertions (that we learned in the class) are not supported in DB2.

Keys

A primary key is a set of attributes whose values should be unique within a table. A table cannot have more than one set of attributes specified as the primary key, and the columns of a primary key cannot contain null values. In DB2 primary keys must be specificed as being not null (see below). Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements.

Non-primary candidate keys can be specified using unique constraints. The attributes for the unique constraint should also be specified as being not null.

CREATE TABLE Order ( \
      OrderNum          INTEGER NOT NULL, \
      CustNum           INTEGER, \
      TotalPrice        INTEGER, \
      PRIMARY KEY(OrderNum) \)

CREATE TABLE Product ( \
      ProdNum           INTEGER NOT NULL, \
      ProdLine          CHAR(1) NOT NULL, \
      Name              CHAR(100) NOT NULL, \
      UnitPrice         DECIMAL(6,2), \
      PRIMARY KEY(ProdNum, ProdLine), \
      UNIQUE(Name) \)

Foreign Keys

A foreign key is a referential integrity constraint. A table can have zero or more foreign keys. The value of the composite foreign key is null if any component of the value is null. Foreign keys are optional and can be defined in CREATE TABLE statements or ALTER TABLE statements.

CREATE TABLE OrderContent ( \
      OrderNum    INTEGER NOT NULL, \
      ProdNum           INTEGER NOT NULL, \
      ProdLine    CHAR(1) NOT NULL, \
      Quantity    INTEGER DEFAULT 1, \
      PRIMARY KEY(OrderNum,ProdNum,ProdLine), \
---- When an order is deleted, we want every ordercontent that
-- references it to also be deleted. Hence, we specify that
-- order deletions should be cascaded
--
      FOREIGN KEY(OrderNum) REFERENCES Order \
            ON DELETE CASCADE, \
---- We don't want to permit the deletion of a product while there
-- are any outstanding orders for the product.
-- The default foreign key semantics is to reject deletion of an
-- product tuple if there is a reference to it in any ordercontent tuple.
--
      FOREIGN KEY(ProdNum, ProdLine) REFERENCES Product \)

Table Check Constraints

Table check constraints specify conditions that are evaluated for each tuple of a table. You can specify check constraints on individual columns. You can add them by using the CREATE or ALTER TABLE statements.

The following statement creates a table with the constraints below.

· The values of the department number must lie in the range 10 to 100

· The job of an employee can only be one of the following: "Sales", "Mgr", or "Clerk"

· Every employee who was hired prior to 1986 must make more than $40,500.

CREATE TABLE EMP
      (ID         SMALLINT NOT NULL,
      NAME        VARCHAR(9),
      DEPT        SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
      JOB         CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),
      HIREDATE    DATE,
      SALARY      DECIMAL(7,2),
      COMM        DECIMAL(7,2),
      PRIMARY KEY (ID),
      CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )

A constraint is violated if the condition evaluates to false on a tuple. Note that in SQL many predicates when applied to a null value, evaluate to unknown, not false. For example, if DEPT is NULL for an inserted tuple, the insert proceeds without error, even though values for DEPT should be between 10 and 100 as defined in the constraint.

The following statement adds a constraint to the EMPLOYEE table named COMP that an employee's total compensation must exceed $15,000.

ALTER TABLE EMP
ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)

The existing tuples in the table will be checked to ensure that they do not violate the new constraint. If they do, the constraint will not be added.

Note that in DB2, the check condition must be some test that can be evaluated by examining a single row of the table to which the check constraint is attached. In other words, the check constraint is like a WHERE clause that refers only to the columns of a single table, with no subqueries. This is a significant restriction, but allows the check condition to be quickly evaluated whenever a row is inserted or updated.

For additional information on DB2 constraints, please read the DB2 reference page on constraints.

This page was originally written for CS343 at Univerisity of Toronto and subsequently modified by Junghoo "John" Cho for CS143 at UCLA in winter 2003.