How to decide on the Primary Key

What is a primary key? A primary key uniquely identifies each record within a table. The main purpose of a primary key is to relate records to additional data stored in other tables. In this sense, the primary key is a  pointer between related records in different tables

Should the primary key be a natural key , made up of natural data points or an auto generated identity ?  

There is another conversation about Natural key versus Surrogate key on a 1 column table. It's a similar debate but focused on the question of how to handle a 1 column table

Thousands of forum posts exist arguing back and forth on the rationale for Primary Key decisions.

Some  arguments against using an auto generated primary key

1)      From a database design theory purists perspective, a  relational key is defined as a subset of attributes of an entity or relationship  in the data model.  Therefore , using auto increment identity key as a Primary Key is applying a pointer not part of the data model logic.  A value is used  not having any relation to the data model meaning.    A data architect     understands the differences and will spend endless hours on the data model logic

2)      Using the auto generated primary key undermines  the  benefits gained  from the designing the data model

3)      SQL programming is harder to apply. Non data model entities make SET theory harder to apply

4)      Extra data needs to be managed

5)      SQL code is less portable. Auto generated keys ten to be proprietary based

6)      Data integrity is compromised. Read more on Where to maintain data integrity rules?

7)      If you delete a row , it leaves a gap in the numbering. A separate routine is required to compress gaps.

8)      Extra constraints are required to to protect against  adding multiple rows of the same data but a different IDENTITY.

Arguments for using an auto generated key .( Sometimes referred as a “surrogate key”)

1)      A Primary key uniquely identifies  each record . You’re not supposed to change the primary key value. Using natural keys – such as  a street name and number  , can lead to problems when a correction is required.

2)      Sometimes it’s not possible to have the right information when the row is created. Using natural data points may make it difficult to create a record , if all data is not available

3)      A natural key is subject to input errors. For example , If suitable checks and constraints aren’t in place , data can enter the system which doesn’t fulfil the data model

4)      Business rules can change – which may lead to changes in values

A big headache arises when there are no industry standards such as ISBN numbers , which then forces the designer to  check digits, validation rules and an audit trail

Read More on T-SQL conventions

SQL Server - ISO-11179 Naming Conventions and SQL DDL

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server Performance Killers

Stored Procedures versus ad-hoc paramaterized queries.Which is faster?