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

Galen Boyer <galenboyer_at_hotpop.com> wrote:
> On Tue, 26 Nov 2002, niall.litchfield_at_dial.pipex.com wrote:
> > 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?

First you panic, then you assign the offending person a fake SSN so you can process them until you get a chance to redesign and fix the system.

> Where is my schema design now. If, on the
> other hand, I defined a dummy PK, and an SSN column with a unigue
> [constraint] 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.

Would it? do you have any
...where blah=(select foo from bar where used_to_be_uniq_col=?) code? That would certainly be broken, as in throwing errors.

Or how about
...where blah=(select foo from bar where used_to_be_uniq_col=? and rownum=1) That would broken, not in that it would throw errors, but in that you don't really know whose leg you just ordered amputated, the new patient with the duplicated SSN, or the original patient who had that SSN.

> 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.

I would rather tell someone "Let's pretend you SSN is 000-00-0001 until we figure this out" than to amputate the wrong person's leg.

I'm not arguing against the use SIDs, I use them all the time. I just think that you if you use a SID as the pk, but then still have a natural key as a unique and not null field and write the application accordingly, you probably aren't solving the problems you think you are solving. If you are writing the application so the the natural key is not required to be unique and not null, then why have that constraint in the database?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB