Re: a union is always a join!

"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:c4Fvl.26433$ZP4.2289_at_nlpi067.nbdc.sbc.com...
>

> As an exercise, try to write a set-based update trigger (a statement
> trigger in Oracle) on a table with only a composite natural key. You'll
> find that when a column in the key may be the target of an update, that
> you can't just join deleted to inserted (old to new in Oracle) to
> determine exactly what is different. (That's one of the main reasons
> Oracle and many other implementations support row triggers.)
>
>

The idea behind entity keys is that they are immutable. When analyzing the universe of discourse into entities, it's important to discover a reliable identifier for each entity. In practice, it's sometimes necessary to synthesize identifiers, because the identifiers in common use are unsuitable. An example might be the use of common names for people at the time when information systems were first being computerized.

Foreign keys are immutable during the period of time that they refer to the same thing, for the same reason that entity keys are immutable. It follows that foreign keys should not have to be updated as a cascade from the update of the entity keys they refer to. An update to a foreign key that represents negation of the old relationship and assertion of a new one ought to be expressble as a delete followed by an insert. I could be wrong on this, as I haven't completed the exercise you proposed.

 The tables I have built with only composite natural keys have all been relationship tables, and not entity tables. In these cases, the composite key that identifies rows in the relationship table are also foreign keys that reference entities.

By the way, are you claiming that all attributes are mutable? Received on Tue Mar 17 2009 - 14:35:32 CET