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

For a single relation, obviously there is no difference. But at the database level there is at least one case - composite overlapping keys - when a surrogate key design can lead to anomalies if not supplemented by other restrictions.

Maybe is not the best example, but let’s take the following database (with the obvious foreign keys):

clients {client}
client_orders {client, order}
client_locations {client, location}

deliveries {delivery_no, client, order, location}

Now add surrogate keys to all relations, and replace the last one with:

deliveries {delivery_no, order_id, location_id}

You may see that client attribute is no longer needed since it can be determined from either order_id or location_id. However this design no longer enforce the rule that the order and the location should refer to the same client.

On Friday, March 8, 2013 9:22:33 PM UTC+2, 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 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?
>
>
>
> When they taught us the different levels of normalisation, the professor
>
> proceeded this way; by giving an example of a not-normalised schema for
>
> each level and how this would break integrity in case of certain
>
> (legal) operations on the data.
>
>
>
> Is there a similar example for my case?
>
>
>
> Any hints to corresponding explanations that I haven't found yet are
>
> welcome.
>
>
>
> Sorry for opening a potential can of worms.
>
>
>
> TIA,
>
>
>
> Sincerely,
>
>
>
> Wolfgang
>
>
>
> P.S.: In my specific application case surrogate keys are a non-starter
>
> anyway, since the schema I am working on serves for
>
> exchanging/federating data from several independent sources, and if
>
> records coming from different input systems referring to the same
>
> "real world" item have different keys, the data from the different
>
> sources can't be federated/exchanged without prohibitive manual extra
>
> work.
>
>
>
> But I'm just looking for the generic case.
Received on Mon Mar 11 2013 - 10:48:45 CET