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.

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)

, , ,
Previous Post
Does Dropping Primary Key Drop Clustered Index on the Same Column? – Interview Question of the Week #082
Next Post
How to Drop Clustered Index on Primary Key Column? – Interview Question of the Week #084

Related Posts

17 Comments. Leave new

  • 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
  • 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
  • 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
  • The question here is whether we can delete the index while retaining the primary key.

    Reply
    • 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
  • 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
  • Thanks Pinal.

    Can you please answer to the Shweta’s comment.

    Reply
  • Here is answer to your question: https://blog.sqlauthority.com/2016/08/14/drop-clustered-index-primary-key-column-interview-question-week-084/

    When you drop from SSMS, it generates the code to drop constraint which you can do it.

    Reply
  • I understood now. Thanks for the quick confirmation..

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

Leave a Reply

Menu