July 9, 2008 at 6:56 am
Am I right in seeing that for sql 2000 if I want to convert a non-clustered Pk to a clusterd PK (same field) that I have to in fact drop it and recreate and therefore drop the 140+ FK's that reference this table and recreate ?!
there must be a simpler way ??? please say there is ?!
~simon
July 9, 2008 at 12:35 pm
If my understanding is correct, you do not need to drop the foreign key. I just tested it.
July 9, 2008 at 3:35 pm
July 10, 2008 at 3:23 am
SQL ORACLE (7/9/2008)
If my understanding is correct, you do not need to drop the foreign key. I just tested it.
Well every time I've ever tried to drop a PK reference by an fk I'd get something like this
Server: Msg 3725, Level 16, State 1, Line 1
The constraint 'PK_parent_Myid' is being referenced by table 'child', foreign key constraint 'FK_child_parent'.
I do appreciate its not necessary to have all PK as clustered but its been identified on this table that there is a performance gain
So I best get scripting out drop statements for all those FK .. and then obviosuly remember to recreate them all !
~simon
July 10, 2008 at 3:43 am
Still not ideal, but you could save yourself a bit of scripting...
Assuming you have a test copy of the database, do the change using Enterprise Manager, and capture the queries in Profiler in the background. You will see that it does all the DROP FK and ADD FK bits (amongst all the other dross it runs).
July 10, 2008 at 3:46 am
Ian Scarlett (7/10/2008)
Still not ideal, but you could save yourself a bit of scripting...Assuming you have a test copy of the database, do the change using Enterprise Manager, and capture the queries in Profiler in the background. You will see that it does all the DROP FK and ADD FK bits (amongst all the other dross it runs).
like you say not ideal but could well be better than the more manual alternative ! thank you 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply