ALTER TABLE CHECK CONSTRAINT not working for Foreign Key

Question

Post reply

ALTER TABLE CHECK CONSTRAINT not working for Foreign Key

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

steve block SSCertifiable Points: 7698 More actions · Answer 1

I found a thread that was almost identical to mine on a Red-Gate forum. I added to it at

http://www.red-gate.com/MessageBoard/viewtopic.php?t=1066&sid=34c6b162e6593d1b075671f06a9deaa4

Maybe they can figure it out.

Steve

noeld SSC Guru Points: 96590 More actions · Answer 2

Wow Thanks!

It is good to know that other people are having the same problem so you keep the sanity

* Noel

steve block SSCertifiable Points: 7698 More actions · Answer 3

Just an update, Red-Gate is looking into the problem and hope to have a fix for it.

Steve

noeld SSC Guru Points: 96590 More actions · Answer 4

Thanks for the feedback.

I know many people that use red-gate and this thread may be helpful to keep around

* Noel

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

The problem is that

ALTER TABLE aaaa CHECK CONSTRAINT all

does only enable the check constraint, and it will still be considered untrusted from the query optimizer's point of view. If you have a look at the status field of the sysobjects table for a foreign key, you will see that the above command will not reset the constraint to its previous status. The correct reset command should be

ALTER TABLE aaaa WITH CHECK CHECK CONSTRAINT all

SQL Compare detects this difference correctly.

Regards,

Andras

--

András Belokosztolszki, PhD

Red Gate Software Ltd.

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Yes, this pretty much sums up the confusion about re-enabling and re-enabling AND validating existing data.

BOL is very confusing about the syntax, and what really happens when you say CHECK CONSTRAINT ALL... There is nowhere to be found that you need to say CHECK CHECK (twice) for validation of existing data to happen. (at least I haven't found it)

I have a pretty wordy 'demo-article-script-thing' that describes this scenario, but unless someone's really bored, I think it's not necessary to post that here - the essence of it is that you need CHECK CHECK not just CHECK to be 'safe'

(though the statusfield thing was new to me - thanks Andras )

/Kenneth

steve block SSCertifiable Points: 7698 More actions · Answer 7

steve block

SSCertifiable

Points: 7698

September 13, 2005 at 10:57 am

#589453

Thanks for the work around. I will try it out.

Steve

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Not trying to beat a dead horse here, but I just want to stress that it's not really a workaround - it's how it's supposed to work (though very poorly documented)

First, you have a check constraint in place on a table that contains data. When you disable it, the constraint becomes 'untrusted', which is perfectly logical, because now data can be entered that does not conform to the constraint. SQL Server knows this.

Sometime later, you eanble the constraint again. There are two ways to do this. Enabling the check constraint and not looking at the current data, or enabling the constraint and also checking that existing data conforms to the constraint.

Only the latter of the two will also make the constraint 'trusted' again. ie we know that during the period the constraint was disabled there was a possibility that 'dirty' data may have snuck in, but we have verified that this is not the case when we turned on the constraint again. If we do this, we can trust the constraint again. If we do not validate existing data, the only thing we can guarantee is that from this point on, the constraint will be enforced. We have no clue about the existing data. SQL Server know this.

So, when you say:

ALTER TABLE table CHECK CONSTRAINT {constraintname | ALL}

..we do the first method - enabling but no verification of existing data. The constraint is active, but doesn't guarantee that all data in the table conforms to it.

What we really want to do, is the second method, enabling and verifying so the constraint becomes 'trusted' again in all apsects.

ALTER TABLE table WITH CHECK CHECK CONSTRAINT {constraintname | ALL}

There is one other major difference between the two - if the table is large, enabling and verifying data may take some time, in such cases it's probably best to do in off-peak hours.

/Kenneth

steve block SSCertifiable Points: 7698 More actions · Answer 9

I'm sorry. When I say workaround I mean implementing syntax that is not obvious (undocumented) or intuitive (WITH CHECK CHECK does seem a little unusual) to solve a problem. I would wager that this is one of those features that was not in the original specifications for SQL Server. An "Oh, that's the way it work? I meant it to work that way." feature.

Steve

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

Oh, no worries. Sometimes I overly mark words a bit. Perhaps due to English not being my native language

AFAIK, this syntax has been there from the early beginnings, though BOL has been very fuzzy about describing it clearly. I've mentioned it once to the BOL folks, perhaps I'll take it up with them again. On the other hand, anyone can leave feedback on BOL about improvements, omissions, errors etc. (there's a link in there somewhere if I remember correctly)

/Kenneth