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.
Table of Contents
- Need for Primary Key
- Primary Key Constraint
- Single & Composite Primary Key
- Create Table with Primary Key
- Naming the Primary Key Constraint
- Find the Primary Key Constraint Name
- Drop a Primary Key from Table
- Add Primary Key to Existing Table
- Modify the existing column a Primary Key
- Primary Key & Index
- Reference
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.
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
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.
1 2 3 4 5 6 7 8 | CREATE TABLE Employee ( EmployeeID int , FirstName varchar(50) , LastName varchar(50) , Department varchar(20) ) |
The following SQL script adds some data.
1 2 3 4 5 6 7 8 9 10 11 12 | insert into Employee(EmployeeID,FirstName,LastName,Department) values (1,'Olive','Yew','Admin'), (2,'Aida','Bugg','Admin'), (3,'Teri','Austin','Accounts'), (4,'Maureen','Biologist','Accounts'), (5,'Peg','Legge','HR'), (6,'Allie','Grater','HR'), (7,'Liz','Erd', 'HR'), (8,'Teri','Austin','Accounts') |
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.
1 2 3 4 | insert into Employee(EmployeeID,FirstName,LastName,Department) values (1,'Olive','Yew', 'Admin') |
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
1 2 3 4 5 6 7 8 | CREATE TABLE Employee ( EmployeeID [int] PRIMARY KEY NOT NULL, FirstName [varchar](50) NULL, LastName [varchar](50) NULL, Department [varchar](20) NULL, ) |
You can also apply the Primary key constraint at the table level.
1 2 3 4 5 6 7 8 9 | CREATE TABLE Employee ( EmployeeID [int] NOT NULL, FirstName [varchar](50) NULL, LastName [varchar](50) NULL, Department [varchar](20) NULL, PRIMARY KEY ( EmployeeID ) ) |
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
1 2 3 4 5 6 7 8 | CREATE TABLE EmployeeLeave ( EmployeeID [int] NOT NULL, LeaveID [int] NOT NULL, DateTaken DateTime2] NOT NULL PRIMARY KEY ( 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
1 2 3 4 5 6 7 8 9 | CREATE TABLE EmployeeLeave ( EmployeeID [int] NOT NULL, LeaveID [int] NOT NULL, DateTaken [DateTime2] NOT NULL CONSTRAINT PK_EmployeeLeave PRIMARY KEY ( EmployeeID, LeaveID ) ) |
Naming a Primary Key
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE Employee ( EmployeeID [int] NOT NULL, FirstName [varchar](50) NULL, LastName [varchar](50) NULL, Department [varchar](20) NULL, CONSTRAINT PK_Employee PRIMARY KEY ( EmployeeID ) ) |
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).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Select C.TABLE_NAME, C.COLUMN_NAME, C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME WHERE C.TABLE_NAME='EmployeeLeave' and T.CONSTRAINT_TYPE='PRIMARY KEY' **Result** TABLE_NAME COLUMN_NAME CONSTRAINT_NAME ------------- ----------- ------------------------------ EmployeeLeave EmployeeID PK__Employee__FD4694664F7CD00D EmployeeLeave LeaveID PK__Employee__FD4694664F7CD00D |
If you want to find out only the constraint name and not fields use the following query
1 2 3 4 5 6 7 8 9 10 11 | SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'EmployeeLeave'; **Result** name ------------------------------ PK__Employee__FD4694664F7CD00D |
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.
1 2 3 | Alter Table EmployeeLeave Drop CONSTRAINT PK_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.
1 2 3 4 5 6 7 | CREATE TABLE EmployeeLeave ( EmployeeID [int] NOT NULL, LeaveID [int] NOT NULL, DateTaken [DateTime2] NOT NULL ) |
The following query creates Composite Primary Key on fields EmployeeID & LeaveID on table EmployeeLeave
1 2 3 4 5 6 7 8 | Alter Table EmployeeLeave Add PRIMARY KEY ( EmployeeID, LeaveID ) or Alter Table EmployeeLeave Add CONSTRAINT PK_EmployeeLeave PRIMARY KEY ( EmployeeID, LeaveID ) |
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
1 2 3 | Alter Table EmployeeLeave Drop CONSTRAINT PK_EmployeeLeave |
Then create the new one.
1 2 3 4 | Alter Table EmployeeLeave Add CONSTRAINT PK_EmployeeLeave PRIMARY KEY ( EmployeeID, LeaveID ) |
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.
1 2 3 4 5 6 7 8 9 | CREATE TABLE Employee ( EmployeeID [int] NOT NULL, FirstName [varchar](50) NULL, LastName [varchar](50) NULL, Department [varchar](20) NULL, CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED ( EmployeeID ) ) |
Or you can explicitly ask for the Clustered Index
1 2 3 4 5 6 7 | ... ... CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED ( EmployeeID ) ... ... |
The syntax is the same for Alter table query.
1 2 3 4 | Alter Table Employee Add CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED ( EmployeeID ) |
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.
1 2 3 4 5 6 7 8 | CREATE TABLE EmployeeLeave ( EmployeeID [int] NOT NULL, LeaveID [int] NOT NULL, DateTaken [DateTime2] NOT NULL, PRIMARY KEY (EmployeeID ASC, LeaveID DESC) ) |
