Geek Answers Handbook

SQL Server: Change Cluster Primary Index to Nonclustered

How to change a primary clustered index to become an index without clustering. (Being a "secondary" table, I want to use an indexed index for the foreign key column of the header table.)

This does not work for me (the error seems reasonable :)

DROP INDEX ClientUsers.PK_ClientUsers CREATE UNIQUE CLUSTERED INDEX IDX_ClientUsers_Id ON ClientUsers(Id) Msg 3723, Level 16, State 4, Line 7 An explicit DROP INDEX is not allowed on index 'ClientUsers.PK_ClientUsers'. It is being used for PRIMARY KEY constraint enforcement.
+4
source share
2 answers

I think you will have to:

  • Drop FK
  • Drop PK
  • Drop Clustered Index
  • Repair PK
  • Rebuild fk
  • Restore Cluster Index in FK Column

and then optionally create a secondary index index in the PK column

+4
source

Have you tried resetting the PRIMARY KEY constraint in the table and then resetting the index and then re-adding both?

 ALTER TABLE table_name DROP primary key
+1
source

More articles:


All Articles