Home » Blog » How to Define an Auto Increment Primary Key in SQL Server

How to Define an Auto Increment Primary Key in SQL Server

more

Basic Table Creation

Once connected to your SQL Server, you’d normally start by CREATING a new table that contains the the field you wish to use as your incremented primary key. For our example, we’ll stick with the tried and true id field:

CREATE TABLE books (
  id              INT           NOT NULL,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

The problem here is, we have no way of controlling our id field. When a new record is inserted, we not only must manually enter a value for id, but we have to perform a query ahead of time to attempt to verify that id value doesn’t already exist (a near-impossibility when dealing with many simultaneous connections).

Using Identity and Primary Key Constraints

The solution turns out to be using two constraint options provided by SQL Server.

The first is PRIMARY KEY, which as the name suggests, forces the specified column to behave as a completely unique index for the table, allowing for rapid searching and queries.

While SQL Server only allows one PRIMARY KEY constraint assigned to a single table, that PRIMARY KEYcan be defined for more than one column. In a multi-column scenario, individual columns can contain duplicate, non-unique values, but the PRIMARY KEY constraint ensures that every combinationof constrained values will in fact be unique relative to every other combination.

The second piece of the puzzle is the IDENTITY constraint, which informs SQL Server to auto increment the numeric value within the specified column anytime a new record is INSERTED. WhileIDENTITY can accept two arguments of the numeric seed where the values will begin from as well as the increment, these values are typically not specified with the IDENTITY constraint and instead are left as defaults (both default to 1).

With this new knowledge at our fingertips, we can rewrite our previous CREATE TABLE statement by adding our two new constraints.

CREATE TABLE books (
  id              INT           NOT NULL    IDENTITY    PRIMARY KEY,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

That’s all there is to it. Now the id column of our books table will be automatically incremented upon every INSERT and the id field is guaranteed to be a unique value as well.