[AccessD] Primary Key Best Practices

jwcolby jwcolby at colbyconsulting.com
Tue Jul 24 10:50:24 CDT 2007


I always and only use autonumber type PKs.  

I differentiate between PKs and unique indexes, whereas many people do not.
A unique index's job is to prevent the same "unique" data from going into
the table twice.  A PK's job is to act as a pointer to a specific record and
to allow EFFICIENT joins between tables (act as a pointer).  Those are two
completely different functions, and they BOTH must be addressed, but they do
NOT have to be addressed in the same structure.  In fact the EFFICIENT join
PREVENTS them being addressed in the same structure!

Pulling fields from parent / grandparent /
greatgrandparent/greatgreatgreatgreatgreat...grandparent tables down into
the current table just causes a slew of problems, from performance to update
issues. Autoincrement PKs neatly sidestep all of those problems.  

In my experience, most of those who demand natural keys use "convenience"
reasons such as "I can always look in any table and see where the data comes
from or who owns the data (lineage).  True, but irrelevant.  That is
laziness, not requirement.  I have also heard a lot of arguments like "if
parent records are deleted I can still see who the data belongs to".  True
but irrelevant.  That is again laziness (too lazy / sloppy to implement RI).
I do not allow parents to be deleted unless children are deleted.  I have
been doing this a long time and NEVER REQUIRED a natural key, and in fact
AFAICR have never built a table with a natural key.  

Now I will grant there is a gray area such as "colors" where the color is
the only "value" field in the table and so "why not" use it as the PK.  The
answer comes back to speed issues.  Integers are faster to deal with inside
of a computer when doing things like joins.  Thus even in such cases I use
autonumber PKs.

I can tell you that I have been doing databases since 1995 and have never,
in even one case, REQUIRED a natural key.  I DO require unique indexes just
as anyone else will.

Just my opinion, ymmv etc.

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 Hewson
Sent: Tuesday, July 24, 2007 10:55 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Primary Key Best Practices

I need a few good references (preferably electronic) on the Best Practices
for defining Primary Keys (PK) Here at the office we have had a few heated
discussions about primary keys.
I don't' mean to stir the pot on this list, just curious what others think.

1.  All tables should have a primary key even though it will not be used as
a foreign key (FK) in another table.
2.  PKs should always be an autonumber (Access), Identity (SQL) or GUID.
3.  Avoid complex PKs such as two PKs from two other tables.
4.  Avoid complex PKs such as two fields from one table.

You could probably come up with some more.

Thanks,

Jim H. 
 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list