How DB2 Provides for Referential Integrity

The REFERENTIAL INTEGRITY feature of DB2 helps us maintain the relationships between the rows in related tables.

Referential Integrity feature can be used to enforce the integrity of the relationships between a parent table and a dependent table.
To enable this feature , the DBA adds clauses to the CREATE TABLE statements that establish the referential constraints between the parent and the dependent   table.

CREATE TABLE MM01.DEPARTMENT
      (DEPTNO          CHAR(6)    NOT NULL,
       DEPTNAME        CHAR(20)   NOT NULL,
       PRIMARY KEY (DEPTNO))
IN DATABASE DB1

CREATE TABLE MM01.STAFF
      (STAFFNO         CHAR(6)    NOT NULL,
       FNAME           CHAR(20)   NOT NULL,
       LNAME           CHAR(20)   NOT NULL,
       STDEPT          CHAR(6)    NOT NULL,
       PRIMARY KEY (STAFFNO),
       FOREIGN KEY DEPTNO (STDEPT)
       REFERENCES MM01.DEPARTMENT
       ON DELETE CASCADE)
IN DATABASE DB1

In above example, DEPTNO is primary key for DEPARTMENT table and STDEPT is the foreign key in the STAFF table that refers to the DEPTNO in DEPARTMENT table. The ON DELETE option for this foreign key is CASCADE, which means that any deletions of parent rows should be cascaded to the related rows in the STAFF table.
We cant add a row to a dependent table unless it has a foreign key that matches a value in the parent table. And we cant change the primary key of a row in parent table that has a matching foreign keys in a dependent table.

What happens when we try to delete a row in a parent table ? That depends on the setting of ON DELETE option. if the RESTRICT option is on, we cant delete a row in parent table that has matching row in the dependent table. If the CASCADE option is on, then the row in the parent table is deleted along with all the matching rows in the dependent table. And if the SET TO NULL option is on , the row in the parent table is deleted and the foreign keys in all related records in the  dependent table are set to NULL values.