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