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,
- 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
Return to Main Page:
Extended Referential Integrity
Return to Issues Page
Copyright © 1998 FFE Software, Inc. All Rights Reserved WorldWide