Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

On Tue, 26 Nov 2002, niall.litchfield_at_dial.pipex.com wrote:
> <ctcgag_at_hotmail.com> wrote in message
> The problem is that the assumptions often turn out to be incorrect.
> It appears that US SSN's can have duplicated values. Equally I
> couldn't appear in such a database (not having an SSN). Pick an SSN as
> a natural key and you will eventually get duplicates or null values.

I've gone back and forth on this and I think this has finally made me make up my mind.

If, I assume that I chose SSN as my primary key, what do I do when the SSN has been duplicated? Where is my schema design now. If, on the other hand, I defined a dummy PK, and an SSN column with a unigue PK on it, then I could turn the unique index on the SSN into a non-unique one.

At that point, I would have the same whirl-wind, Oh shit, what do we do now issue no matter the PK vs non-PK argument, but my application would still work. I could figure the work-around, test it and implement it. With the duplicate SSN as my PK, I have actually have to tell somebody that I can't service them as a customer, and then try to fix things.

So, the internal dummy key seems to offer something the "supposedly natural key" can't. Protection from disastrous client interactions.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.