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