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

7 years ago

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

Answer: This is one of the most popular questions, whenever I ask this to any candidate, they usually give an answer in yes or no. However, if I ask them follow up question to confirm, I have noticed most of the time they change the reply or accept that they really do not know the answer. It is totally fine. As when I was beginning with SQL, I even did not know the answer of it. I learn doing various experiments and doing various interviews.

Well, the answer in one simple statement – “When you drop the primary key constraint on a column where there is a clustered index, it will drop clustered index along the clustered index as well.“

Here is a small reproduction script.

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 primary key constraint. Please note that we are not dropping the clustered index, we are just dropping primary key constraint on the column where there is a clustered index.

-- Drop Primary Key Constraint
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

Now, once again, let us run two scripts where we can see the primary key and 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

This time you can see that results are empty.

Let us clean up the created table for our experiment.

-- Clean up 
DROP TABLE Table1
GO

Summary: From the example it is very clear that when we drop the primary key constraint on a column where there is a clustered index, it will drop clustered index on the table as well.

Let me know what you think about this blog post. Leave your favorite interview question in the comment and I will do my best to answer in the future blog posts.

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

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

How to Find Outdated Statistics? – Interview Question of the Week #081

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

11 Comments. Leave new

Kevin Fries
August 1, 2016 4:33 am
Note: I pulled the code for the most part from one of Klaus Aschenbrenner’s posts.
I’ll preface this by stating that in many of my systems, the primary key is a composite and not necessarily the same as the clustered key.
This is my understanding of how it works. What you wrote was a tad confusing in that dropping a composite primary key (PK) or a PK on a single column will leave the clustered key intact if the clustered key does not have the same columns.
I realize you know this but this part is for the other readers. A primary key is not necessarily the same key as the clustered key on a table. If you choose to use a unique identifier for the clustered index and specify the PK as nonclustered like the below:
— Create the Primary Key constraint on an ever-increasing
— key column
CREATE TABLE Foo2
(
Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
Col2 UNIQUEIDENTIFIER NOT NULL,
Col3 INT NOT NULL
)
GO
— Create the Clustered Index on a random key column
CREATE UNIQUE CLUSTERED INDEX ci_Col2 ON Foo2(Col2)
GO
Query sys.indexes and you’ll see the primary and clustered keys
Then drop the primary key.
ALTER TABLE [dbo].[Foo2] DROP CONSTRAINT [PK__Foo2__A259EE553CA2B97A]
GO
The clustered key will still exist.
Thanks for giving me the many opportunities to learn from you. Now you also know that I give interviewers a hard time with their questions.
Reply
- Pinal Dave
  August 1, 2016 6:37 am
  Thanks for your comment.
  The discussion which I had in the blog post is in the same column, which is primary key and clustered index. This does not apply for any system where PK and CI are on different columns.
  This is actually the next part of the question which I am currently writing for the upcoming week.
  I appreciate adding it here.
  Reply
vaibhav shringi
August 1, 2016 11:46 am
Primary key is always bound to an clustered or non-clustered index thus dropping key will drop the index too. So your question and answer will only work if we are creating an primary key with clustered index (which is default for heap/new table).
Note: We can have both clustered and non-clustered index on same column.
Creation of primary key on a column of a table which already have a clustered index on the same column, will create a separate non-clustered index. So any future dropping attempts will only drop the new non-clustered index.
Reply
Dave Poole
August 4, 2016 9:26 pm
I feel the terminology used is a bit confusing in this post.
If the Primary Key is the clustered key then dropping the primary key will drop the clustered key because they are one and the same.
If the Primary Key is not the clustered key and you have a separate clustered index on exactly the same columns then dropping the primary key does not drop the clustered index.
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’dbo’ AND TABLE_NAME=’TestTable’)
BEGIN
DROP TABLE dbo.TestTable
PRINT ‘DROPPED TABLE: dbo.TestTable’
END
GO
CREATE TABLE dbo.TestTable (
TestTableID INT NOT NULL IDENTITY(1,1)
CONSTRAINT pk_TestTable PRIMARY KEY NONCLUSTERED,
TestTableName varchar(50) not null
constraint UQ_TestTable unique
)
GO
SELECT
TestName=’No Clustered index created yet’,
Table_Name=OBJECT_NAME(object_id),
IndexName=name,
type_desc
FROM sys.indexes
WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
AND OBJECT_NAME(object_id)=’TestTable’ AND OBJECT_SCHEMA_NAME(object_id)=’dbo’
GO
CREATE CLUSTERED INDEX idx_TestTable_Clustered ON dbo.TestTable(TestTableID)
GO
SELECT
TestName=’Clustered index created’,
Table_Name=OBJECT_NAME(object_id),
IndexName=name,
type_desc
FROM sys.indexes
WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
AND OBJECT_NAME(object_id)=’TestTable’ AND OBJECT_SCHEMA_NAME(object_id)=’dbo’
GO
ALTER TABLE dbo.TestTable DROP CONSTRAINT pk_TestTable
SELECT
TestName=’Primary Key Dropped’,
Table_Name=OBJECT_NAME(object_id),
IndexName=name,
type_desc
FROM sys.indexes
WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
AND OBJECT_NAME(object_id)=’TestTable’ AND OBJECT_SCHEMA_NAME(object_id)=’dbo’
GO
Reply
Amit Srivastava
August 12, 2016 12:09 pm
Two Questions –
1. What is the reason of drop of CLUSTERED INDEX on deleting PK?
2. Is this applicable for Non CLUSTERED Index as well
Reply
Pinal Dave
August 17, 2016 1:57 pm
Hi Amit,
1) The question is answer here: https://blog.sqlauthority.com/2016/07/31/dropping-primary-key-drop-clustered-index-column-interview-question-week-082/#comment-1969011
2) Yes, it does the same for non clustered index
Reply
M McDonald
August 24, 2016 4:04 pm
Same comment as Dave Poole. However, this is a slight modification of your scripts…
— 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 Primary Key Constraint
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
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
The clustered index remains.
Reply
haidder
December 14, 2016 9:00 pm
Hi Pinal, Hope you are well. One quick question, what is the best practice when you are getting data in multiple language and wanted to load in one data warehouse. I appreciate this question is not related to the above topic but hopefully, you can give your valuable opinion.
Many thanks.
Mustafa
Reply
- Pinal Dave
  December 25, 2016 5:31 pm
  In SQL Server, you can store multilingual data in column by using Unicode data types like nvarchar. books online have a lot of information on this.
  Reply
  - haidder
    December 26, 2016 1:37 am
    Thank you Pinal for your reply.
Avi
September 26, 2017 5:24 pm
“When you drop the primary key constraint on a column where there is a clustered index, it will drop clustered index along the clustered index as well.“
“it will drop clustered index along the clustered index as well.“,Not able to get this,Clustered index along with clustered index?
Reply