Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Separate foreign keys with shared ID space

Re: Separate foreign keys with shared ID space

From: Erland Sommarskog <>
Date: Sat, 31 Jul 2004 23:19:46 +0000 (UTC)
Message-ID: <Xns9538D45EA661Yazorman@>

Howard J. Rogers ( 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,

Books Online for SQL Server SP3 at
Received on Sat Jul 31 2004 - 18:19:46 CDT

Original text of this message