Can I change which index a primary key uses? I have a table with a non-clustered index on the id, and a clustered index on some other fields. The fields of the clustered index are non-sequential, and are frequently modified. I'd like to change the table to use the auto-increment id column for the clustered index.


I can't explicitly drop and recreate the index on the id, as it is used to enforce the PK.
I can't drop the PK, because it is referenced by foreign keys.
I would like to avoid dropping the foreign keys, because re-adding them will result in a constraint check on the existing rows, and there are a lot of rows.


I thought the following steps might work:


Drop the existing clustered index
Create a clustered index on the id
Somehow tell the table to use that index for enforcing the PK constraint
Remove the old non-clustered index
Rename the clustered index to match the convention


Is this possible? And if so, is it a good idea?
There are some limitations/restrictions when modifying existing Primary Keys (PK), Clustered Indexes (CI) and Foreign Keys (FK).

General Information

When a PK is created and no CI exists, the PK will create a CI if no other index is referenced in the constraint definition.


  The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.


Reference: Creating and Modifying PRIMARY KEY Constraints (Technet)

You can't drop/modify an existing PK if it is being referenced by a FK.


  A PRIMARY KEY constraint cannot be deleted if the following exist:  
  
  
  If it is referenced by a FOREIGN KEY constraint in another table; the FOREIGN KEY constraint must be deleted first.  
  The table has a PRIMARY XML index applied on it.
  


Reference: Creating and Modifying PRIMARY KEY Constraints (Technet)

Solution

You will have to drop the existing constraints (FK and PK) and/or indexes to create the solution you are aiming for.

This can be observed by reading the following articles:


How to convert clustered primary key to non-clustered without dropping referring foreign keys in SQL Server 2005 (Stackoverflow)
How to change the primary key to be non-clustered? (Stackoverflow)
Modify Foreign Key Relationships (Microsoft Docs)
Creating and Modifying PRIMARY KEY Constraints (Technet)