Re: ID field as logical address

Brian Selzer wrote:

>[...] But in
>theory, using just the before and after values of the Bins relvar, how can
>one declare the transition constraint?

You're right that it's impossible without making some further assumption, Designing your database schema to have immutable keys is clearly preferable but I agree with you that in some situations this isn't possible. Changing the situation to allow it would still be a good idea, as I think your bins example clearly shows: it's a design begging for trouble.

The alternative you suggest, if I understand you correctly, is to impose a strict policy of use for update, insert and delete statements, in which an update to a primary key value must always mean that the identification of the entity is being changed, while a sequence of deletions and insertions never changes this identification.

I think this does resolve the issue, not that of formulating transition constraints in general, but the more specific problem of formulating transition constraints in terms of entity names ("supplier", "bin") that map one-to-one, but not immutably, to the tuples of a relation.

But that doesn't mean it is the only way of addressing this issue. A simpler way is to just not use such designs or formulations.

-- 
Reinier