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

clustered-primary-keysql-server-2005

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.

Best Answer

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

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