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

On Fri, 8 Mar 2013 23:11:29 +0100
Wolfgang Keller <feliphil_at_gmx.net> wrote:

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

The killer argument: You don't make unique things more unique by numbering them. You don't even make nonunique things unique by numbering them; it just seems that way.

> > There is no theoretical/logical basis to introduce a surrogate
> > primary key in the presence of a natural key.
>
> 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.

Roy correctly said that the relational model identifies a row by its key. A relational database wouldn't require one of the keys to be "primary".

I'm shocked, shocked to hear of DBAs and frameworks that don't understand elementary aspects of the relational model. Shocked, I say.

I have worked with tables whose natural key was four columns or more. The worst was a tax lot table with IIRC at 13-column key: the combination of account number, security id, quantity, date, and price wasn't enough. Assigning each one a number made them more convenient to refer to. But (as in your case) it was an extra column, another number to generate and maintain, another opportunity for error.

HTH. --jkl Received on Sat Mar 09 2013 - 01:49:57 CET