Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: multiple primary keys
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