SQL Server Add Primary Key

Adding a primary key can be done either after a table is created, or at the same a table is created. It is important to note, that by default a primary key is clustered. This may or may not be the preferred method of creation. For more information on clustered vs non clustered indexes, please see this article. Either way, you can specify the clustered / non clustered option when creating a primary key.

To create a clustered primary key on an existing table:

[cc lang=”sql”]
ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY CLUSTERED (PersonID);
[/cc]

To create a non clustered primary key on an existing table:

[cc lang=”sql”]
ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY NONCLUSTERED (PersonID);
[/cc]

To create a composite primary key on an existing table:

[cc lang=”sql”]
ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY CLUSTERED (PersonID, DOB);
[/cc]

For an existing table, there are two places that can be used to specify the primary key. The first is inline to the column. Using this method, you cannot create a composite primary key:

[cc lang=”sql”]
CREATE TABLE [dbo].[Person](
PersonID [int] IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED NOT NULL,
FirstName VARCHAR(50) NULL
)
[/cc]

The second way is to specify the primary key after the creation of the table, this will enable you to create a composite primary key. Keep in mind that in order to use a column in the primary key, the field must not be nullable.

[cc lang=”sql”]
CREATE TABLE [dbo].[Person](
PersonID [int] IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NOT NULL
CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonID, FirstName)
)
[/cc]

Note: The “CONSTRAINT PK_Name” portion of the code above is optional, however if not specified a constraint name will be auto generated.

Prerequisites
The primary key column(s) must be unique for each row. If you are altering an existing table and trying to add a primary key to a column that is not unique, you will receive the error:

“The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.Person’ and the index name ‘PK_Person’. The duplicate key value is (1).”