Re: Separate foreign keys with shared ID space

Howard J. Rogers (hjr_at_dizwell.com) writes:
> The problem I have with synthetic primary keys is precisely that they
> abstract themselves so far away from business logic and hence become
> 'meaningless' that they cease to do the job they were intended to do. If
> I insert a new record into the PERSONS table, using only a sequence
> number generator to supply a new, unique ID for the row, I am pretty
> well guaranteed to be able to insert the new record, even for a criminal
> clone of Al Capone. If I use the SSN as a primary key field, however, it
> is highly likely that my insert will (correctly) fail whenever someone
> is trying to do a bit of identity theft.

So you use SSN. Now you want to enter me in your database. What is my SSN?
> Sure, I could use a synthetic primary key, and then
> add a unique constraint to a field such as SSN...

At least then you have less problem when someone's SSN changes. And you can handle persons without SSNs. (That is, your constraint should be unique-when-NULL.)

You can also apply a partial validation rule. If the person is a Swedish tax payer, I may require that is person identifcation number is unique (because our customers reports to Swedosh tax authorities), but if he pays tax in Upper Fragonia I could care less what his identification numbers they may use there.

> For me, a primary key should absolutely embody a business rule ("no two
> people can share a SSN", for example),

The problem is that the real-world business rule is different. Two persons *can* have the same person identification number. It can be because of systems that have goofed, or it can be because they live in different countries - but still lives in the same system.

-- 
Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp