Re: Updating Foreign Keys in Child Tables (ON DELETE CASCADE)

> Kevin Kinney <kvkinne_at_envc.sandia.gov> writes:
> I am trying to update a primary key in one table which flows down into many other
> tables, sometimes two or three levels. Is there a command similiar to ON DELETE
> CASCADE, maybe called ON UPDATE CASCADE, which will let me update the parent table
> and then cascade those changes to the child tables. Or is there a way to handle this
> with some SQL.
>>>>

According to standard modeling practices, it is not normal to update a primary key. This being said, the answer is NO.

Database triggers may be used for this kind of thing, but it isn't going to be pretty. Note that if you are using Forms, for example, the changed "primary key" would normally be updated in the child records, which would conflict with a row-level trigger, causing a mutation error in row level triggers, nor may row triggers change the primary or foreign key columns of a constraining table. This sticks you with some very ugly logic in a statement level trigger.

Not to belabor the point, but the need to update a "primary key" column usually stems from poor practices such as using an "intelligent" key value, which is based on data. Primary key columns should really be non-intelligent and arbitrary, which obviates the need for anything like this.

Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Fri May 19 1995 - 00:00:00 CEST