473,242 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

alter table nocheck constraint still some dependencies

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
3 15834
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
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
<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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something...
1
by: Rajesh Kumar Mallah | last post by:
Hi , Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint. the only way is to add the constriant is using UNIQUE INDEX . Is it a bug or intended behaviour? ...
10
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...
2
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1,...
3
by: moskie | last post by:
Is there a way to run an alter table statement that adds a constraint for a foreign key, but does *not* check the existing data for refrential integrity? I'm essentially looking for the equivalent...
0
by: pjhitchman | last post by:
Hi, Using DB2 version 9, fixpak 2 on AIX 5L. I created my DDL for defining a Primary Key to be done by a ALTER TABLE command, but I could not find a way of naming the tablespace to control were...
2
by: raghulvarma | last post by:
Can any body help me why do we go for nocheck constraint what is the main use of it? my next question is what means by this statement below mentioned create table sample1 (empid varchar(7)...
4
by: Maximilian Scherf | last post by:
Hello, I have two problems with the ALTER TABLE command (Warning: I'm not exactly an Oracle expert): First Problem: I want to change the name of a column. I've tried the following: ALTER...
7
by: quincy451 | last post by:
drop table . CREATE TABLE . ( NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , NULL , NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , (16) COLLATE
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.