Re: multiple primary keys

> 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 --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Oct 02 2000 - 14:24:29 CDT