[AccessD] Primary Key Best Practices

There are good arguments for 1. is you're using SQL Server, since you
can't update those tables from the FE unless they have a PK.  4. seems
to be a justification for 2. rather than a real rule of thumb.  An
autonumber, etc., will always uniquely identify a row, but if you have
to create a unique key on that table anyhow using several fields, that
gives you TWO unique keys on the same row, which can be argued about
endlessly (and has, here! LOL).  3. ignores the fact that junction/join
tables often or perhaps usually use the PKs from the tables being joined
to uniquely identify the record and form a natural PK.

Just my opinions, of course. ;>

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
Sent: Tuesday, July 24, 2007 7: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