How to convert clustered primary key to non-clustered without dropping referring foreign keys in SQL Server 2005

Question

I've made mistake creating clustered primary key on GUID column. There are many tables that reference that table with defined foreign keys. Table size is not significant.

I would like to convert it from clustered to non-clustered without manually dropping and recreating any foreign keys or even primary key constraint.

Is it possible to achieve that in MS SQL2005 and how if yes ?

Is it possible to achieve that ONLINE (without db down time) if yes ?

Answer 1

You could try creating the unique nonclustered NC index first, then drop the clustered PK. The FK should recognise this other index (but might not: never tried it).

When you run ALTER TABLE to drop the clustered PK use the ONLINE option. However, it's only available in Enterprise edition.

ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable WITH (ONLINE = ON)

You can't use ONLINE for the ADD CONSTRAINT bit.

Basically, your options are limited without blocking, or creating another table first and moving data over...