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

On 27 Nov 2002, ctcgag_at_hotmail.com wrote:
> 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?

This is the point. When I started coding, I "thought" ssn's were unique. I then find out they aren't after the app is in production. With the SSN as the PK, I have a much bigger issue. I now have this key all over the database, and it is the incorrect key and will need to be changed.

If I had a dummy key, then I would be able to isolate the issue to how to handle the SSN number. If in fixing it, like your example talks about, I might amputate the wrong leg, well, then how I deal with and fix this issue would be different. I might not fix it right away, but instead, tell the guy (as you said, lets pretend your ssn number is 000-000-0001 until we figure this out). But, when it came time to fix it, it would be an isolated fix. Maybe an extra piece of code added to the app to count how many rows returned and then prompt for blah... In your scenario, this same exact fix might be your solution as well, but you would have the added task of redefining your PK. So, not only do you have to implement the same fix I would have to, you also have to fix your database in a drastic fashion.

-- 
Galen Boyer