2

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?

3

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.