Newbie question - WITH NOCHECK

MartinHMartinH Posts: 82 Bronze 2
edited April 29, 2005 12:05PM in SQL Compare Previous Versions
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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
  • 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
    Daniel Handley
    Red Gate Software Ltd
This discussion has been closed.