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

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<uwumzph80.fsf_at_hotpop.com>...
> 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, I do not find your argument to be very convincing. If you have an artificial PK and along with a unique requirement on the SSN and the unique SSN suddenly becomes non-unique you are likely to have numerous places in the code where one and only one row is expected by the logic represented by your code plus the data related by the artificial key is in reality related to the business value, that is, the SSN. So now are you sure the related artificial key data is connected to the correct SSN information. While technically the artificial key should keep working in practical reality you are likely to have problems. In a case like this you have a design issue that is really independent of the method used to assign the PK.

The US Social Security Administration has never said that SSN numbers are unique as it is the SSN and certain character positions of the name that make the number truly unique; however, it is policy to not re-issued the number to anyone until the original holder has been dead for a certain length of time so for practical matters the number should be unique. What you are most likely to run into is that in the case of foreign nationals who marry an American who then passes away is that the Social Security Administration uses the deceased person&#8217;s SSN plus a suffix. That means the nine-digit numeric is actually a ten digit alphanumeric where the tenth digit is almost always blank or null depending on how you want to model it. Most systems only expect nine-digit SSN and lose the suffix if the holder bothers to give it.

In most all other cases you either have a bad number or fraud. Though to complicate matters I did read that a minor error was made and a few numbers (somewhere in range 15,000 &#8211; 30,000) were re-issued too soon.

But this is a data definition issue and the point is if your business rules change your application may need to change no matter what method of assignment was used for creating the primary key as the artificial key has to be associated to the correct business data. In examples like this that association is subject to errors.

IMHO -- Mark D Powell -- Received on Wed Nov 27 2002 - 09:13:31 CST