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?