Primary Key in SQL Server

A primary key in SQL Server is a field (or combination of fields) in the table that uniquely identifies each row or record. In this tutorial let us learn what is a Primary key. We will learn how to create a Primary Key constraint. Learn to add or drop the Primary key from an existing table. We also look at how to change the primary key index from clustered to non clustered and also change the sort order from ascending to descending etc.

Need for Primary Key

Take a look at the following employee data. We can look for a particular employee using their firstName. But what about Teri Austin (Accounts Department). We have two employees with the same name. There is no way for us to identify the correct Teri Austin.

Need for Primary Key in SQL Server

We solve this problem by assigning a unique Id to each employee. No two employees will have the same ID. The makes it easier to identify an employee easily. Such a unique id is the Primary key of the table

Employee Data with Employee ID as Primary in SQL Server

Table without Primary Key

You can create a table without a Primary Key in SQL Server. But it is a very bad practice.

We learned how to create a table in an SQL server. The following script creates the table Employee. Note that we have an EmployeeID column, but we have not marked it as a Primary Key.

The following SQL script adds some data.

Now, what if we insert a duplicate record ?. The SQL Server does not stop us from doing so. The following script runs without any error.

This is where the Primary Key Constraint comes into the picture. We mark the EmployeeID column as Primary Key. This will prevent the SQL server from inserting a duplicate value into the Primary Key Column

Primary Key Constraint

The PRIMARY KEY constraint tells the SQL Server that the specified column(s) is the Primary Key of the table.

When we mark a column as Primary Key the SQL Server enforces the uniqueness of the Primary Key value. i.e. no two rows of a table can have duplicate values

The primary key can consist of one or more columns. If the Primary key contains more than one column then it is called a Composite Primary Key

A table can have only one Primary Key. That is by design. SQL Server does not allow the creation of more than one Primary Key per table

Only NOT NULL columns can be part of the Primary Key.

SQL Server creates a unique clustered index on the Primary key columns. If the table already has a clustered index then it will create a unique non clustered index. This also ensures fast access to data when the primary key is used in the queries.

Single & Composite Primary Key

A Table can have only one Primary Key. But the Primary key can contain a single column or combination of multiple columns. If the Primary key contains more than one column, then it is called a Composite Primary Key

If a primary key contains more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key must be unique.

Create Table with Primary Key

Single Primary Key

If the Primary key contains only one column then you can apply the constraint on the column level.

The following query marks the EmployeeID as the Primary Key of the Employee table. Remember you also need to apply the NOT NULL Constraint on that column also

You can also apply the Primary key constraint at the table level.

Composite Primary Key

We cannot create the composite Primary key at the column level. The only way to achieve this is to apply the Primary Key constraint at the table level.

The following query creates the Composite Primary key using the columns EmployeeID & LeaveID

Naming the Primary Key Constraint

We can name the Primary Key Constraint while creating the Table. The following script creates the composite primary key using fields EmployeeID & LeaveID with the name PK_EmployeeLeave.

Naming a Composite Primary Key

Naming a Primary Key

Find the Primary Key Constraint Name

Use the following query to find out the current primary key name along with the fields of a given table ( EmployeeLeave).

If you want to find out only the constraint name and not fields use the following query

Drop a Primary Key from Table

To drop the existing Primary key first find out the Primary key constraint name using the query from the previous section

Once you have the name you can delete it using the following query, which drops the primary key with the name PK_EmployeeLeave from the table EmployeeLeave.

Note that the primary key column may be referred to by other tables using Foreign key constraints. You must drop/disable them before dropping the Primary Key.

Add Primary Key to Existing Table

You can add a Primary Key to an existing table using Alter Table query.

The following query creates Composite Primary Key on fields EmployeeID & LeaveID on table EmployeeLeave

Modify the existing column a Primary Key

You cannot modify an existing Primary key. You need to drop the Primary key and then create a new one

Then create the new one.

Primary Key & Index

Clustered/Non Clustered

SQL Server automatically creates a clustered Index when creating the Primary Key. You can change it to a nonclustered Index just by including the NONCLUSTERED keyword.

Or you can explicitly ask for the Clustered Index

The syntax is the same for Alter table query.

Sort Order

By default SQL Server sorts the fields in an index in ascending order. You can use the ASC or DESC after the column names to change the order.

The following query sorts the EmployeeID in ascending order while sorts the LeaveID in descending order.

Reference

Create Primary Keys

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top