Question: Does dropping a primary key drop the clustered index on the same column?
Answer: It drops the index that enforces that primary-key constraint. If the primary key is clustered, that clustered index goes with it. A separately created clustered index does not disappear merely because it uses the same column.
This is a favorite follow-up question of mine. Candidates often start with a confident yes or no, then reconsider when I ask them to explain the relationship. That’s fine. I also learned it through experiments when I was starting with SQL Server.
Create a PRIMARY KEY CLUSTERED and then drop its constraint, and both the constraint and its enforcing index disappear. That’s correct for that case. The wording “on the same column” makes it sound broader than it is.
Try the Common Case and Its Boundary
The following temporary-table test uses a small Col1/Col2 table. The second table makes the primary key nonclustered and creates a separate clustered index on exactly the same column:
IF OBJECT_ID('tempdb..#SqlaPk39271') IS NOT NULL
OR OBJECT_ID('tempdb..#SqlaSeparate39271') IS NOT NULL
THROW 50001, 'A sample table already exists in this session.', 1;
DECLARE @States TABLE (Step nvarchar(80), PrimaryKeys int, ClusteredIndexes int);
CREATE TABLE #SqlaPk39271
(Col1 int NOT NULL, Col2 varchar(100) NULL,
CONSTRAINT PK_Sqla39271 PRIMARY KEY CLUSTERED (Col1));
INSERT #SqlaPk39271 VALUES (1,'One'),(2,'Two');
INSERT @States
SELECT N'Clustered PK: before drop',
(SELECT COUNT(*) FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#SqlaPk39271') AND type = 'PK'),
(SELECT COUNT(*) FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaPk39271') AND type = 1);
ALTER TABLE #SqlaPk39271 DROP CONSTRAINT PK_Sqla39271;
INSERT @States
SELECT N'Clustered PK: after drop',
(SELECT COUNT(*) FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#SqlaPk39271') AND type = 'PK'),
(SELECT COUNT(*) FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaPk39271') AND type = 1);
-- Same column, but two independent indexes.
CREATE TABLE #SqlaSeparate39271
(Col1 int NOT NULL, Col2 varchar(100) NULL,
CONSTRAINT PK_SqlaSeparate39271 PRIMARY KEY NONCLUSTERED (Col1));
CREATE CLUSTERED INDEX CX_SqlaSeparate39271 ON #SqlaSeparate39271(Col1);
INSERT @States
SELECT N'Separate clustered index: before PK drop',
(SELECT COUNT(*) FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#SqlaSeparate39271') AND type = 'PK'),
(SELECT COUNT(*) FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaSeparate39271') AND type = 1);
ALTER TABLE #SqlaSeparate39271 DROP CONSTRAINT PK_SqlaSeparate39271;
INSERT @States
SELECT N'Separate clustered index: after PK drop',
(SELECT COUNT(*) FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#SqlaSeparate39271') AND type = 'PK'),
(SELECT COUNT(*) FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaSeparate39271') AND type = 1);
SELECT * FROM @States;
SELECT Col1, Col2 FROM #SqlaPk39271 ORDER BY Col1;
DROP TABLE #SqlaPk39271, #SqlaSeparate39271;
For the clustered primary key, the first two rows show the primary-key count changing from one to zero and the clustered-index count also changing from one to zero. The data rows are still present; the table has become a heap.
For the independently created clustered index, the last two state rows show the primary key disappearing while the clustered index remains. Same column, different relationship.
Ask Which Index Belongs to the Constraint
For a real table, sys.key_constraints.unique_index_id identifies the enforcing index in sys.indexes. Don’t rely only on a familiar name or a list of indexed columns. A primary key can be clustered or nonclustered, and its logical uniqueness rule is distinct from the table’s physical storage choice.
This is a sample-table experiment, not a production change script. Foreign keys and other dependencies can prevent a primary-key drop. Removing uniqueness also changes what future data can be accepted. Check those dependencies and the intended replacement before changing an existing table.
If you want another interview question covered, leave it in the comments. The useful part of this one is explaining why the first answer works and where it stops working.
Dropping a primary key is not dropping every index on its column, it is dropping the one index that enforces it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
11 Comments. Leave new
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.
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.
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.
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
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
Hi Amit,
1) The question is answer here: http://blog.sqlauthority.com/2016/07/31/does-dropping-primary-key-drop-clustered-index-on-the-same-column-interview-question-of-the-week-082/#comment-1969011
2) Yes, it does the same for non clustered index
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.
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
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.
Thank you Pinal for your reply.
“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?