Detailed Description Of Primary Key In SQL Server


In this article, we will learn how to use the SQL SERVER PRIMARY KEY constraint to create a Primary Key for a table. A Primary Key is one of the most basic components on which a relational database theory is based. Primary keys enforce entity integrity by uniquely identifying entity instances.

Primary Key

The key points about Primary Key are as follow:
  • The PRIMARY KEY constraint uniquely identifies each record in a table.
  • Primary keys must contain UNIQUE values, and cannot contain NULL values.
  • A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
We can create a Primary Key either using SQL SERVER MANAGEMENT STUDIO or Transact-SQL (T-SQL). When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values. Here, I will briefly explain to you what is a clustered and non-clustered index, as it is connected to many primary key concepts.

Clustered Index

A clustered index defines the order in which data is physically stored in a table.
  1. --The following query retrieves the indexes created on the student table.  
  3. EXECUTE sp_helpindex student  
The output of the above query is:
Detail Description About Primary Key In SQL Server
In the output, you can see only one index. This is the index that was automatically created because of the primary key constraint on the “id” column. 

Non-Clustered Index

A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored in one place and table data is stored in another place.
  3. CREATE TABLE Emp (  
  4.    EmpID INT  
  5.   ,EmpFname VARCHAR(50)  
  6.   ,EmpLname VARCHAR(50)  
  7.   ,DeptID INT  
  8.   ,IsActive BIT  
  9.   )  
  10. --The below script creates a non-clustered index on the "EmpFname" column of the Emp table.  

  12. ON Emp(EmpFname ASC
You can view Non-clustered index either using Transact-SQL Query or using Object Explorer:
  1. exec sp_helpindex 'Emp' 
The output of the T-SQL Query, you can easily see index_name,index_description, and index_keys,
Detail Description About Primary Key In SQL Server
Using Object Explorer you can see Non-Clustered indexes
Detail Description About Primary Key In SQL Server
Now, let’s understand the usage of the Primary Key using an example.
STEP-1 - Applying Primary Key on a Single Column.
The following SQL creates a PRIMARY KEY on the "ID" column when the “Student" table is created:
  1. create table Student  
  2. (  
  3.    ID int NOT NULL identity(1,1) PRIMARY KEY,  
  4.    LastName varchar(255) NOT NULL,  
  5.    FirstName varchar(255),  
  6.    Age int  

You can see Output in the design Page, how the Primary Key exactly looks.
Detail Description About Primary Key In SQL Server
We can clearly see in column properties that in identity specification if we want to set identity the specification is “No”. It gives a Warning as mentioned below as well as sees if we want to remove the Primary key from the design section, then it will also give the same warning. There is no modification to the ID column till its Primary Key.
Detail Description About Primary Key In SQL Server
STEP 2 - Using Transact-SQL,you can DROP a Primary Key constraint.
First,you should know " the name of primary key" either Transact-SQL method or using Object Explorer.
Using Object Explorer,Right-Click on [Student] ______[Keys]______[PK__Student__3214EC273F466844]
Detail Description About Primary Key In SQL Server
Either using T-SQL Query,
  1. SELECT name    
  2. FROM sys.key_constraints    
  3. WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'Student';    
  4. GO   
We got the respected Output,
Detail Description About Primary Key In SQL Server
Now, we write T-SQL Query to drop the Primary Key Constraint.
  1. -- Drop the primary key constraint.    
  2. ALTER TABLE Student    
  3. DROP CONSTRAINT PK__Student__3214EC273F466844;     
  4. GO   
Command(s) completed successfully.
Now, Refresh the Object explorer, Primary Key is dropped.
STEP 3 - Adding Primary Key on already created table i.e., SQL Primary Key on ALTER Table
Suppose we created a table named 'salesman' in which we want 'salesman_id' as Primary Key.
  1. CREATE TABLE [salesman](  
  2. [salesman_id] [intNULL,  
  3. [name] [varchar](25) NULL,  
  4. [city] [varchar](25) NULL,  
  5. [commission] [floatNULL
 So, we have to do it either using the T-SQL Method or using Object Explorer.
  1. --Syntax to Add Primary Key on already Created Table.   
  3. ALTER TABLE Table_name    
  4. ADD PRIMARY KEY (column_name);     
  5. --Now Adding Primary Key on 'salesman' Table as 'salesman_id' as PK.    
  6. --Execute the below code  
  7. ALTER TABLE salesman    
  8. ADD CONSTRAINT PK_SalesHistoryArchive_SalesmanID PRIMARY KEY CLUSTERED (salesman_id);   
The output of the above-Executed Query,
The columns which we want to assign as PRIMARY KEY constraint must be defined as NOT NULL.
If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.
Now, we have set 'salesman_id' column as NOT NULL using T-SQL or using Object Explorer.
  1. ALTER TABLE table_name  
  2. ALTER COLUMN column_name datatype;   
  5.   salesman  
  7.   salesman_id  
  8.     int NOT NULL
 Now, execute the previous Query to create a Primary Key:
  1. ALTER TABLE salesman  
  2. ADD CONSTRAINT PK_SalesHistoryArchive_SalesmanID PRIMARY KEY CLUSTERED (salesman_id);  
  4. --To view Created Primary Key 
  5. exec sp_helpindex 'salesman' 
 Detail Description About Primary Key In SQL Server
Or, you can See Primary Key using Table Design Property in Object Explorer.
Detail Description About Primary Key In SQL Server
STEP 4 - Defining a PRIMARY KEY constraint on multiple columns
Suppose we create an Employee Table. The VALUE of the primary key is made up of TWO COLUMNS (EmpID + LastName), as there is only ONE PRIMARY KEY
(PK_Employee ). The primary key value depends on each other.
  1. CREATE TABLE Employee (  
  2.     EmpID int NOT NULL,  
  3.     LastName varchar(255) NOT NULL,  
  4.     FirstName varchar(255),  
  5.     Salary int,  
  6.     CONSTRAINT PK_Employee PRIMARY KEY (EmpID,LastName)  
  7. );   
You can see output either using Transact-SQL method or using Object Explorer:
  1. exec sp_helpindex 'Employee' 
 Detail Description About Primary Key In SQL Server
Using Object Explorer
Detail Description About Primary Key In SQL Server
Thank you for reading this article
I hope this article will help you to clear your information about the Primary Key. If you have any suggestions or questions, please free to ask below in the comments section.