Extended Referential Integrity (2)

Establishing Self-referencing and Cross-referencing Relationships

Normally, there is just one guideline for defining and adding data to foreign key and primary key tables - create or add the primary key first. In fact, all data can be added to the primary key table, in any order, before any data is added to the foreign key table, even before the foreign key table is created. For self-referencing and cross-referencing foreign keys, the situation is more complicated. Often it is a chicken or egg problem - which comes first?

Defining Cross-referencing Relationships

Creating self-referencing foreign keys causes no problem because only one table is involved. With cross-referencing, two or more tables are involved so the order of table creation becomes an issue. Normally, the primary key table is created first, then the foreign key table. This enables the DBMS to check that the foreign key is compatible with the primary key (at least that the number of columns in the key are the same, often that the corresponding columns are type-compatible).

For cross-referencing foreign keys, both tables are primary key tables to each other so one can't be added before the other. FirstSQL allows a foreign key to reference a primary key table that has not yet been created. This could be described as 'dangling' foreign keys. The primary key table is created later, and the DBMS performs checks on the dangling foreign key at that time.

Notice that these capabilities are useful even when foreign keys are not cross-referencing.

Adding Data to Cross-referencing and Self-referencing Tables

The basic tenet of referential integrity is that a foreign key is only valid when it matches an existing primary key value (unless the foreign key contains Null values). Therefore, the primary key row must be added before the matching foreign key row. This is problematic for cross-referencing (do we add the department to the database first or the department manager?) and difficult for self-referencing especially when the self-reference is circular.

The solution is the foreign key must be defined to allow Null values, so that,

The foreign key row can be added with a Null value for the referencing foreign key,
then, the primary key row is added,
finally, the value of the foreign key is changed from Null to the new primary key value.

Next section Changing or Removing Self-referencing and Cross-referencing Relationships.

Return to Main Page: Extended Referential Integrity Return to Issues Page