Primary keys: best practice
Posted on 2006-03-24
I was not the most attentative student in my Database lectures but I did pick up enough on relational database theory to understand the process of defining primary keys for a table. However, I have found myself in a situation where I have designed a database and used "natural primary keys" where my choice of keys has been challanged in favour of a unique record ID (IDENTITY) field. To explain - take the tables
create table tblTest(
userid INT IDENTITY(1,1) NOT NULL,
PassportNumber VARCHAR(20) PRIMARY KEY
create table tblTest2 (
recid INT IDENTITY(1,1) NOT NULL,
userid_id INT NOT NULL,
product_id INT NOT NULL,
... some other fields
ALTER TABLE [dbo].[tblTest2] WITH NOCHECK ADD
CONSTRAINT [PK_tblTest2tblTest] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
In both cases the primary key selected is the natural primary key for the table and is guaranteed unique - no problem.
A "discussion" has arisen because the implementor wants to remove the primary keys created and make the record id's the primary keys (the identity fields) because it will make some part of the coding easier. When I challanged this I realised I couldn't give a practical reason why the original method was the way to go - other than that from a theoretical perspective it was the correct way of doing things. Obviously the change would include creating a unique constraint on the fields previoulsy part of the primary key along with indices where required.
I would like to hear some opinions on this - does it really matter and if so why?