A constraint is a rule that the database manager enforces. Four types of constraints handling are covered in this section:
A unique constraint is the rule that ensures that key values are unique within the table. Each column making up the key in a unique constraint must be defined as NOT NULL. Unique constraints are defined in the CREATE TABLE or the ALTER TABLE statement, using the PRIMARY KEY clause or the UNIQUE clause.
A table can have any number of unique constraints; however, you can only define one unique constraint as the primary key for a table. Moreover, a table cannot have more than one unique constraint on the same set of columns.
When a unique constraint is defined, the database manager creates a unique index (if needed), and designates it as either a primary or a unique system-required index. The constraint is enforced through the unique index. Once a unique constraint has been established on a column, the check for uniqueness during multiple row updates is deferred until the end of the update.
A unique constraint can also be used as the parent key in a referential constraint.
The database manager maintains referential integrity through referential constraints, which require that all values for a given attribute or table column also exist in some other table or column. For example, a referential constraint might require that every employee in the EMPLOYEE table be in a department that exists in the DEPARTMENT table. No employee can be in a department that does not exist.
You can build referential constraints into a database to ensure that referential integrity is maintained, and to allow the optimizer to exploit knowledge of these special relationships to process queries more efficiently. When planning for referential integrity, identify all of the relationships between database tables. You can identify a relationship by defining a primary key and referential constraints.
Consider the following related tables:
| DEPTNO (Primary Key) | DEPTNAME | MGRNO |
|---|---|---|
| A00 | Spiffy Computer Service Div. | 000010 |
| B01 | Planning | 000020 |
| C01 | Information Center | 000030 |
| D11 | Manufacturing Systems | 000060 |
| EMPNO (Primary Key) | FIRSTNAME | LASTNAME | WORKDEPT (Foreign Key) | PHONENO |
|---|---|---|---|---|
| 000010 | Christine | Haas | A00 | 3978 |
| 000030 | Sally | Kwan | C01 | 4738 |
| 000060 | Irving | Stern | D11 | 6423 |
| 000120 | Sean | O'Connell | A00 | 2167 |
| 000140 | Heather | Nicholls | C01 | 1793 |
| 000170 | Masatoshi | Yoshimura | D11 | 2890 |
Many of the following concepts, useful for understanding referential integrity, are discussed in relation to these tables.
A unique key is a column or a set of columns where no values in a row are duplicated in any other row. You can define one unique key as the primary key for the table. The unique key may also be known as a parent key when referenced by a foreign key.
A primary key is a unique key that is part of the definition of the table. Each table can have only one primary key. In the preceding tables, DEPTNO and EMPNO are the primary keys of the DEPARTMENT and EMPLOYEE tables, respectively.
A foreign key is a column or a set of columns in a table that refer to a unique key or the primary key of the same or another table. A foreign key is used to establish a relationship with a unique key or the primary key to enforce referential integrity among tables. The column WORKDEPT in the EMPLOYEE table is a foreign key because it refers to the primary key, DEPTNO, in the DEPARTMENT table.
A composite key is a key that has more than one column. Both primary and foreign keys can be composite keys. For example, if departments were uniquely identified by the combination of division number and department number, two columns would be needed to create the key for the DEPARTMENT table.
A parent key is a primary key or a unique key of a referential constraint. The primary key constraint is the default parent key of a referential constraint when a set of parent key columns is not specified.
A parent table is a table containing a parent key that is related to at least one foreign key in the same or another table. A table can be a parent in an arbitrary number of relationships. For example, the DEPARTMENT table, which has a primary key of DEPTNO, is a parent of the EMPLOYEE table, which contains the foreign key WORKDEPT.
A parent row is a row of a parent table whose parent key value matches at least one foreign key value in a dependent table. A row in a parent table is not necessarily a parent row. The fourth row (D11) of the DEPARTMENT table is the parent row of the third and sixth rows in the EMPLOYEE table. The second row (B01) of the DEPARTMENT table is not the parent of any other row.
A dependent table is a table containing one or more foreign keys. A dependent table can also be a parent table. A table can be a dependent table in an arbitrary number of relationships. The EMPLOYEE table contains the foreign key WORKDEPT, and is dependent on the DEPARTMENT table, whose primary key is DEPTNO.
A dependent row is a row of a dependent table that has a non-NULL foreign key value that matches a parent key value. The foreign key value represents a reference from the dependent row to the parent row. Since foreign keys can accept NULL values, a row in a dependent table is not necessarily a dependent row.
A table is a descendent of a table if it is a dependent table, or if it is a descendent of a dependent table. A descendent table contains a foreign key that can be traced back to the parent key of some table.
A referential cycle is a path that connects a table to itself. When a table is directly connected to itself, it is a self-referencing table. If the EMPLOYEE table had another column called MGRID that contains the EMPNO of each employee's manager, the EMPLOYEE table would be a self-referencing table. MGRID would be a foreign key for the EMPLOYEE table.
A self-referencing table is both a parent and a dependent in the same relationship. A self-referencing row is a row that is both a parent and a dependent of itself. The constraint that exists in this situation is called a self-referencing constraint. For example, if the value for the foreign key in a row of a self-referencing table matches the value of the unique key in that row, the row is self-referencing.
A referential constraint is an assertion that non-NULL values of a designated foreign key are valid only if they also appear as values for a unique key of a designated table. The purpose of referential constraints is to guarantee that database relationships are maintained, and that data entry rules are followed.
Enforcement of referential constraints has special implications for some SQL operations that depend on whether the table is a parent or a dependent. This section describes the effects of maintaining referential integrity on SQL INSERT, DELETE, UPDATE, and DROP operations.
DB2 does not automatically enforce referential constraints across systems. Consequently, if you want to enforce referential constraints across systems, your applications must contain the necessary logic.
The following topics are discussed:
You can insert a row at any time into a parent table without any action being taken in dependent tables. However, you cannot insert a row into a dependent table, unless there is a row in the parent table with a parent key value equal to the foreign key value of the row that is being inserted, unless that foreign key value is NULL. The value of a composite foreign key is NULL if any component of the value is NULL.
This rule is implicit when a foreign key is specified.
If you try to insert a row into a table that has referential constraints, the INSERT operation is not allowed if any of the non-NULL foreign key values are not present in the parent key. If the INSERT operation fails for one row during an attempt to insert more than one row, none of the rows are inserted.
When you delete a row from a parent table, DB2 checks if there are any dependent rows in dependent tables with matching foreign key values. If any dependent rows are found, several actions are possible. You can determine which action will be taken by specifying a delete rule when you create the dependent table.
The delete rules for a dependent table (the table containing the foreign key) when a primary key is deleted are:
Deleting the parent row first automatically deletes the dependent rows referencing a primary key. The dependent rows do not need to be deleted first. If some of these dependent rows have dependents of their own, the delete rule for those relationships is applied. DB2 manages cascading deletions.
If no delete rule is explicitly defined when the table is created, the NO ACTION rule applies.
Any table that can be involved in a delete operation is said to be delete-connected. The following restrictions apply to delete-connected relationships.
You can, at any time, delete rows from a dependent table without taking any action against the parent table. In the department-employee relationship, for example, an employee could retire and have his row deleted from the employee table with no effect on the department table. (In the reverse relationship of employee-department, the department manager ID is a foreign key referring to the parent key of the employee table. If a manager retires, there is an effect on the department table.)
DB2 prevents the update of a unique key for a parent row. When you update a foreign key in a dependent table, and that foreign key is not NULL, it must match some value of the parent key for the parent table of the relationship. If any referential constraint is violated by an UPDATE operation, an error occurs and no rows are updated.
When a value in a column of the parent key is updated:
To update the value of a parent key that is in a parent row, you must first remove the relationship to any child rows in the dependent tables by either:
If there is no dependency to the key value in the row, the row is no longer a parent in a referential relationship and can be updated.
If part of a foreign key is being updated and no part of the foreign key value is NULL, the new value of the foreign key must appear as a unique key value in the parent table. If there is no foreign key dependent on a given unique key; that is, the row containing the unique key is not a parent row, part of the unique key may be updated. However, no more than one row can be selected for update in this case, because you are working with a unique key, and duplicate rows are not allowed.
Business rules identified in your design can be enforced through table check constraints. Table check constraints specify search conditions that are applied to each row of a table. These constraints are automatically activated when an update or insert statement is applied against the table. They are defined through the CREATE TABLE or the ALTER TABLE statement.
A table check constraint can be used for validation. For example, values for a department number must be within the range of 10 to 100; the job title of an employee can only be "Sales", "Manager", or "Clerk"; or an employee who has been with the company for more than 8 years must earn more than $40,500.
Refer to the Data Movement Utilities Guide and Reference for information about the impact of table check constraints on the IMPORT and LOAD commands.
A trigger is a defined set of actions that are performed whenever a delete, insert, or update operation is carried out against a specified table. Triggers can be defined to help support business rules. Triggers can also be used to automatically update summary or audit data. Because triggers are stored in the database, you do not have to code the actions in every application program. The trigger is coded once, stored in the database, and automatically called by DB2, as required, when an application uses the database. This ensures that the business rules related to the data are always enforced. If a business rule changes, only the triggers need to be modified.
A user-defined function (UDF) can be called within a triggered SQL statement. This allows the triggered action to perform a non-SQL operation when the trigger is fired. For example, e-mail can be sent as an alert mechanism. For more information about triggers, see Creating a Trigger and refer to the Application Development Guide.