Re: ID field as logical address

On Jun 3, 7:04 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> This is an example of the noise I referred to in a previous post. The
> problem exhibited here occurs for /any/ key in which instances of it do not
> permanently identify something in the Universe. This even includes keys
> that are supposedly stable, such as part numbers or GL account numbers. If
> it is possible to issue an update that targets one or more prime attributes,
> then a join of the relations before and after the update can't be relied
> upon for defining transition constraints.

I see your point, but the problem has a different and subtle cause. You change the value of both the key and dependent attributes in the same time. So two logical operations are combined in a single relational assignment. This two-in-one approach hides some information from the system. The problem can easily be avoided by performing the operation in two steps (as shown earlier).

But let’s consider your suggestion and use a surrogate key. Now suppose the person with id #1 with state “single” gets married and divorced in the same weekend. The operator thinks that it makes no sense to issue two update operations so he changes the state in “divorced”. The operation will obviously fail. This is the same logical error, a two-in-one operation.

In the absence of the transition constraint we can make arbitrary relational assignments. However when transition constraints are involved, not only the state but also the state change itself is information. By combining several logical operations in one relational assignment we hide the state change information from the system. As a consequence we should expect transition constraint failures.

Still I can see a case where using surrogate keys might be useful (when using transition constraints).

Suppose we change the key to {L, F, State} (a silly example since the state is clearly a dependent attribute).

Now the transition constraint for changing the state from “single” to “divorced” will never be fired. Since state is part of the key the system has no way to join the old and new values. More over, if we put a constraint such as “initial value of the state must be single” then we will not be able to set the state to a different value.

It is hard to find a reason to put a transition constraint on something (the primary key) that is not supposed to change (except by accident), but probably there are some legitimate cases.

In such a particular case, when we need a transition constraint on an attribute that is part of all the keys, there will be no other solution than adding a surrogate key (adding and not replacing existing keys). Received on Thu Jun 04 2009 - 10:19:02 CEST