Re: multiple primary keys

In article <39d7296d.718536_at_news.mindspring.com>,   rakessler_at_mindspring.com (Richard Kessler) wrote:
> But the original question is still unanswered (and I'd like to know as
> well) : How many PRIMARY KEYs can one have on a table ??
>
> Have seen something recently indicating it is possible to have
> multiple primary keys,, but nothing in all the official documentation
> I've seen.... ..
>
> Richard Kessler
> rakessler
> @mindspring.com
>
> On Fri, 29 Sep 2000 16:09:49 GMT, mechhunter_at_rocketmail.com (Arthur)
> wrote:
>
> >On Fri, 29 Sep 2000 12:22:41 +0100, "Grzegorz Dryl"
> ><Grzegorz.Dryl_at_crt.tpsa.pl> wrote:
> >
> >>
> >>> I am only new to this but I don't think that you can have more
 than
> >>> one primary key per table.
> >>
> >>
> >
> >A ha! Thanks! I was thinking about that in the shower..
>
> I usually wake up at 05:30 thinking of this stuff ;-)
>
> >

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.

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


Sent via Deja.com http://www.deja.com/
Before you buy.