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,

Table Structure

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.


More articles: Clustered Index Primary Key

Comments

Author: suchit shah18 Jan 2012 Member Level: Silver   Points : 0

Asheej very much useful information... Thanks for sharing such a good point

Guest Author: Joseph Gomez17 Oct 2012

Thanks for sharing such a useful information, I will be checking your blog for further information and updates.

Guest Author: College Papers28 Dec 2012

I am saving your article in my favorite’s folder to visit again You made valid points within this content that I feel need further exploration. I agree with most all of this info. Great work.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: