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

> (a) there's no such thing as a primary key;

PostgreSQL seems to think differently. >;->

It requires definition of a primary key afaik.

> 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.

Don't tell that to me. ;-)

Tell that to those DB admins who refuse natural (composite) keys and to the developers of certain DB application frameworks that don't allow composite primary keys.

> 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.

The surrogate key was not supposed to *improve* data integrity.

The issue was whether a surrogate primary key plus a unique constraint on the natural key is *equivalent* concerning data integrity, especially relational integrity, to a natural primary key.  

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

So I'm mistaken...?  

> But why introduce surrogate keys at all?

I don't want to open *that* can of worms, since for me the decision is taken anyway. By the requirement to federate/exchange data referring to the same "real world" item but coming from different sources. Which would require prohibitive amounts of handwork with surrogate keys, if it is possible at all.

I just wanted to know if there's a "killer" argument concerning data integrity *against* the approach to use a surrogate primary key plus a unique constraint.

> > Sorry for opening a potential can of worms.
>
> Not at all. We like a good worm here.

Bon appetit. >;->

Sincerely,

Wolfgang Received on Fri Mar 08 2013 - 23:11:29 CET