Referential Integrity

What is referential integrity?

• Referential integrity means that each row in a dependent table must have a foreign key that is equal to the primary key in the parent table.
• CREATE TABLE statements can be coded so that DB2 enforces referential integrity automatically.
• When an SQL statement fails because it violates referential constraints, DB2 returns a SQLCODE in the -500 range (for example -530, -532, -536).
• It guarantees that relationships between tables never point to missing rows.

Parent, dependent and delete rules

• Parent table: holds the primary key (e.g. CUSTOMER). Dependent table: holds the foreign key (e.g. INVOICE).
• On INSERT into the dependent table, the foreign key must match an existing parent key.
• On UPDATE, a parent key cannot be changed while dependent rows still refer to it.
• On DELETE from the parent table, the delete rule decides what happens - there are three options:

• DELETE with CASCADE :- deleting a parent row automatically deletes the related dependent rows.
• DELETE with RESTRICT :- deleting a parent row is rejected while dependent rows exist (SQLCODE -532).
• DELETE with SET TO NULL :- deleting a parent row sets the foreign key of dependent rows to NULL.

Example

• Parent and dependent tables with ON DELETE CASCADE:-
  
  CREATE TABLE MM01.CUSTOMER (CUSTNO CHAR(6) NOT NULL, FNAME CHAR(20) NOT NULL, LNAME CHAR(30) NOT NULL, PRIMARY KEY (CUSTNO)); CREATE TABLE MM01.INVOICE (INVCUST CHAR(6) NOT NULL, INVNO CHAR(6) NOT NULL, INVDATE DATE NOT NULL, INVSUBT DECIMAL(9,2) NOT NULL, FOREIGN KEY CUSTNO (INVCUST) REFERENCES MM01.CUSTOMER ON DELETE CASCADE);
• The INVCUST column in each INVOICE row must equal a CUSTNO in the CUSTOMER table.
• Deleting a CUSTOMER row cascades - the related INVOICE rows are deleted too.