SQL Server Performance Forum – Threads Archive

Converting Non-Clustered Primary Key to Clustered

I have a primary key defined as a non-clustered index and is used to enforce FK references. When trying to flip the non-clustered to clustered in EM I get the message "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option". When I try to delete the index I get the message "An explicit DROP INDEX is not allowed <tablename><constraint>. It is being used for PRIMARY KEY constratint enforcement". Any idea how I can get around this without having to drop all FK references.

Through EM,
Goto design Table & Manage Indexes.
Change the created non-clustered index to clustered index by checking create as clustered checkbox.
I think it should run.
It will then save the referenced table too.

May look at thishttp://www.sqlservercentral.com/columnists/RDyess/bookmarklookups_printversion.asp link for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

So you apparently also have a clustered index defined on other column(s)?
If there aren’t too many rows in the table, I would use EM to identify the clustered index and drop it and then change your PRIMARY KEY to clustered as Ranjit already suggested. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Ranjit, I had tried what you suggested but got the error mentioned in my original query. Frank, there is no other clustered index on the table.

Are you sure? When you don’t explicitely create your PRIMARY KEY as nonclustered, SQL Server will make it the clustered index. Can you post the table structure along with indexes, constraints…? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>