Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

On Monday, March 11, 2013 11:53:56 AM UTC-7, Wolfgang Keller wrote:
> > For a single relation, obviously there is no difference. But at the
> > database level there is at least one case - composite overlapping
> > keys

Last message I mentioned two particular ways of changing your database: adding surrogate side-by-side with naturals everywhere, and adding a surrogate in a 1:1 base table and replacing the naturals by the surrogate everywhere else. Here you are more or less talking about adding a surrogate everywhere.

> And I wouldn't even *want to* try
> and investigate what constraints I would need to enforce integrity in my
> case, given the shear number of entities and the complexity of the
> dependencies between the primary/foreign key]s.

So your base tables using natural columns that get surrogates added need the constraint added that each surrogate is paired with the naturals in its 1:1 table. You should add a redundant but optimization-helping fk definition involving a surrogate corresponding to each fk involving its naturals. Why is this overwhelming?

> Since I couldn't find an example like that in the (online as well as
> printed) literature, is there a textbook (or online documentation) about
> these issues? First, to quote it when I have to argue with people, and
> second, to improve my own education.

When you add a surrogate column corresponding to some natural key columns you have to add the fact the surrogate has to be paired with those naturals in the 1:1 base table. Do you need any more theory than the definition/reason for "constraint", "a constraint restricts a base table to values that can validly arise"?

philip Received on Mon Mar 11 2013 - 23:14:44 CET