[AccessD] Primary Key Best Practices

  Groan...I guess I'll stick my neck out a bit.

<<1.  All tables should have a primary key even though it will not be used
as a foreign key (FK) in another table.>>

  Yes, so a record is always uniquely identified.

<<2.  PKs should always be an autonumber (Access), Identity (SQL) or GUID.>>

  I don't agree with that 100%.  Surrogate keys are in widespread use and
for most tables are used 98% of the time in today's designs, but I think
it's really silly to add a surrogate key to a many to many linking table
when you already have a FK pair that must be unique and would serve
perfectly well as a PK.

<<3.  Avoid complex PKs such as two PKs from two other tables.>>

  Don't agree in regards to M-M linking tables.

<<4.  Avoid complex PKs such as two fields from one table.>>

  If your in the surrogate key world, this doesn't ever apply.

Jim.

 

-----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