SQL Primary Key

This example creates a Customer table with Id as the primary key.

CREATE TABLE Customer (
  Id INT IDENTITY,
  FirstName NVARCHAR(40) NOT NULL,
  LastName NVARCHAR(40) NOT NULL,
  City NVARCHAR(40) NULL,
  Country NVARCHAR(40) NULL,
  Phone NVARCHAR(40) NULL,
  CONSTRAINT PK_Customer PRIMARY KEY (Id)
)

The Id column will hold a unique value for each Customer record.

Note: The IDENTITY keyword creates an autonumber column that automatically increments the Id when a new record is added.

An autoincrementing primary key column is called a surrogate key.

The alternative is a natural key, which is a primary key defined by one or more existing columns in the table. Natural keys are rarely used because most column values are not unique. Also, natural keys with multiple columns take up more resources, and they are cumbersome to work with.

Primary keys help identify and locate specific records in the database.

In this query the primary key value (88) identifies a specific Customer record.

SELECT *
  FROM Customer
 WHERE Id = 88

Note: Retrieving records by primary key is very fast and efficient. First of all, SQL Server automatically adds a UNIQUE INDEX on the primary key column which makes locating the record fast. Secondly, the WHERE clause is simple with just one column: WHERE Id = 88.

Tables can be created without a primary key.
To add a primary key, use the following syntax.

ALTER TABLE Customer  
ADD CONSTRAINT PK_Customer PRIMARY KEY 
    CLUSTERED (Id)

A composite key with multiple columns can also be added to a table.

ALTER TABLE Customer  
ADD CONSTRAINT PK_Customer PRIMARY KEY 
    CLUSTERED (FirstName, LastName, Phone)

Again, composite keys should rarely, if ever, be used.

To remove the primary key from a table use the following syntax.

ALTER TABLE Customer
DROP PRIMARY KEY

With this command, the column is no longer used as a primary key. It does not delete the column.

A composite key is a primary key which that consists of multiple columns.

The table below has a composite key on 3 columns: FirstName, LastName, and Phone.

CREATE TABLE Customer (
  FirstName NVARCHAR(40) NOT NULL,
  LastName NVARCHAR(40) NOT NULL,
  City NVARCHAR(25) NULL,
  Country NVARCHAR(40) NULL,
  Phone NVARCHAR(20) NOT NULL,
  CONSTRAINT PK_Customer PRIMARY KEY (FirstName, LastName, Phone)
)

Retrieving records by composite key requires a more verbose, cumbersome WHERE clause.

SELECT *
  FROM Customer
 WHERE FirstName = 'Paula' 
   AND LastName = 'Parente'
   AND Phone = '(14) 555-8122'

Note: Not only are WHERE clauses more cumbersome with composite keys, JOIN statements are particularly awkward with numerous columns that have to match.

For this reason, composite keys are rarely used. You may encounter them in legacy databases.

• Our Sql CREATE Table Reference
• Our Sql Foreign Key Reference
• Our Sql Identity Reference