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

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.

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)