Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

share|improve this answer
    
Hhm. I think I' quicker in writing a PowerShell Script using SMO to skript out all foreign keys, than to to install Red Gate tools. BTW I think it would be better to script the FKs before dropping them. I guess the mentioned tool needs a connection to the database and despite the name compare tool, can script the FK from that given Database? –  bernd_k Mar 6 '11 at 15:05
    
yes. It will generate a script with the differences (you can choose to ignore some). There are other tools such as dbghost which are free too. –  gbn Mar 6 '11 at 15:14
    
I searched for T-SQL scripts for generating the FK. I found some which aren't perfect and I'm not going to improve them. But the choice between SMO scripting and use of tools seems a questions of personal preferences. As far I stay with the combination of PowerShell and SMO. –  bernd_k Mar 6 '11 at 18:12

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:

http://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"
share|improve this answer
1  
Nice try. Thought of it myself, but it doesn't work. Same error message as before. –  bernd_k Mar 7 '11 at 16:35
    
well shucks. leaving it here instead of deleting so someone else doesn't have the same idea :D –  Derek Downey Mar 7 '11 at 16:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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