Extended Referential Integrity (3)

Changing or Removing Self-referencing and Cross-referencing Relationships

This is kind of the reverse of adding relationships - updating or deleting primary keys and dropping primary key tables. Normally, this can be accomplished by changing or removing the foreign key row or table first. As in the previous section, this can be problem for cross-referencing and for self-referencing when the self-reference is circular.

Updating or Deleting Primary Key Data

This functionality is provided as part of referential integrity by a DBMS. These are referential integrity 'effects'. Referential integrity effects are often specified separately for update and for delete. The 3 forms of referential integrity effects when a primary key is changed or purged (and referencing foreign keys exist) are:

Restricts. This is the default effect and the only choice for some DBMSs. It indicates that the primary key cannot be changed or purged when referencing foreign keys exist. When this effect is specified, the referencing foreign keys must first be:
- Changed to another existing primary key value,
- set to Null (if Null values are allowed) or,
- deleted (this is not possible when references are circular).
Nulls (also called Marks). This indicates that any existing foreign keys are set to Null when the referenced primary key is changed or removed. This effect works for all cases of cross-referencing and self-referencing.
Cascades. This indicates that the operation on the primary key is cascaded (propagated) to all referencing foreign keys. If the primary key is updated, all referencing foreign keys are updated to the same value. If the primary key is deleted, all referencing foreign key rows are deleted. This effect works for all cases of cross-referencing and self-referencing.

Of the possible referential integrity effects, cascading produces the most complex situations. It can produce recursive cascading, wrapping operations around to multiple rows in tables. This is especially true for cascading deletes but also occurs for cascading updates when the foreign key shares columns with the primary key of the table. It is an expanding ripple effect. Expanding because primary key to foreign key relationships are often one-to-many (though not always, as when the foreign key and primary key for a table specify the same columns).

Using the Employee table as an example and assuming cascading deletes, a delete of the department manager would trigger the delete of all employees that work for the manager. The cascading delete wraps around recursively in the same table. A delete of a Vice President would delete all department managers who work for the executive and all employees in their departments. A delete of the President would cause deletion of all employees in the company. That's an interesting thought! Sort of like assistant football coaches when the head coach is fired.

Cascading updates and deletes can cause problems for DBMSs that compile their query plans. Chains of foreign key references must be checked for circular definitions to avoid infinite loops in the DBMS. Users should verify that their DBMS provides proper support before using cascading update or delete effects with cross-referencing and self-referencing referential integrity.

Dropping Primary Key Tables that are Cross-referenced

Self-referencing tables are not a problem when the table is dropped because only one table is involved. Dropping cross-referenced tables, on the other hand, requires special processing since the primary key table can't be dropped first, leaving a dangling foreign key table.

FirstSQL supports - ALTER TABLE ... DROP FOREIGN KEY ..., which removes the foreign key definition but doesn't drop the table so that the primary key table can then be dropped. This syntax is provided by FirstSQL, as well as, just a simple DDL command - DROP FOREIGN KEY ... .

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

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