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.

How to Drop Clustered Index on Primary Key Column? - Interview Question of the Week #084 keyclindex-800x284

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.

Solarwinds

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

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

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

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

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)

Solarwinds
, ,
Previous Post
Does Dropping Clustered Index Drop Primary Key on the Same Column? – Interview Question of the Week #083
Next Post
Does Dropping Primary Key Drop Non-Clustered Index on the Column? – Interview Question of the Week #085

Related Posts

8 Comments. Leave new

  • Sanjay Monpara
    August 14, 2016 11:08 pm

    Hi pinal,
    In this post I read
    “Remember, it is not a good idea to have a table with clustered index or primary key as they are very critical element in RDBMS for database integrity and performance.”

    Could you give say something more about it or give reference of any related post?

    Reply
  • wilfred van Dijk
    August 15, 2016 12:19 pm

    I think it should be WITHOUT CLUSTERED INDEX instead of WITH CLUSTERED INDEX

    Reply
  • Hi Pinal,
    Ur statement dont go well with RDBMS>
    “Remember, it is not a good idea to have a table with clustered index or primary key as they are very critical element in RDBMS for database integrity and performance.”

    Please if you wanted to say something else or guide us how ur statement is correct.
    Thanks

    Reply
    • Hi Ashok,

      I think what I meant to say was 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.

      I think I had typo there. I have made the correction. I hope now the statement is clear.

      Reply
  • “How to Drop Clustered Index on Primary Key Column?” and the answer is you can not;
    what you have suggested is to drop “Primary Key” it self.

    Reply
  • HI Pinal,
    I am totally agree with you.

    Vineet Goyal

    Reply
  • Mohammed Zaid Meraj
    October 5, 2017 1:28 pm

    getting error like
    Msg 3727, Level 16, State 0, Line 2
    Could not drop constraint. See previous errors

    Reply
  • Alter statement still gives an error i.e.,
    Could not drop the primary key constraint ‘PK_Table1_Col1’ because the table has an XML or spatial index.

    Reply

Leave a Reply

Menu