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

On 2013-03-15 12:01:45 +0000, robur.6_at_gmail.com said:

> On Friday, March 15, 2013 2:03:43 AM UTC+2, Jan Hidders wrote:

>>> - Performance (?)

> 
> That’s why I put a "?".
> 
>>> - You need more joins (performance?)

> 
> Let’s consider the relation reviews{reviewer, review-language, book, 
> rating} and tuple {34, 14, 66734, 4}. Can you tell anything about it? 
> Now replace with {34, 'EN', 'ISBN-XXXXX’, 'verry good'}. Do we need any 
> join if we want to search for a specific book, review language or 
> rating?

Ok. Fair enough. If the query only needs the parts of the entity that is in the foreign key that was replaced with the surrogate key, then, yes, you need an extra join.

> Also if you have something like D -> C -> B -> A, each relation having 
> it’s own surrogate, then you cannot join A and D directly (pretty 
> stupid design, but found in practice).

Hmm, yeah, but that's just stupid design and not really inherent in the use of surrogate keys. You could argue that the mistake cannot even be made if you wouldn't have had surrogate keys.

>>> - You lose the simplicity and power of overlapping composite foreign> > 
>>> keys constraints (discussed in this thread).

> 
> The problem may appear only if you replace a natural composite key by a 
> surrogate.

As in "it's because you are using them you might be misled into thinking you don't need composite keys"? Maybe, but what I would like to know is if they cause some inherent problems that cannot be avoided, even when modelling carefully.

• Jan Hidders