Does Dropping Clustered Index Drop Primary Key on the Same Column? – Interview Question of the Week #083

August 7, 2016

Question: When we drop clustered index on the column, does it automatically drop primary key on the same column (if created)?

Answer: This is a second follow up question I often get after earlier question discussed over here: Does Dropping Primary Key Drop Clustered Index on the Same Column? – Interview Question of the Week #082. Before you continue this blog post, I would like to request you to read the blog post here first as they both are very much related.

Does Dropping Clustered Index Drop Primary Key on the Same Column? - Interview Question of the Week #083 indexandkey-800x285

Here is a simple answer to this question – No.

It is not possible to drop clustered index if there is a primary key on the same table. If your primary key is in a different column, then clustered index, you can for sure manipulate them.

If you try to drop clustered index on the column, which is also primary key, it will give you an error. Let us see that in a quick demonstration.

First, let us create a sample table.

-- Create Table
CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY CLUSTERED (
Col1 ASC)
)
GO

Next let us check the primary key on the table with following script. Along with that we will also check clustered index on the same table.

-- Check the Name of Primary Key
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' 
		AND OBJECT_NAME(parent_object_id) = N'Table1'
GO

-- Check the Clustered Index 
SELECT OBJECT_NAME(object_id),name
FROM sys.indexes 
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1
		AND type_desc='CLUSTERED'
		AND OBJECT_NAME(object_id) = N'Table1'
GO

Now let us drop the clustered index and not the primary key constraint.

-- Drop Clustered Index
DROP INDEX PK_Table1_Col1   
    ON Table1;

When we try to drop above clustered index it will throw following error.
Msg 3723, Level 16, State 4, Line 26
An explicit DROP INDEX is not allowed on index ‘Table1.PK_Table1_Col1’. It is being used for PRIMARY KEY constraint enforcement.

In a simple words, we can’t drop clustered index if it is on Primary Key. We will have to remove the primary key first if we want to drop clustered index.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Clustered Index, SQL Constraint and Keys, SQL Index, SQL Server

Does Dropping Primary Key Drop Clustered Index on the Same Column? – Interview Question of the Week #082

How to Drop Clustered Index on Primary Key Column? – Interview Question of the Week #084

18 Comments. Leave new

Shweta
August 7, 2016 2:21 pm
Hi Pinal,
I have a doubt here. We are not allowed to drop clustered index if it is on Primary Key by using SQL syntax DROP INDEX TableName.IndexName. However, We can do it from Object Brower Window. Just right click on the index and delete it. It will be deleted even if it is on primary key. And also, Primary key will also be deleted. So according to me answer should be ‘Yes’.
Reply
ѕι๋∂∂нєѕн
August 8, 2016 8:58 am
Hi,
We can create primary key as non-clustered index too, so answer is No. When we created Primary key index on a table, it will automatically create index on a table with same name (but work as a constraint). We cannot drop index individually, it will give below error as mentioned in post. For this we have to drop constraint. This will remove Primary Key constraint & index.
Dropping Clustered Index Drop Primary Key on the Same Column? – No
Dropping Primary Key Drop Index on the Same Column? – Yes
Reply
Balan chinnappan
August 8, 2016 11:11 pm
I have the same question too, We can still drop the Cluster index from SSMS(It will remove both cluster index and PK constraint). Its not possible only through Command.
Reply
Deepak
August 13, 2016 8:59 am
Hi Pinal,
You are correct, explicit drop is not allowed,
however, I right clicked on the index and select “Delete”. It actually allows me to delete the clustered index and it also removes primary key constraint.
So can I consider this, right click and deleting things from SQL is as good as drop command. If yes, we can drop clustered index.
Kindly provide your review.
Reply
Deepak
August 13, 2016 9:18 am
I think I got my answer,
I checked the script in separate window and I found it actually drops the constraint which then drops index as well.
Script is
ALTER TABLE [Emp]
DROP CONSTRAINT [PK_01]
GO
Reply
Lokesh Sharma
August 24, 2016 3:50 pm
The question here is whether we can delete the index while retaining the primary key.
Reply
- Pinal Dave
  August 28, 2016 6:11 pm
  You can recreate, only non-clustered index if you want and keep that index. However, when you create a Primary Key with Clustered index, when you drop PK or Remove CL it removes both.
  Reply
M McDonald
August 24, 2016 4:16 pm
Here, like last week, the “key” is how the PK & CI are created — almost like a trick question… If they are created separately, one can be dropped without the other
— Create Table and nonclustered PK
CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY NONCLUSTERED (
Col1 ASC)
)
GO
–Create clustered index
create clustered index ci_Table1_Col1 on Table1
(Col1)
— Check the Name of Primary Key
SELECT name
FROM sys.key_constraints
WHERE type = ‘PK’
AND OBJECT_NAME(parent_object_id) = N’Table1′
GO
— Check the Clustered Index
SELECT OBJECT_NAME(object_id),name
FROM sys.indexes
WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
AND type_desc=’CLUSTERED’
AND OBJECT_NAME(object_id) = N’Table1′
GO
— Drop Clustered Index
DROP index ci_Table1_Col1 on Table1
GO
— Check the Name of Primary Key
SELECT name
FROM sys.key_constraints
WHERE type = ‘PK’ AND OBJECT_NAME(parent_object_id) = N’Table1′
GO
— Check the Clustered Index
SELECT OBJECT_NAME(object_id),name
FROM sys.indexes
WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
AND type_desc=’CLUSTERED’
AND OBJECT_NAME(object_id) = N’Table1′
GO
— Clean up
DROP TABLE Table1
GO
Reply
- Pinal Dave
  August 24, 2016 4:26 pm
  Thanks for your valuable input. It is so much fun to receive the details from you.
  Reply
Ramkoti
August 28, 2016 5:54 pm
Thanks Pinal.
Can you please answer to the Shweta’s comment.
Reply
Ramkoti
August 28, 2016 6:16 pm
I understood now. Thanks for the quick confirmation..
Reply
Usha
February 3, 2017 5:22 pm
Hi Pinal,
Could you please clarify my doubt here? What I’m trying to achieve is retaining a primary key while removing the non clustered index created on it. And then create a clustered index on the same column. How can I do that?
Here is the sample queries I tried.
— Create Table
CREATE TABLE dbo.Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY NONCLUSTERED (
Col1 ASC)
)
GO
— Create Clustered Index on Separate Column
CREATE CLUSTERED INDEX IDX_Table1_CL ON dbo.Table1(Col2)
GO
DROP INDEX IDX_Table1_CL ON dbo.Table1
GO–Removed clustured index IDX_Table1_CL successfully
SELECT name FROM sys.key_constraints WHERE type = ‘PK’ AND OBJECT_NAME(parent_object_id) = N’Table1′
SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Table1]’) AND name = N’PK_Table1_Col1′
/*Primary key and nonclustered Index are there*/
DROP INDEX PK_Table1_Col1 ON dbo.Table1
GO
–Getting following error here.
/*
An explicit DROP INDEX is not allowed on index ‘dbo.Table1.PK_Table1_Col1’. It is being used for PRIMARY KEY constraint enforcement.*/
Your assistance in this matter would be greatly appreciated.
Reply
- Pinal Dave
  February 7, 2017 4:02 am
  ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [PK_Table1_Col1]
  GO
  Reply
Ajay Narvekar
March 9, 2021 3:20 pm
You can drop the clustered index without affecting the primary key if that clustered index is not associated with the primary key. If the clustered index is created separately, the creation of primary key will obviously create a nonclustered index. Dropping the clustered index then won’t affect the primary key. Moreover, if you drop the primary key, it will drop the nonclustered index created along with primary key and won’t affect the clustered index on the same column.
Reply