In the business world we quite often need to make sure certain rules are always enforced. For instance, an employee working on a project has to be on the payroll list. Or, we want certain events to happen systematically. For instance, if a salesperson makes a sale, their commission should be increased.
DB2 Universal Database offers a useful suite of methods to this end. Unique constraints is the rule that forbids duplicate values in one or more columns of a table. Referential integerity constraints ensure the data consistency across the specified 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. Triggers can be used for writing to other tables, for modifying of input values, and for the issuing alert messages.
The first section provides a conceptual overview of keys. Later, referential integerity, constraints, and triggers are explored through examples and diagrams.
A key is a set of columns that you can use to identify or access a particular row or rows.
A key composed of more than one column is called a composite key. In a table with a composite key, the ordering of the columns within the composite key is not constrained by their ordering within the table.
A unique key is defined to have no two of its values the same. The columns of a unique key cannot contain null values. The constraint is enforced by the database manager during the execution of INSERT and UPDATE statements. A table can have mulitple unique keys. Unique keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements.
A primary key is a unique key that is a part of the definition of the table. A table cannot have more than one primary key, and the columns of a primary key cannot contain null values. Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements.
A foreign key is specified in the definition of a referential 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.
A unique constraint ensures that values of a key are unique within a table. Unique constraints are optional, and you can define them using the CREATE TABLE or ALTER TABLE statements by specifying the PRIMARY KEY or UNIQUE clause. For example, you can define a unique constraint on the employee number column of a table to ensure that every employee has a unique number.
By defining unique constraints and foreign keys you can define relationships between tables and consequently enforce certain business rules. The combination of unique key and foreign key constraints is commonly referred to as referential integrity constraints. A unique constraint referenced by a foreign key is called a parent key. A foreign key refers to or is related to a specific parent key. For example, a rule might state that every employee (EMPLOYEE table) must belong to an existing department (DEPARTMENT table). So, we define department number in the EMPLOYEE table as foreign key, and department number in the DEPARTMENT table as the primary key. The following diagram provides a visual description of referential integrity constraints.
Figure 4. Foreign and Primary Constraints Define Relationships and Protect Data
|
Table check constraints specify conditions that are evaluated for each row 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 following constraints:
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 only if the condition evaluates to false. For example, if DEPT is NULL for an inserted row, 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 rows in the table will be checked to ensure that they do not violate the new constraint. You can defer this checking by using the SET CONSTRAINTS statement as follows:
SET CONSTRAINTS FOR EMP OFF
ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED
First, the SET CONSTRAINTS statement is used to defer constraint checking for the table. Then one or more constraints can be added to the table without checking the constraints. Then the SET CONSTRAINTS statement is issued again to turn constraint checking back on and to perform any deferred constraint checking.
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 row, to read from other tables for cross-referencing purposes, to write to other tables for audit-trail purposes, or to support alerts through electronic mail messages. Using triggers results in faster application development, global enforcement of business rules, and easier maintenance of applications and data.
DB2 Universal Database supports several types of triggers. Triggers can be defined to be activated either before or after a DELETE, INSERT, or UPDATE operation. Each trigger includes a set of SQL statements called a triggered action that can include an optional search condition.
After triggers can be further defined to perform the triggered action either for each row or once for the statement, while before triggers always perform the triggered action for each row.
Use a trigger before an INSERT, UPDATE, or DELETE statement to check for certain conditions before performing a triggering operation or to change the input values before they are stored in the table. Use an after trigger to propagate values as necessary or perform other tasks, such as sending a message, that may be required as a part of the trigger operation.
The following example illustrates a use of before and after triggers. Consider an application that records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY defined as:
CREATE TABLE CURRENTQUOTE
(SYMBOL VARCHAR(10),
QUOTE DECIMAL(5,2),
STATUS VARCHAR(9))
CREATE TABLE QUOTEHISTORY
(SYMBOL VARCHAR(10),
QUOTE DECIMAL(5,2),
TIMESTAMP TIMESTAMP)
When the QUOTE column of CURRENTQUOTE is updated using a statement such as:
UPDATE CURRENTQUOTE
SET QUOTE = 68.5
WHERE SYMBOL = 'IBM'
The STATUS column of CURRENTQUOTE should be updated to reflect whether the stock is:
This is done using the following before trigger:
(1)
CREATE TRIGGER STOCK_STATUS
NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW MODE DB2SQL
(2)
SET NEWQUOTE.STATUS =
(3)
CASE
(4)
WHEN NEWQUOTE.QUOTE >=
(SELECT MAX(QUOTE)
FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'High'
(5)
WHEN NEWQUOTE.QUOTE <=
(SELECT MIN(QUOTE)
FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'Low'
(6)
WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
THEN 'Rising'
WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
THEN 'Dropping'
WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
THEN 'Steady'
END
In addition to updating the entry in the CURRENTQUOTE table, an audit record needs to be created by copying the new quote, with a timestamp, to the QUOTEHISTORY table. This is done using the following after trigger:
(1)
CREATE TRIGGER RECORD_HISTORY
AFTER UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
(2)
INSERT INTO QUOTEHISTORY
VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
END
CURRENT TIMESTAMP is a special register containing the timestamp. A list and explanation is provided in Special Registers.