[AccessD] Access and SQL Server

My definition of a PK is:

1) A field or set of fields which uniquely identifies any given record
2) *AND IS USED TO DO THAT*.

NOTICE that I am not discussing *data* uniqueness here.  *THEY ARE COMPLETELY SEPARATE ISSUES*.

Candidate keys perform function #1 above.
There may be several candidate keys.  They cannot all be the PK because there can be only one PK!

Whether you use a candidate key or a surrogate key as your PK, selecting it as you PK means it CAN 
AND IN FACT does both.

So a PK is a PK simply because it can do both 1 and 2 above AND you select it to be your PK.  It 
does *not* have to be used as a FK to be a PK.  However IF you need a FK in a child table to link 
back to the parent table, then you are *supposed to* use the PK.  If you are a fruit or nut, you 
could use any candidate key to do that.  You could even use one candidate key as the FK in table ABC 
and a second candidate key as your PK in table XYZ.  You would of course be a fruit or nut to do that.

In fact (IMHO) you would be a fruit or nut to use a natural key at all.  ;)

An autonumber field with a unique index on it is automatically a candidate key.  It can be used to 
uniquely identify any given record in the table - #1 above.  However it in no way guarantees the 
uniqueness of the *data* in the table.  Only a unique index covering a set of fields which, taken 
together guarantees unique data, will in fact enforce data uniqueness.

A natural PK, by definition, performs both.  By making it the PK, SQL Server automatically creates a 
unique index on the entire set of fields, and guarantees *data* uniqueness and, while it is at it, 
calls it the PK.

If we are going to use a surrogate PK, then we *still* have to perform the analysis to find at least 
one candidate key, and we have to manually create a unique index to cover that candidate key (set of 
fields).

John W. Colby
www.ColbyConsulting.com

On 3/2/2011 4:47 PM, Jim Dettman wrote:
> John,
>
> <<
> <<   Having an auto number PK does not in any way relieve the developer from
> the responsibility of
> analyzing for a field or set of fields which ensure uniqueness and setting a
> unique index on those
> fields.>>
>
>   >Yes because it's a misnomer to call a auto number a PK or even a surrogate
> key.  It may be labeled
> as such, but it certainly does not perform the function of one.
>
> What?  It certainly can.
>>>
>
>    What then is your definition of a PK?
>
> Jim.