Constraints in DB2

Constraints and Triggers

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. Triggers allow you to define a set of actions that are executed, or triggered, by a delete, insert, or update operation on a specified table.

Keys

A primary key is a minimal key specified as being primary. 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.

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.

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 null, 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.

Triggers

A trigger defines a set of actions that is activated by an operation that modifies the data in a specified base table.

You can use triggers to perform validation of input data, to automatically generate a value for a newly inserted tuple, to read from other tables for cross-referencing purposes, or to write to other tables for audit-trail purposes.

Example

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

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

CREATE TABLE CSC343H.OrderContent ( \
	OrderNum	INTEGER NOT NULL, \
	ProdNum		INTEGER NOT NULL, \
	ProdLine	CHAR(1) NOT NULL, \
	Quantity	INTEGER NOT NULL 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 CSC343H.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 CSC343H.Product \
)

-- disallows the insertion of too many products in a given order

CREATE TRIGGER CSC343H.Ordersize \
	NO CASCADE BEFORE INSERT ON CSC343H.OrderContent \
        REFERENCING NEW AS N \
        FOR EACH ROW MODE DB2SQL \
	WHEN ( (SELECT count(ORDERNUM) \
		   FROM CSC343H.ORDERCONTENT \
		   WHERE OrderNum = N.OrderNum ) > 9 ) \
	BEGIN ATOMIC \
		SIGNAL SQLSTATE '75001' \
		('There cannot be more than 10 products per order'); \
	END

-- update order price upon insert of a new product into the order

CREATE TRIGGER csc343h.OrderTotal \
	AFTER INSERT ON csc343h.OrderContent \
        REFERENCING NEW AS N \
        FOR EACH ROW MODE DB2SQL \
	BEGIN ATOMIC \
		UPDATE csc343h.order SET TotalPrice = \
		( select sum(UnitPrice*Quantity) \
		  from csc343h.OrderContent C, csc343h.Product P \
		  where C.ProdNum = P.ProdNum and \
			C.ProdLine = P.ProdLine and \
			C.OrderNum = N.OrderNum ) \
		WHERE N.OrderNum = OrderNum; \
	END



Additional information on triggers is available from IBM here.