What is Primary Key Constraint in SQL Server Database - SQL Server / T-SQL Tutorial Part 54

What is Primary Key Constraint:

Primary Key Constraint is created to ensure uniqueness of records/rows. Primary Key Constraint can be created on single or set of columns as long as the column/columns do not allow Null values.

1) Primary Key Constraint creates Clustered Index by default if does not exist already
Let's say that you are creating new table and you have added column with Primary Key, it is going to create clustered index.

In below example we are creating table dbo.Customer and we have added CustomerId as Primary Key Constraint. Let's check what type of index is created.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


We can query sys.indexes system view to get index information 

SELECT *
FROM sys.indexes
WHERE object_name(object_id) = 'Customer'


As you can see that Clustered index is created as there was no cluster index on this table before.Noticed the name for Clustered Index, it is generate by SQL Server. It is always good idea to provide proper name for indexes according to naming convention/ Standards your company is using.


2) If clustered index is already created on Column, Primary Key is going to Create Unique Index
If clustered index is already present on column/columns in a table and we try to create Primary Key, it will create Unique Non-clustered.

Let's drop and re-create dbo.Customer table. As we can not create Clustered Index on columns which accept Null values, we have to define our Columns Not Null.

USE YourDatabaseName
GO
Drop table dbo.Customer
GO

CREATE TABLE dbo.Customer (
    Customerid INT Not Null
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10) Not Null
    )

--Create Clustered Index on SSN Column
CREATE CLUSTERED INDEX IX_Customer_SSN 
    ON dbo.Customer (SSN); 


--Create Primary Key on CustomerID
Alter Table dbo.Customer
Add Constraint Pk_Customer_CustomerId Primary Key(CustomerId)




Let's check what type of indexes are created by using sys.indexes system view. There should be one Clustered Index as we created on SSN and there should be one unique nonclustered that should be created by Primary Key Constraint.

What is Primary Key Constraint in SQL Server - SQL Server / TSQL Tutorial 


Video Demo : What is Primary Key Constraint in SQL Server and How to Create Primary Key

No comments:

Post a Comment