|
|
Posted by Hugo Kornelis on 05/18/06 00:48
On Wed, 17 May 2006 10:25:25 -0400, Jeff Kish wrote:
>Hi.
>
>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
>
>/* updates here */
>
>
>sp_msforeachtable @command1="print '?'",
>@command2="ALTER TABLE ? CHECK CONSTRAINT all"
>go
>sp_msforeachtable @command1="print '?'",
>@command2="ALTER TABLE ? ENABLE TRIGGER all"
>go
>
>I guess the alter table nocheck constraint isn't disabling the fk's
>completely?
Hi Jeff,
ALTER TABLE xxx NOCHECK CONSTRAINT yyy is intended to (temporarily)
disable the checking of the constraint. The constraint is not removed
from the metadata. That means that you still can't perform any
modifications that would invalidate the constraint. (Coonsider what
would happpen if you change the datatype of a column on one end of a
FOREIGN KEY constraint but not on the other end and then try to
re-anable the constraint...)
>Is there a way around this, or do I manually have to do the constraint
>dropping/recreating?
If you google for it, you might be able to find scripts to generate the
code to drop and recreate constraints. I've never used any such code, so
I can't comment on the reliability.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|