Newbie question - WITH NOCHECK

MartinH

When comparing two supposedly iqual databases I get several foreign key differences stating that the 'WITH NOCHECK' values differ.

When I use Enterprise Manager I can't see any differences when I inspect the foreign key properties. Where do I go to see these differences?

When I tell SQLCompare to update the destination database it really does do something because the next 'compare' comes up clean, but I can't see what it has done.

TIA,
MartinH.

Brian Donahue

Hi Martin,

SQL Compare is probably the most authoritative answer about checking existing data on creation. Because the SQL language lets you specify WITH NOCHECK in three different ways:

• ALTER TABLE x ADD CONSTRAINT WITH NOCHECK y
• ALTER TABLE x ADD NOCHECK CONSTRAINT y
• ALTER TABLE x ADD CONSTRAINT y WITH NOCHECK

Query Analyzer only recognizes one of these when it checks the box that says 'check existing data on creation'. Even worse, there's a bug that means that doesn't even work reliably.

I hope this helps clear the situation up a bit.

mchughc

Brian,

I am having the same problem. Can you please provide more details as to how I can validate this. If Enterprise Manager is incorrect can you please provide a SQL snippet that I can run against both Dbs to see that in fact there is a difference in the Constraint. In other words how is SQL Compare determing the difference? We are evaluating your product, and I need to prove to my mnager that your product is correct and EM is wrong. I haven't been able to find where in the data dictionary that info is stored?

Thanks,
Craig

Daniel Handley

Here is some script that will help you.
Run it against each database and note the diffferances.

SELECT "table" = object_name(parent_obj),
"constraint" = name
FROM sysobjects
WHERE xtype IN ('F', 'C')
AND objectproperty(id, 'CnstIsNotTrusted') = 1
ORDER BY 1, 2

Thanks go to Craig who sent this in.

Regards
Dan