[AccessD] Primary Key Best Practices

jwcolby jwcolby at colbyconsulting.com
Wed Jul 25 12:33:43 CDT 2007


Jim,

>  No I don't.  A surrogate key is not a primary key in terms of the model.

But I don't work in the model, I work in the real world.  A PK is a PK
because the database assigns it the PK symbol, and as such I then use it as
a pointer.  The PK object in any database system (I use) CAN BE a surrogate
so the REAL WORLD accepts that surrogates can be PKs.  I don't see you
calling up MS and complaining that a PK symbol is assigned to your surrogate
PK.  It may be called a surrogate key, but in the real world it is called it
a surrogate PK.

>  Really?  I don't have to have any method of ensuring that each row in a
table is unique?

Open SQL Server (or Access).  Create a table.  Create a unique index on it.
You now have NO PK but you do have a unique index, ensuring that each row is
unique.

So SQL Server at least differentiates between PK and unique index.  I know
that Access works this way.  I suspect that the rest do too.  A PK and a
unique index are NOT the same thing.  You MAY specify that a field is the
PK, and as such the database will create a unique index - I acknowledge that
it has to do that, but again, the index itself does NOT MAKE the PK, the
unique index is a necessary attribute of the PK.  Being a PK is not an
attribute of a unique index.

Let's stop discussing the model.  It is not useful, I already signed my life
away stating that anything you wish to claim about the model is true, so
stop with the model already.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, July 25, 2007 12:44 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

John,

<<You accept that a PK can be a surrogate, you state that a PK is nothing
but a pointer, therefore you accept (and
state) that a PK is nothing but a pointer.>>

  No I don't.  A surrogate key is not a primary key in terms of the model.
It has no relation to the data what so ever.  I agree it's a pointer, but
nothing more then that. 

<<AND... from the perspective of the child, a FK is nothing but a pointer.
Does a table HAVE TO HAVE a PK at all?  Not if it has no children. >>

  Really?  I don't have to have any method of ensuring that each row in a
table is unique?

<<And I don't give a rat's patuty about relational theory,>>

  Yeah, I know you don't as we have been down this road a few times
before<g> (something about pig's teats comes to mind).  Personally I like to
fundamentally understand something as much as possible.  It helps me apply
things to other areas.

<<You are nitpicking to death stuff that simply does not matter.  If you
want to insist that the PK HAS TO BE a natural key, then how can you say in
the next breath that 

"I don't object to the use of surrogate keys">>  

  Because a "primary key" and a "key" don't mean the same things to me.  One
denotes something that is connected to the data.  The other could be
anything.    As for using a surrogate key, I don't object to them and I use
them all the time.    But I think it's important to understand that they are
a short cut.  In this case, the benefits are many and the ramifications few.
Does it matter?  In a day to day context no.  But in understanding how data
can be modeled certainly yes.

Jim.




More information about the AccessD mailing list