|
|
Posted by Jeff Kish on 05/18/06 15:11
<snip>
>Since you did not include the actual code that implements the change,
>I will have to guess. My guess is that you change the length of a
>PK column that is referenced by an FK.
>
>If that is the case, you indeed have to drop the FK, as an FK must
>always be of the same data type as the key it refers to. SQL Server
>cannot know that you are altering both columns, so it only sees that
>you are breaking the rule.
>
>> sp_msforeachtable @command1="print '?'",
>> @command2="ALTER TABLE ? CHECK CONSTRAINT all"
>
>When you reenable constraints, you should use this quirky syntax:
>
> @command2="ALTER TABLE ? WITH CHEC CHECK CONSTRAINT all"
>
>This forces SQL Server to re-check the constraints. While this take
>much longer time, it also means that the optimizer can trust these
>constraints and take them in regard when computing a query plan. In
>some situations this can have drastic effects on the performance
>of the application.
Thanks to both of you, not only for the quick accurate explanation, but also
the reenable recommendation.
I guess I got kind of spoiled by Oracle (I hope that isn't a dirty word here),
but I was able to get things to work better by dropping then re-creating the
constraints.
Yes, I was changing the length of one of the columns in the primary key .
I took some of Erland's other advice I saw elsewhere, and decided not to rely
on any automated tools, and just sat down and grunted through manually
figuring out and implementing the scripts.
regards,
Jeff Kish
Navigation:
[Reply to this message]
|