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: 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


Copyright © 1998 FFE Software, Inc. All Rights Reserved WorldWide