Database Table Primary Keys

When designing a database, I have a standard of always creating a clustered primary key in each table of type INT IDENTITY(1,1) NOT NULL.

CREATE TABLE Cars(
    ID                    INT IDENTITY(1,1)   NOT NULL
    CONSTRAINT PK_Cars PRIMARY KEY CLUSTERED,
    Brand                 NVARCHAR(20)        NOT NULL,
    RegistrationNumber    NVARCHAR(10)        NOT NULL,
    MadeIn                NVARCHAR(20)        NOT NULL
)

It is simple to create and for 90% of all tables this will give good performance and a small storage footprint. Those 9% of tables that are link tables for many-to-many relationships get a composite primary key, made up of the primary keys from the two related tables. Two simple rules handles 99% of the cases for primary keys. In this post I will go through some considerations for primary keys and show why an INT IDENTITY(1,1) NOT NULL field almost always is a good choice for a clustered primary key. The examples and technical details in this article are written for MS SQL Server, but the concepts should apply to other database engines as well.

Software Development is a Job – Coding is a Passion

I'm Anders Abel, an independent systems architect and developer in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

Code for most posts is available on my GitHub account.

Popular Posts

Archives

Series

Powered by WordPress with the Passion for Coding theme.