Data integrity refers to the accuracy and correctness of data in the database. When related changes are made to a database, the database manager maintains integrity of the data by ensuring that either all or none of the changes are made. This protects other users and programs from using inconsistent or wrong data. This type of integrity is called atomic integrity.
Data integrity is also maintained by ensuring the uniqueness of certain data in the database. For example, the SUPPLIERS table must not have duplicate supplier numbers (SUPPNO). Using this integrity rule, the database manager ensures that duplicates do not exist. This type of integrity is called entity integrity.
For consistency and integrity, when one table references values in another table, the referenced values must exist in both tables, or the reference is not valid. The database manager automatically enforces rules that you define on the tables. These rules are called referential constraints. Enforcement of referential constraints ensures the referential integrity of the data referenced.
The rule that each row in the EMPLOYEE table must represent one and only one employee is an example of entity integrity. By defining a primary key on the table, you can ensure that duplicate rows do not occur, thereby enforcing entity integrity. For example, in the following SQL statement, the column EMPNO is defined as a primary key, so a unique index is automatically created on that column. This enforces uniqueness of the data in that column.
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
SALARY DECIMAL(9,2) ,
PRIMARY KEY (EMPNO)
)
A unique constraint enables you to enforce data integrity without having to enforce entity integrity. While a primary key can ensure that each row in the EMPLOYEE table represents one and only one employee, a unique constraint can ensure that each entry in another column is unique. For example, a company has one telephone for every employee and wants to maintain a set of unique phone numbers. Its database, however, already uses an employee number as a primary key. A unique constraint can ensure that no phone numbers are repeated in the table. Also, if the phone number consists of several columns (area code, 7-digit number, extension), the unique constraint can include all those columns.
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
AREACODE CHAR(3) NOT NULL,
PHONENUM CHAR(7) NOT NULL,
PHONEEXT CHAR(4) NOT NULL,
PRIMARY KEY (EMPNO)
UNIQUE PHONE (AREACODE,PHONENUM,PHONEEXT)
)
The ALTER TABLE command can be used to add, activate, deactivate, or remove a unique constraint. Another way to remove a unique constraint is either by dropping the table or the dbspace. Although a unique index is created when the unique constraint is created, the constraint cannot be dropped by dropping the index.
The WITH CHECK OPTION clause in the CREATE VIEW statement is an example of data integrity in the maintenance of data defined by a view. See Creating a View.
Referential integrity defines the condition on a set of tables in which the existence of values in one table depends on the existence of the same values in another table. By enforcing referential constraints (referential integrity rules) that are part of the table definitions, the database manager ensures the referential integrity of the data in the tables.
Figure 92 shows examples of relationships supported by the database manager.
Figure 92. Table Relationships with Referential Integrity. T1, T2, ... are tables. Arrows point from parent tables to dependent tables.
You should be familiar with the following terms:
To ensure referential integrity in new tables, you must specify a primary key, a foreign key, and a delete rule that together define the relationship between the parent table and the dependent table. Delete rules specify what will happen to the dependent rows if the corresponding parent row is deleted. Insert and update rules are automatically defined on tables when primary keys and foreign keys are defined on those tables.
The relationship is defined when the new table is created using the CREATE TABLE statement.
You should be aware of the referential constraints of the tables you manipulate, as well as the rules for those tables. In this way you can avoid violating any referential constraints, and take appropriate action should you inadvertently do so.
In the example below, the EMPLOYEE table is the parent of the DEPARTMENT table. This relationship is established by specifying a primary key (EMPNO) on the EMPLOYEE table and a foreign key (MGRNO) on the DEPARTMENT table. This relationship specifies that every manager listed in the DEPARTMENT table is also listed in the EMPLOYEE table. The REFERENCES privilege is required on the parent table. The foreign key is nullable.
 |
To add referential integrity to existing tables, you must add a primary key, a foreign key, and a delete rule that together define the relationship between the parent table and the dependent table. Delete rules specify what will happen to the dependent rows if the corresponding parent row is deleted. Insert and update rules are implicitly defined on tables when primary keys and foreign keys are defined on those tables.
The relationship is defined using the ALTER TABLE statement.
When keys (primary or foreign) are added to an existing table, any packages that depend on the table are invalidated. When the application programs are run again, the packages will be dynamically repreprocessed. Refer to Running the Program (DB2 Server for VM) or Running the Program (DB2 Server for VSE) for more information on dynamic repreprocessing.
As in the case of new tables, you should be aware of the referential constraints of the tables you manipulate as well as the rules for those tables, in order to avoid violating any referential constraints or to take appropriate action should you inadvertently do so.
Consider the existing DEPARTMENT and PROJECT tables. The PROJECT table was created by the following CREATE TABLE statement:
 |
The following ALTER TABLE statement adds a referential constraint to the PROJECT table, thereby establishing a relationship between it and the existing DEPARTMENT table:
ALTER TABLE PROJECT
ADD FOREIGN KEY DNUM (DEPTNO)
REFERENCES DEPARTMENT ON DELETE CASCADE;
In this relationship, DEPARTMENT is the parent table and PROJECT is the dependent table. This specifies that every department that is responsible for a project is also in the DEPARTMENT table.
| Note: | The ALTER TABLE statement can also be used to defer the enforcement of referential constraints or cause the removal of referential constraints. These topics are discussed in the section Enforcing Referential Integrity. |
The ALTER TABLE statement can be used to add, drop, activate, or deactivate primary and foreign keys. Various clauses of the statement alter alter the keys and to establish relationships between tables. When the ALTER TABLE statement establishes or changes relationships, specific privileges are required on parent tables and dependent tables. Figure 93 shows the privileges that are required.
Figure 93. Privileges to Use the ALTER TABLE Statement
|
ALTER TABLE Clause | Privilege on
Parent Table | Privilege on
Dependent Table |
|---|---|---|
| Add Column | ALTER |
|
| Add Primary Key | ALTER |
|
| Add Foreign Key | REFERENCES | ALTER |
| Drop Primary Key | ALTER
REFERENCES1 | ALTER |
| Drop Foreign Key | REFERENCES | ALTER |
| Deactivate Primary Key | ALTER
REFERENCES1 | ALTER |
| Deactivate Foreign Key | REFERENCES | ALTER |
| Activate Primary Key | ALTER
REFERENCES1 | ALTER |
| Activate Foreign Key | REFERENCES | ALTER |
| Note: | The REFERENCES privilege is required only if the parent table has dependents. |
You can grant to or revoke from another user the privilege to add, drop, activate, or deactivate a relationship between a parent table and its dependent. In order to enter any of these statements, you must have the REFERENCES privilege on the parent table whenever a referential constraint is to be:
By revoking the privileges previously granted on tables in a referential structure, you can prevent the accidental removal of constraints that your applications may depend on.
Applications that currently enforce consistency and integrity of their data can be modified to let the database manager do the checking. Using the referential constraints and the integrity rules that apply to the tables containing the data, the system checks that the rules are adhered to, and thereby enforces integrity of the data. As this function can be performed by the database manager, some existing code can be removed from the application.
To maintain the consistency and integrity of the data, the database manager checks that integrity rules for insert, update, and delete operations are followed.
Applying Insert Rules: The database manager checks the implicit insert rules when a row is inserted into either the parent or a dependent table in a referential structure. When a row is inserted into a parent table, the database manager checks that the primary key remains unique and does not contain null values. When a row is inserted into a dependent table, the database manager checks each foreign key for the following:
Assuming for the moment that department D21 does not already exist in the parent table (DEPARTMENT), the following INSERT statement adds a new row to DEPARTMENT.
INSERT INTO DEPARTMENT (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT)
VALUES ('D21','ADMINISTRATION SYSTEMS','000070','D01')
| Note: | The primary key (the DEPTNO column) in the DEPARTMENT table remains unique and does not contain null values. |
Figure 94. Part of Department Table
| DEPTNO | DEPTNAME | MGRNO |
|---|---|---|
| A00 | SPIFFY COMPUTER SERVICE DIV. | 000010 |
| B01 | PLANNING | 000020 |
| C01 | INFORMATION CENTER | 000030 |
| D01 | DEVELOPMENT CENTER | ? |
| D11 | MANUFACTURING SYSTEMS | 000060 |
| E11 | OPERATIONS | 000090 |
| D21 | ADMINISTRATION SYSTEMS | 000070 |
Assuming for the moment that project IF2000 does not already exist in the dependent table (PROJECT), the following INSERT statement adds a new row with DEPTNO = C01 to PROJECT. This value for DEPTNO must exist in the parent (DEPARTMENT) table.
INSERT INTO PROJECT (PROJNO,PROJNAME,DEPTNO,RESPEMP,PRSTAFF)
VALUES ('IF2000','USER EDUCATION','C01','000030',1.00)
Applying Update Rules: When a key value is updated, the database manager checks the implicit update rules. A key value may be updated when a parent row (primary key) or a dependent row (foreign key) is updated. If the primary key is updated due to updates made to the parent table, the database manager checks that the updated primary key is unique and is not null. All rows in the dependent table that reference the primary key must first be deleted or updated, or an error will occur. This ensures that the dependent table is not referencing an "old" primary key.
If foreign keys are updated, the database manager checks that each updated foreign key has either a matching primary key in the corresponding parent table, or that the updated foreign key is a null key. A foreign key is null when one or more of its column values are null.
Notes:
UPDATE EMPLOYEE
SET SALARY = 65000.00
WHERE LASTNAME = 'SMITH' AND EXISTS
(SELECT * FROM NAME
WHERE LASTNAME = 'SMITH')
The following operations change the DEPTNO B01 to F01 in the DEPARTMENT table. Since DEPTNO is a primary key in the parent table, the foreign key with DEPTNO equal to B01 must also be changed in the dependent table (PROJECT).
INSERT INTO DEPARTMENT (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT)
VALUES ('F01','PLANNING','000020'',A00')
UPDATE PROJECT SET DEPTNO = 'F01' WHERE DEPTNO = 'B01'
DELETE FROM DEPARTMENT WHERE DEPTNO = 'B01'
The example below changes the DEPTNO A00 to D11 for the ADMIN SERVICES project in the PROJECT table. Since DEPTNO is a primary key in the parent table, the database manager ensures that DEPTNO D11 in the dependent table (PROJECT) also exists in the parent table (DEPARTMENT).
UPDATE PROJECT SET DEPTNO = 'D11' WHERE PROJNAME = 'ADMIN SERVICES'
Applying Delete Rules: The database manager does not do any checking when data is deleted from dependent tables. The delete rule in a referential constraint clause defines what action should be taken by the database manager when a parent row is deleted. The delete rules are:
In the following example, the NAME table must not be a descendent of the EMPLOYEE table:
DELETE FROM EMPLOYEE
WHERE LASTNAME = 'SMITH' AND EXISTS
(SELECT * FROM NAME
WHERE LASTNAME = 'SMITH')
In the example below, the row with EMPNO equal to 000050 is deleted from the EMPLOYEE table:
DELETE FROM EMPLOYEE WHERE LASTNAME = 'GEYER'
Because the EMPLOYEE table is a parent table and the delete rule is SET NULL in the relationship that exists between the EMPLOYEE table and the DEPARTMENT table, the database manager sets MGRNO equal 000050 to null in the DEPARTMENT table. Also, because the EMPLOYEE table is a parent table and the delete rule is SET NULL in the relationship that exists between the EMPLOYEE table and the PROJECT table, the database manager sets RESEMP equal 000050 to null in the PROJECT table. (Refer to Figure 95 for more information.)
In the example below, the row with DEPTNO equal to D01 is deleted from the DEPARTMENT table:
DELETE FROM DEPARTMENT WHERE DEPTNAME = 'DEVELOPMENT CENTER'
Because the DEPARTMENT table is a parent table and the CASCADE rule was set in the relationship that exists between the DEPARTMENT table and the PROJECT table, the row with DEPTNO D01 is also deleted from the PROJECT table.
When INSERT, UPDATE, and DELETE statements are issued against tables in a referential structure, the database manager generates internal SQL statements, which it uses to ensure the consistency and integrity of the data in the tables. The number of rows affected, the cost of processing the INSERT, UPDATE, DELETE, and the internally generated statements are returned in the SQLERRD fields in the SQLCA. The SQLERRD(3) gives the number of rows that were processed successfully. Upon successful completion of the DELETE statement, SQLERRD(5) contains the number of dependent rows that were successfully deleted or set to null. For other data-manipulating language (DML) statements, SQLERRD(5) is set to zero. The relative cost of processing all the statements is given in the SQLERRD(4) field.
Additional information on internally generated statements can be found in tables updated by the EXPLAIN statement. (This statement is discussed in the DB2 Server for VSE & VM SQL Reference manual.) To determine this information, enter the EXPLAIN statement for the INSERT, UPDATE, or DELETE statement.
Referential constraints may be enforced as soon as they are defined, or their enforcement may be deferred. If the constraints are enforced as soon as they are defined, the insert, update, and delete integrity rules are enforced immediately when the INSERT, UPDATE, and DELETE statements are issued.
To defer the enforcement of a constraint is to render the constraint inactive so that it is not immediately enforced when the INSERT, UPDATE, and DELETE statements are issued. This is done by deactivating either the primary key, the dependent foreign key(s), or the foreign key(s). If any of these keys are deactivated, both the parent and the dependent tables become inactive and unavailable for data manipulation statements to general users (that is, other than the DBA and the owner of the tables). However, these tables are available for data definition statements.
When a primary key is deactivated, all active dependent foreign keys are implicitly deactivated, and the primary key index is dropped from the parent table. Both parent and dependent tables become inactive. A primary key cannot be implicitly deactivated.
With a table in an inactive state, only the owner of the table or a database administrator (DBA) can enter data manipulating language (DML) statements against it. No one can enter INSERT, UPDATE, and DELETE statements that cause statements to be generated against an inactive table.
When keys (either primary or foreign) are activated, the constraints are automatically verified. If they cannot be verified because of integrity problems, an error message is returned, and the tables remain unavailable for data manipulation statements entered by users other than the DBA or the owner.
When keys (either primary or foreign) are activated or deactivated, packages that depend on the table are invalidated. When the program is run again, it is dynamically repreprocessed.
In general, you would defer the enforcement of referential constraints between tables when large amounts of data are to be loaded, or when data is to be loaded in an order that violates the referential constraint at some point during the loading operation. For further information, refer to the DB2 Server for VSE & VM Database Administration manual.
The relationships among the EMPLOYEE, DEPARTMENT, and PROJECT tables are shown in Figure 95.
Figure 95. Relationships among the TABLES. Arrows point from primary keys in parent tables to foreign keys in dependent tables. Delete rules are labeled as (C) = CASCADE, (N) = SET NULL, (R) = RESTRICT.
 |
Then,
ALTER TABLE DEPARTMENT DEACTIVATE PRIMARY KEY
explicitly deactivates the primary key in DEPARTMENT, and implicitly deactivates the foreign keys DNUM in the PROJECT table and WORKNUM in the EMPLOYEE table. The DEPARTMENT, EMPLOYEE, and PROJECT tables become inactive. Therefore, only the owner of these tables or the DBA can enter data manipulation statements against the tables.
However,
ALTER TABLE DEPARTMENT DEACTIVATE FOREIGN KEY MNUM
will not affect the primary key in the EMPLOYEE table. However, both the EMPLOYEE table and the DEPARTMENT table become inactive since the foreign key affects both tables. As mentioned earlier, when tables become inactive, only the owner of the tables or the DBA can enter data manipulation statements against them.
To remove a referential constraint, you must drop the foreign key. When a table that contains foreign keys is dropped, the referential constraints associated with that table are removed. You can drop a table explicitly with the DROP TABLE statement, or implicitly with the DROP DBSPACE statement. You can also drop the foreign key with the ALTER TABLE statement, provided that you have the ALTER privilege on the dependent table and the REFERENCES privilege on the parent table. For descriptions of the above three statements, see Chapter 9, Maintaining Objects Used by a Program.
When a table that contains a primary key is dropped, the database manager drops the primary key and any foreign keys that reference the primary key and removes the referential constraints associated with those foreign keys. The ALTER TABLE statement can also be used to drop a primary key directly. To use the ALTER TABLE statement for this purpose, you must have the ALTER and REFERENCES privileges on the parent table as well as the ALTER privilege on all dependent tables.
When keys are dropped, any packages that depend on the table are invalidated. When the program is run again, it is dynamically repreprocessed. The new package no longer contains internally generated statements to enforce referential integrity.