SQL Server - Primary Key

Here you will learn what is a primary key and how to create it in a new or existing table in the SQL Server database.

What is Primary Key?

In SQL Server, a Primary key is a constraint that uniquely identify each row in the table. It enforce data integrity in tables.

• A table can have only one primary key.
• A primary key can be defined on one column or the combination of multiple columns known as a composite primary key.
• A primary key cannot exceed 16 columns and a total key length of 900 bytes.
• The primary key uniquely identifies each row in a table. It is often defined on the identity column.
• The Primary key column do not allow NULL or duplicate values. It will raise an error if try to do so.
• All columns defined within the primary key constraint must be defined as a NOT NULL column.
• If clustered or nonclustered is not specified, then a unique clustered index for the primary key column will be created if there no clustered index on the table. This makes retrieving data faster whenever the primary key column is included in the query.

Primary Key can be created in two ways in SQL Server:

• Using T-SQL
• Using SSMS (SQL Server Management Studio)

Create Primary Key Using T-SQL

A primary key can be configured in the create table T-SQL script. Append CONSTRAINT statement at the end of all column declaration.

CONSTRAINT <primarykey_name> PRIMARY KEY [CLUSTERED | NONCLUSTERED] (<column_names>)

The name of a primary key should be in PK_TableName_ColumnName format to recognize it easily.

The following query creates a new Employee table and defines a primary key on the EmployeeID column.

CREATE  TABLE Employee(EmployeeID int IDENTITY(1,1) NOT NULL,FirstName nvarchar(50) NOT NULL,LastName nvarchar(50) NOT NULL,EMail nvarchar(50) NULL,Phone nvarchar(20) NULL,HireDate date NULL,ManagerID int NULL,Salary float NULL,DepartmentID smallint NULL,CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID))

If you want to configure multiple columns as a primary key, then specify column names in parenthesis. For example, the following configures EmployeeID and ManagerID as a primary key.

CREATE  TABLE Employee(EmployeeID int IDENTITY(1,1) NOT NULL,FirstName nvarchar(50) NOT NULL,LastName nvarchar(50) NOT NULL,EMail nvarchar(50) NULL,Phone nvarchar(20) NULL,HireDate date NULL,ManagerID int NOT NULL,Salary float NULL,DepartmentID smallint NULL,CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID, ManagerID))

Duplicate values in one column are allowed if a primary key contains more than one column, but each combination of values from all the columns of the primary key must be unique.

Create a Primary Key in an Existing Table

USe the ALTER TABLE statement to add a primary key in an existing table.

The following query creates a primary key for the EmployeeID column of the Employee table in the HR database.

ALTER TABLE EmployeeADD CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID)

Note: If using the ALTER statement to add a primary key to a column, make sure the primary key columns are declared as NOT NULL.

Create Primary Key Using SSMS

Open SSMS and expand the database in Object Explorer, and right-click on the table on which you want to add a primary key, and click on Design, as shown below.

This will open the table designer in the query window.

Now, click on the row selector for the column for which you want to define the primary key. To select multiple rows as composite primary keys, hold down the Ctrl key and select the other columns.

Here, we will set EmployeeID as a primary key, so right-click on it and click on Set Primary Key option in the context menu, as shown below.

Finally, save the table by pressing Ctrl + s keys or click on the save icon in the top toolbar.

After setting a primary key, you will see the primary key symbol before columns for which you set them as the primary key, as shown below.

Create Primary Key using Indexes/Keys

Another way to define a primary key is using Idexes/Keys dialog box. Here, you will get more options to configure it.

Right-click on the table designer and select Indexes/Keys, as shown below.

This will open Indexes/Keys dialog box where you can add or delete indexes or primary/foreign keys definend on the table. Click on the Add button to add a key.

Here, you can configure a primary key column, name, clustered index, etc. Save the table and the primary keys will be created.