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

Question: How to drop clustered index, which is created on primary key?

Answer: If you thought the answer as simple as following script, you are wrong.

DROP INDEX PK_Table1_Col1
ON Table1
GO

When you run above script on Table1 where PK_Table1_Col1 is clustered index it will throw an 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.

Here are two blog posts which are related to the concept described in this blog post, I suggest you read them before you continue with this blog post as that will help you understand the subject of Primary Key and Clustered Index a bit more in detail.

• Does Dropping Primary Key Drop Clustered Index on the Same Column?
• Does Dropping Clustered Index Drop Primary Key on the Same Column?

So question still remains, How do we drop clustered index on primary key column?

The answer is very simple, but first we will go over the entire script which will demonstrate to us that we have created a clustered index and primary key on the table.

First, let us create a table.

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

Next, check if table has a primary key and clustered index on the same column with the help of following a script.

-- 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 attempt to drop a clustered index with drop script, which will give an error.

-- Drop Clustered Index
DROP INDEX PK_Table1_Col1   
    ON Table1
GO

The script listed above will give us 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.

Now it is clear that we are not able to drop the clustered index as there is a primary key. Now if you want to drop a clustered index, you will have to drop it with the help of following script where we drop constraint on the same column.

-- Drop Constraint
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

The above script will give us success message.

Now let us run following script and double check that table does not have either primary key or clustered index.

-- 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

You will notice that if we drop primary key on the clustered index column, it also automatically drops the clustered index.

Remember, it is not a good idea to have a table without clustered index or primary key as they are very critical element in RDBMS for database integrity and performance.

Now you can execute the following script to clean up the table which we have created for demonstration purpose.

-- Clean up
DROP TABLE Table1
GO

I am very sure that you have more question once you read this blog post as well as this and this blog post. I suggest you leave a comment and I will address them in the future blog post.

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