Re: PRIMARY KEYS - Mailing list pgsql-general

On Wed, May 21, 2003 at 12:02:01PM -0400, Vivek Khera wrote:
> >>>>> "MvO" == Martijn van Oosterhout <kleptog@svana.org> writes:
>
> MvO> Number or Drivers Licence number as primary key is forbidden. If
> MvO> you're permitted to have the information you're allowed to store
> MvO> it but you sure as hell can't index your filing cabinets on it or
> MvO> make it a primary key.
>
> What exactly is the difference between
>
> UNIQUE INDEX on a NOT NULL column of driver license numbers with no
> other primary key on that table
>
> and
>
> PRIMARY KEY on that same column?
>
> The only thing that changed was the name.

There's no *technical* difference, but a very large semantic difference.
Whatever you use as your primary will become prolific. It will be used in
tables that reference this one, it will appear on invoices and receipts,
debugging traces. It will yelled across the room by staff in efforts to
track down problems. It will be given as a reference number to other
organisations who do stuff on a customer's behalf. If I had to give you my
driver's licence number, I'd feel a lot better if there was at least some
possibility of access control.

Primary keys should never change once a record has been created. If they do
you need to update a lot of other stuff. In that sense using opaque identifier
is good because then it never needs to change.

Besides, over here two states could hand out the same licence number, you
need to write the name of the state down too.

> But then, try explaining that to your lawmakers...

They care only about the semantic difference. It's a very simple but
effective way to restrict the flow of private information. Note they are
only principles (guidelines), not laws (though there are departments to deal
with customer complaints about it). The criminal act is the unauthorised
distribution of private information. Identity theft is a real problem, why
not do the right thing and do your part?

If you can guarentee that even though it's your primary key you're not going
print it out or send it to people who have no right to know that info, then I
guess you're in the clear. If you can't guarentee it, why take the risk?

Given the amount of effort going into prevent spam, why can't people spend
the same amount of time reducing the amount of private information floating
around uncontrolled.

Sorry for going off topic,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington