A constraint is a rule that the database manager enforces. Four types of constraint handling are covered in this section:
A unique constraint is the rule that the values of a key are valid only if they 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 statements 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. Also, 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 (if needed) a unique index and designates it as either a primary or unique system-required index. The enforcement of the constraint is 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.
Referential integrity lets you define required relationships between and within tables. The database manager maintains these relationships which are expressed as referential constraints and require that all values of a given attribute or column of a table also exist in some other table or column. For example, a typical referential constraint might require that every employee in the EMPLOYEE table must 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 effectively. When planning for referential integrity, identify the relationships to be established between database tables. You can identify a relationship by defining a primary key and referential constraints.
The following two tables are related, and show some of the relationships to
be discussed:
| 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 |
The following definitions are useful for understanding referential integrity.
A unique key is a set of columns where no two values are duplicated in any other row. You may define one unique key for each table as the primary key. 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 only have one primary key. In the preceding tables DEPTNO and EMPNO are the primary keys of the DEPARTMENT and EMPLOYEE tables.
A foreign key is a column or set of columns in a table that refer to a unique key or primary key of the same or another table. A foreign key is used to establish a relationship with a unique key or 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, column DEPTNO, in the DEPARTMENT table.
A composite key is a key that has more than one column. Unique 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 comprise the key to the DEPARTMENT table.
A parent key is a primary key or unique key of a referential constraint.
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 rows.
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 in an arbitrary number of relationships. For example, the EMPLOYEE table contains the foreign key WORKDEPT, which is dependent on the DEPARTMENT table that has a primary key.
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 may 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 has another column called MGRID that contains the EMPNO of each employee's manager, then the EMPLOYEE table would be a self-referencing table. MGRID would be a foreign key for the EMPLOYEE table.
A referential constraint is an assertion that non-null values of a designated foreign key are valid only if they also appear as values of a unique key of a designated table. The purpose of referential constraints is to guarantee that database relationships are maintained and data entry rules are followed.
A self-referencing table is both a parent and a dependent in the same relationship. A self-referencing row is a row that is 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 of the foreign key in a row of a self-referencing table matches the value of the unique key in that row, then the row is self-referencing.
The following additional topic is discussed within this section:
Enforcement of referential constraints has special implications for some SQL operations that depend on whether the table is a parent or a dependent. This segment describes the effects of referential integrity on the SQL INSERT, DELETE, UPDATE, and DROP operations.
The database manager does not automatically enforce referential constraints across systems. As a result, if you wish to enforce referential constraints across systems, your application programs must contain the necessary logic.
The following referential integrity rules are discussed:
You can insert a row at any time into a parent table without any action being taken in the dependent table. 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 the 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.
When 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, all rows in the statement are backed out.
When you delete a row from a parent table, the database manager checks if there are any dependent rows in the dependent table with matching foreign key values. If any dependent rows are found, several actions could be taken. 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:
See the SQL Reference for an example where this is different from NO ACTION.
Deleting the parent row first would automatically delete the dependent rows referencing a primary key. Therefore, the dependent rows would not need to be deleted first. If some of these dependent rows have dependents of their own, the delete rule for those relationships will be applied. In other words, the database manager can handle cascading deletions.
If no delete rule is explicitly defined when the table is created, the NO ACTION rule will be applied.
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 on the parent table. For example, in the department-employee relationship, an employee could retire and have his row deleted from the employee table with no effect on the department table. (Ignore, for the moment, the reverse relationship of employee-department, in which 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.)
The database manager prevents the update of a unique key of a parent row. When you update a foreign key in a dependent table, and the foreign key is not null, it must match some value of the parent key of 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:
When 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, then part of the unique key may be updated. However, no more than one row can be selected for updating in this case, because you are working with a unique key where duplicate rows are not allowed.
Business rules identified within your design can be enforced through table check constraints. Table check constraints specify search conditions that are enforced for each row of a table. These constraints are automatically activated when an update or insert statement runs against the table. They are defined when using either CREATE TABLE or ALTER TABLE statements.
A table check constraint can be used for validation. For example: the values of a department number must lie within the range 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.
See Chapter 6. "Utilities for Moving Data" for more information on the impact of table check constraints on the IMPORT and LOAD commands.
A trigger is a defined set of actions that are executed when a delete, insert, or update operation is carried out against a specified table. To help support business rules, triggers can be defined. Triggers are stored in the database, therefore application development is faster because 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 the database manager, 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 does change, only a modification to the trigger is required instead of to each application program.
For example, triggers can be used to automatically update summary or audit data.
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 on triggers, see "Creating a Trigger" and the Embedded SQL Programming Guide manual.