Drop Clustered index

Question

Drop Clustered index

sunny.tjk

SSCertifiable

Points: 5041
More actions
March 22, 2010 at 12:43 pm

#139434

I'm trying to drop the clustered index on a table, but it's giving me the below error.
An explicit DROP INDEX is not allowed on index 'dbo.PAYMENTS_tmp.PK_Payments_tmp'. It is being used for PRIMARY KEY constraint enforcement.
Can anyone please tell me why am i not allowed to drop it and how can i drop it?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

sunny.tjk (3/22/2010)
I'm trying to drop the clustered index on a table, but it's giving me the below error.
An explicit DROP INDEX is not allowed on index 'dbo.PAYMENTS_tmp.PK_Payments_tmp'. It is being used for PRIMARY KEY constraint enforcement.
Can anyone please tell me why am i not allowed to drop it and how can i drop it?
Thanks.

You would have to remove the primary key first. And prior to that, if the key is used in any relationships (FK to another table), then you will need to remove those as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

I deleted the foreign key but I'm still not able to delete the primary key. Is it any way connected to the unique keys that I've on 3 columns?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

sunny.tjk (3/22/2010)
I deleted the foreign key but I'm still not able to delete the primary key. Is it any way connected to the unique keys that I've on 3 columns?

A Primary Key is a Unique key. Have you created an additional UK on those columns?

When trying to drop your PK, what message are you getting?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

If it is really a primary key, then you would not use the DROP INDEX statement.

You would use ALTER TABLE tablename DROP CONSTRAINT constraint_name

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Michael Valentine Jones (3/22/2010)
If it is really a primary key, then you would not use the DROP INDEX statement.
You would use ALTER TABLE tablename DROP CONSTRAINT constraint_name

Good idea to point it out so clearly.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 6

Great!! The ALTER statement worked. i always thought that I could right click on the index and then delete it from Management studio.

I'll remember this.

Thanks a lot everyone.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events