Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: multiple primary keys

Re: multiple primary keys

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Tue, 03 Oct 2000 13:54:08 GMT
Message-ID: <8rcods$nlg$1@nnrp1.deja.com>

In article <39D8DF09.E8E91B3A_at_home.nl>,
  frank <fbortel_at_home.nl> wrote:
> > By definition a table may have one and only one primary key which
 must
> > uniquely identify each and every row in the table and may consist of
> > one or more columns.
> >
> > It is possible to have several sets of columns to choose from to be
 the
> > primary key. These 'candidate keys' can be defined as unique
> > constraints. You can have both a PK and several unique constraints.
> >
> > Example - Employee Table
> > The table includes the Social Security Number which would make a
 good
> > PK but for security reasons an employee number is generated and
 this is
> > made the PK. The SSN is then declared as a unique constraint. The
> > employee number is publically visible, but the SSN is restricted to
> > mostly internal system use and payroll/benefits reports.
> >
>
> No so good example in today's world - SSN is very USA. Apart from
 that,
> what if the employee left the company (emp record stil exists as per
 legal
> requirements, but is dormant), and then returns? SSN for PK would
 impose
> a problem.
>
> In many systems, primary keys consist of many fields, because of the
> uniqueness constraint. It is thus regarded fasted (read: easier to
 program)
> to use artificial PK's (usually from number generators, like
 sequences).
> And: *don't* code that - I once had an employee id that could identify
> whether I was hired as white collar worker or not, the coutry I lived
 in,
> that I was male, whether I was married or not, etc, etc. Guess what
 happened
>
> when an employee from abroad joined...
> Rule #1 of computing: don't code id's!
>
> Don't understand the security part you mention - is it unsecure to let
> someone know your ssn? Pardon the ignorance - as you may have guessed
> from the above, I'm European, I don't know the consequences.
>
> > Because it is often desirable to seek employee data by name a third
> > unique index is devised using last name, first name, middle initial,
> > and Date of Birth and a unique constraint is declared on this set of
> > columns to allow the definition of a FK on the name columns in
 another
> > table.
> >
> > Anyway this is the theory as I remember reading up on it.
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >

Actually there is no problem with using the SSN as the PK just because an employee leaves and then returns. You would not want to issue a new PK to the same employee just because of a break in service. You would just flip the status flag from inactive, retired, terminated, etc... to active and reuse the existing employee record. After all you need to tie the employee back to his prior records for benefits, pension, year of service etc.... But the point of the example was to answer the question if a table can have more than one primary key. By theory it can not, but designers can have legitimate choices between columns to be the PK.

And yes the American SSN is a sensative piece of data. Because every American is issued an SSN it is convienent for companies to use it to identify people. Address's change, telephone numbers change, but the SSN is for life. Careless display of the SSN or any other sensitive peice of information aids criminals in commiting identify theft, credit fraud, and the like.

I agree that intelligent keys are a bad idea. The original set of rules is usually fairly complex and over time almost always becomes a nightmare of exceptions and special cases that have to be handled. When these kind of keys are used it is not uncommon to find that the table has to have both the super key and columns it contains in the key, present and indexed.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Oct 03 2000 - 08:54:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US