How to Define an Auto Increment Primary Key in SQL Server
Similar to MySQL, PostgreSQL, Oracle, and many other relational databases, SQL Server is best utilized when assigning unique primary keys to most database tables.
The advantages to using numeric, auto incremented primary keys are numerous, but the most impactful benefits are faster speed when performing queries and data-independence when searching through thousands of records which might contain frequently altered data elsewhere in the table. With a consistent and unique numeric identifier, applications can take advantage of these faster and more reliable queries.
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
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 KEY can 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 combination of 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
IDENTITY 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
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.