SQL Server: Alter a clustered primary index to an non-clustered

Question

How do I alter a primary clustered index to become a non-clustured index. (Being a "secondary" table I want to use the clustured index for the foreign key column of the "header" table.)

This doen't work for me (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.

Answer 1

I think you will have to:

1. Drop the FK
2. Drop the PK
3. Drop the clustered index
4. Recreate the PK
5. Recreate the FK
6. Recreate the clustered index on the FK column

and then optionally create a secondry index on the PK column

Answer 2

Have you tried dropping the PRIMARY KEY constraint on the table as well - then dropping the index - then re-adding both?

ALTER TABLE table_name DROP primary key