By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,625 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,625 IT Pros & Developers. It's quick & easy.

alter table nocheck constraint still some dependencies

P: n/a
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?
Is there a way around this, or do I manually have to do the constraint
dropping/recreating?

Thanks
Jeff Kish
May 17 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
May 17 '06 #2

P: n/a
Jeff Kish (je*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 17 '06 #3

P: n/a
<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
May 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.