Wednesday, January 17, 2007

Review topic: DB2, referential integrity

Referential integrity is an important concept in relational databases. It means that each row of a dependent table will have a foreign key with a value equal to the value of a primary key in the parent table. The names of the fields may and will generally be different. A foreign key comprises a column (or columns) in a dependent table that correspond to the primary key in the parent table. So even in a relational database, the concept of "hierarchy" can exist in a somewhat artificial manner.

With DB2 (2.1) the database will enforce referential integrity with the CREATE TABLE statement.

When you INSERT, every row in the parent table must have a unique primary key, and every row in any dependent table needs a foreign key corresponding to the primary key value. You can't change the primary key with an UPDATE if any foreign keys in dependent tables refer to it. The DELETE statement has CASCADE, RESTRICT, and SET TO NULL options to handle dependent rows in a manner decided by the applications designer.

The SQLCODE value for a referential integrity error is -500.

I once worked on the conversion of an IMS name-and-address database to DB2 (back in 1999). An independent consultant designed all of the tables, and referential integrity was one of his first concerns. The design took several months, which is typical in any major conversion.

(Picture, unrelated: Paw-Paw tunnel on C&O Canal, Maryland, on Potomac River)

No comments: