Detailed Description Of Primary Key In SQL Server

Introduction 

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

SQL Server Primary Key

The key points about Primary Key are as follows.

• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values and cannot have 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).

Note

We can create a Primary Key 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 what a clustered and nonclustered index is, as it is connected to many primary key concepts.

Clustered Index in SQL Server

A clustered index defines the order in which data is physically stored in a table.

--The following query retrieves the indexes created on the student table.  
  
EXECUTE sp_helpindex student  

The output of the above query is.

In the output, you can see only one index. This index was automatically created because of the primary key constraint on the "id" column. 

Nonclustered Index in SQL Server

A nonclustered index doesn't sort the physical data inside the table. A nonclustered index is stored in one place, and table data is stored in another.

--CREATE A EMP TABLE  
  
CREATE TABLE Emp (  
   EmpID INT  
  ,EmpFname VARCHAR(50)  
  ,EmpLname VARCHAR(50)  
  ,DeptID INT  
  ,IsActive BIT  
  )  
--The below script creates a non-clustered index on the "EmpFname" column of the Emp table.  

CREATE NONCLUSTERED INDEX IX_tblEmp_Name  
ON Emp(EmpFname ASC) 

You can view the Nonclustered index either using Transact-SQL Query or using Object Explorer:

exec sp_helpindex 'Emp' 

In the output of the T-SQL Query, you can easily see index_name,index_description, and index_keys,

Nonclustered indexes using Object Explorer

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:

create table Student  
(  
   ID int NOT NULL identity(1,1) PRIMARY KEY,  
   LastName varchar(255) NOT NULL,  
   FirstName varchar(255),  
   Age int  
) 

You can see Output on the design Page, how the Primary Key exactly looks.

We can 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, and removing the Primary key from the design section will also provide the same warning. There is no modification to the ID column till its Primary Key.

Step 2. Using Transact-SQL, you can DROP a Primary Key constraint.

First, you should know "the name of the primary key" either the Transact-SQL method or usingObject Explorer.

Using Object Explorer,Right-Click on [Student] ______[Keys]______[PK__Student__3214EC273F466844]

OR

Either using T-SQL Query, 

SELECT name    
FROM sys.key_constraints    
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'Student';    
GO  

We got the respected Output,

Now, we write T-SQL Query to drop the Primary Key Constraint.  

-- Drop the primary key constraint.    
ALTER TABLE Student    
DROP CONSTRAINT PK__Student__3214EC273F466844;     
GO 

Command(s) completed successfully.

Now, Refresh the Object explorer; Primary Key is dropped.

Step 3. Adding Primary Key on the 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.

CREATE TABLE [salesman](  
[salesman_id] [int] NULL,  
[name] [varchar](25) NULL,  
[city] [varchar](25) NULL,  
[commission] [float] NULL) 

 So, we must do it using the T-SQL Method or Object Explorer.

--Syntax to Add Primary Key on already Created Table.   
  
ALTER TABLE Table_name    
ADD PRIMARY KEY (column_name);     
--Now Adding Primary Key on 'salesman' Table as 'salesman_id' as PK.    
--Execute the below code  
ALTER TABLE salesman    
ADD CONSTRAINT PK_SalesHistoryArchive_SalesmanID PRIMARY KEY CLUSTERED (salesman_id);   

The output of the above-Executed Query,

Note

The columns we want to assign as PRIMARY KEY constraints 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.

We have set the 'salesman_id' column as NOT NULL using T-SQL or Object Explorer.

ALTER TABLE table_name  
ALTER COLUMN column_name datatype;   
  
ALTER TABLE  
  salesman  
ALTER COLUMN  
  salesman_id  
    int NOT NULL; 

Now, execute the previous Query to create a Primary Key.

ALTER TABLE salesman  
ADD CONSTRAINT PK_SalesHistoryArchive_SalesmanID PRIMARY KEY CLUSTERED (salesman_id);  
  
--To view Created Primary Key 
exec sp_helpindex 'salesman' 

Or, you can See Primary Key using Table Design Property in Object Explorer.

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.

CREATE TABLE Employee (  
    EmpID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Salary int,  
    CONSTRAINT PK_Employee PRIMARY KEY (EmpID,LastName)  
);   

You can see output either using the Transact-SQL method or using Object Explorer.

exec sp_helpindex 'Employee' 

 

Using Object Explorer

Summary

I hope this article will help you clear your primary key information. If you have any suggestions or questions, please ask in the comments section below. Thank you for reading this article