Difference Between UNIQUE/ PK/CLUSTERED
Introduction
Primary Key (PK) of a table object is containing CLUSTERED INDEX by default. So the confusion arises between PK – CLUSTERED INDEX and UNIQUE CLUATERED INDEX.
In this article we are trying to solve this confusion.
So what is That?
UNIQUE Constraint means it takes the Unique Records on the columns and with this It Supports one NULL value.
UNIQUE Constraint = Unique Records in Columns + One NULL Value
CLUSTERED means, How data the is Arranged in the Disk.
Where have INDEX in different type
1. UNIQUE CLUSTERED Index
2. Non UNIQUE Clustered Index
3. UNIQUE NON CLUSTERED Index
4. NON UNIQUE NON CLUSTERED Index
If we think about the PRIMARY KEY (PK) by default it takes CLUSTERED Index. So the Primary Key is UNIQUE and NOT Support any NULL VALUES.
Primary Key (PK) = UNIQUE + NOT NULL + CLUSTERED Index (By Default)
Here we must remember that the CLUSTERED Index is not the feature of Primary key. The Clustered Index is created automatically when we create the Primary Key. We can create Primary Key without any CLUSTERED Index also (Not a good Practice).
So a primary key support only UNIQUE value and NO NULL is allowed here.
For UNIQUE CLUSTERED or NON CLUSTERED Index One NULL value is allowed.
A table object cannot have more than one Primary Key but have multiple Unique Indexes.
