Change which index a primary key uses

azure-sql-databaseclustered-indexprimary-key

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?

Best Answer

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)

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Best Answer

General Information

Solution

Related Solutions

Sql-server – How does SQL Server choose an index key for a foreign key reference

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

Related Question