How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity

Question

After discovering, that I better change the primary keys from non clustered to clustered. Cf this question about clustered indexes on identity columns, the next step is how to do this in a prcatical way.

I was surprised that I didn't found this question on SO.

Having referential references between my tables, I can't simply do

if object_id('MyChild') > 0 drop table MyChild
if object_id('Mytable') > 0 drop table Mytable
create table Mytable(
    id int Not NULL
)
go
create table MyChild(
    id int Not NULL,
    masterid int NULL
)
go
alter table Mytable add constraint PK_Mytable primary key  (id)
alter table MyChild add constraint FK_MyChild_MyTable foreign key (masterid) references Mytable(id)
go
-- Now I want to change the primary key into a clustered primary key
-- this throws an error
ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable
GO
ALTER TABLE Mytable ADD CONSTRAINT PK_Mytable  PRIMARY KEY CLUSTERED (id)
GO

Here I get the following error

Msg 3725, Level 16, State 0, Line 1
Auf die 'PK_Mytable'-Einschränkung wird von der 'MyChild'-Tabelle, FOREIGN KEY-Einschränkung 'FK_MyChild_MyTable' verwiesen.
Msg 3727, Level 16, State 0, Line 1
Die Einschränkung konnte nicht gelöscht werden. Siehe vorherige Fehler.

OK I know that it works, when I drop the foreign key constraint before the change and reestablish it afterwards.

alter table MyChild drop constraint FK_MyChild_MyTable
go
ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable
GO
ALTER TABLE Mytable ADD CONSTRAINT PK_Mytable  PRIMARY KEY CLUSTERED (id)
GO
alter table MyChild add constraint FK_MyChild_MyTable foreign key (masterid) references Mytable(id)
go

That sounds like, I have to script all the foreign key relations from the database, drop them, do my primary key changes and reestablish the foreign keys from the generated script.

Are there any better ways for this task.

BTW. Finally we want to do this on production databases. (Downtime is an allowed option). All databases are SQL Server 2005 or SQL Server 2008.

Edit:

A coworker proposed to drop all indexes before this operation and to rebuild them afterwards. Sounds reasonable to me.

2. Edit:

Looking at this dba.stackexchange question, I'm starting to ask myself whether it would suffice to just add clustered indexes to the tables primary key columns.

Answer 1

Your approach is correct and pretty much the only way, but I would consider using a tool like Red Gate to help me.

• drop all FKs without scripting them
• change the indexes (as above)
• generate an "FK only" script using the compare tool to re-apply the FKs

The 3rd step saves writing something to script our your FK definitions, or fiddling with SSMS/SMO

Answer 2

I'm not a SQL Server guy, but I know that in MySQL you can temporarily disable foreign key checks. I did a quick google on whether it was possible to do this in SQL Server, and came up with this link:

https://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql

Summary from the answer:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints
EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"