|
|
Posted by Erland Sommarskog on 05/18/06 01:18
Jeff Kish (jeff.kish@mro.com) writes:
> I'm getting errors like this when I try to run an upgrade script I'm
> trying to write/test:
>
> altering labels to length 60
> Server: Msg 5074, Level 16, State 4, Line 5
> The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
>
> I used this to bracket my script:
>
> sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
> go
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.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|