Can we have primary key and clustered index on same table with different field?
In this article I am going to explain what will happen if we try to create primary key on a table which is already having clustered index in it. Also this article is the answer to the question "Can we have primary key and clustered index on same table with different field?"
This is one of my favourite interview question. I thought I will explain this in detail here.
Answer for the question "Can we have primary key and clustered index on same table with different field" is YES we can create primary key on one field and clustered index on another field.
Let's start with step by step by creating table, index and primary key.
First we will create a simple Customer table like below with three fields.
Create TABLE [Customer](
[id] [int] not null,
[name] [nchar](10) NULL,
[Phone] [nvarchar](50) NULL
) ON [PRIMARY]
Second, we will add clustered index on Phone field.
CREATE CLUSTERED INDEX [Ph_Ind] ON Customer
(
Phone ASC
) ON [PRIMARY]
And finally we will create primary key
ALTER TABLE Customer
ADD CONSTRAINT pk_ID PRIMARY KEY (id)
All this query will work fine without any issue. Now we will check the table and see what and all are created.
sp_help Customer
You will get the details like below when you execute the above command,
If you look at the last section "constraint type" you will find that PRIMARY KEY (non-clustered) index has been created here.
What happened here is when you create primary key after creating clustered index, it will add non clustered index on that field.
I hope you are very clear about the explanation and concept.
Asheej very much useful information... Thanks for sharing such a good point