alter table nocheck constraint still some dependencies

[Jeff Kish]

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_msforeachtab le "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtab le "ALTER TABLE ? DISABLE TRIGGER all"
go

/* updates here */


sp_msforeachtab le @command1="prin t '?'",
@command2="ALTE R TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtab le @command1="prin t '?'",
@command2="ALTE R TABLE ? ENABLE TRIGGER all"
go

I guess the alter table nocheck constraint isn't disabling the fk's
completely?
Is there a way around this, or do I manually have to do the constraint
dropping/recreating?

Thanks
Jeff Kish

[Hugo Kornelis]

Re: alter table nocheck constraint still some dependencies

On Wed, 17 May 2006 10:25:25 -0400, Jeff Kish wrote:
[color=blue]
>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_msforeachta ble "ALTER TABLE ? NOCHECK CONSTRAINT all"
>go
>sp_msforeachta ble "ALTER TABLE ? DISABLE TRIGGER all"
>go
>
>/* updates here */
>
>
>sp_msforeachta ble @command1="prin t '?'",
>@command2="ALT ER TABLE ? CHECK CONSTRAINT all"
>go
>sp_msforeachta ble @command1="prin t '?'",
>@command2="ALT ER TABLE ? ENABLE TRIGGER all"
>go
>
>I guess the alter table nocheck constraint isn't disabling the fk's
>completely?[/color]

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...)
[color=blue]
>Is there a way around this, or do I manually have to do the constraint
>dropping/recreating?[/color]

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

[Erland Sommarskog]

Re: alter table nocheck constraint still some dependencies

Jeff Kish (jeff.kish@mro. com) writes:[color=blue]
> 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_msforeachtab le "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtab le "ALTER TABLE ? DISABLE TRIGGER all"
> go[/color]

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.
[color=blue]
> sp_msforeachtab le @command1="prin t '?'",
> @command2="ALTE R TABLE ? CHECK CONSTRAINT all"[/color]

When you reenable constraints, you should use this quirky syntax:

@command2="ALTE R 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@sommarsk og.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

[Jeff Kish]

Re: alter table nocheck constraint still some dependencies

<snip>[color=blue]
>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.
>[color=green]
>> sp_msforeachtab le @command1="prin t '?'",
>> @command2="ALTE R TABLE ? CHECK CONSTRAINT all"[/color]
>
>When you reenable constraints, you should use this quirky syntax:
>
> @command2="ALTE R 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.[/color]
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