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

Op vrijdag 8 maart 2013 20:22:33 UTC+1 schreef Wolfgang Keller het volgende:
> 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 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.
>
>
>
> Am I mistaken?
>
>
>
> Or could anyone give an example where the approach of using a surrogate
>
> primary key together with a unique constraint on the natural key would
>
> "break" data integrity (especially referential integrity) which could be
>
> avoided by using a natural (composite) primary key?

As you probably have understood by now from the other responses, there is no such example. The reasons to be against surrogate identifiers are usually more of the pragmatic type, e.g., - Schema designers tend to specify only the artificial PK and forget about the other keys - Tables become harder to understand and interpret for people (can become very relevant at maintenance time)

An argument that might be considered more fundamental is about the updatability of columns. If you believe that columns of PKs should not be updatable (and some DBMSs enforce this) because such updates are in principle better thought of as a delete plus an insert, then introducing a surrogate identifier changes the updatability. But most DBMSs actually do support updating such columns, and IMO the arguments for their non-updatability are not strong anyway.

• Jan Hidders