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

Wolfgang Keller wrote:

> Hello,
>
> I'm aware that this might be an FAQ, but I've googled to no avail so
> far. The discussion/documents that I've found relating to the
> subject revolve around other aspects than data integrity, and this is
> what I care for.
>
> I've always been used to using exclusively natural (comosite) primary
> keys, to warrant data integrity. Now computer science people (I'm just
> an engineer) keep telling me that concerning data integrity, it would be
> absolutely equivalent to use a surrogate primary key together with a
> unique constraint on the natural key.

I'm a computer science person, albeit one who dates back to the 1970s and I will tell you (a) there's no such thing as a primary key; a key is a key or it's not. Preferring one candidate key over another is purely psychological. There is no theoretical/logical basis to introduce a surrogate primary key in the presence of a natural key. Nor, of course is there a basis for excluding a surrogate primary key either. Use surrogates or don't, but don't pretend the surrogate key improves your data integrity/quality because it doesn't.

And (b) in the absence of a natural key any surrogate key is pure hope and conjecture (i.e. error dressed up as "best practice"). Doing that certainly would mask a data massiveintegrity problem.

>
> I seem to vaguely remember from my classes long ago that there is an
> issue with data integrity, especially referential integrity with this
> alternative, but I can't figure anymore out what it was precisely.

It's a pain in the jacksie when surrogate keys spread deep into the database abandoning the natural keys at the door so you can never find anything except by joining (and joining and joining...).

I don't think there are any integrity problems provided you enforce unique constraints on the natural keys.

But why introduce surrogate keys at all? Even in SQL databases we can define a foreign key with ON UPDATE CASCADE.

> Am I mistaken?

Only a bit.

> Sorry for opening a potential can of worms.

Not at all. We like a good worm here.

-- 
Roy