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

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 Fri Mar 08 2013 - 20:22:33 CET