Foreign Key nockeck in compare

RussellRussell Posts: 6
edited September 13, 2005 9:55AM in SQL Compare Previous Versions
I have a database where I will run a script to make FK be set to no check do some processing and then set them back to check. I think compare the database with my control database and I get all the Foreign keys as different. The difference is they all generate with nocheck in them. The keys have been altered back to check. In fact if I generate from MSSQL that is not part of my generation, and if I try to add data in the table the FK functions correctly.
Russell Kahler
onContact Software
Cedarburg WI 53012

Comments

  • Hi Russell

    If you want to ignore the check / nocheck there is an option "ignore with nocheck ..." that may help you.

    Regadrs
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • That would allow me to ignore it but thats not really what I want. I was wonding why MSSQL thinks the FK is set to check and it does but SQL Compare doesn't. THe only way I can get SQL Compare to see the FK as checking is to drop and rebuild the FK.

    I don't want to do that every time a need to do this.

    I am using the comparing option to filter them out. I guess I would rather beable to check for it. I was wondering if there was some other MSSQL setting I could set to handle this.
    Russell Kahler
    onContact Software
    Cedarburg WI 53012
  • Hi Russell

    There is a bug in Enterprise manager that means if you look at the check options you will always see the "check existing data..." will be unchecked.

    Is this where you are looking to see how the check options are set.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • I am checking there and I am generating the Foreign Key from Query Anaylzer and it does not generate with nocheck in the syntax as I would expect it to .


    Also I have tested the Foreign Key be inserting data that is not valid and it FK constraint blocks my insert.
    Russell Kahler
    onContact Software
    Cedarburg WI 53012
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Russell,

    I've just put some information up about constraints in the knowledge base. It uses check constraints as an example but also could apply to foreign keys.

    http://www.red-gate.com/messageboard/vi ... php?t=1069

    Can you have a look and see if this is the issue you're running against?
  • Not quite. My issue is that I have the same constraint in two database, in both it is as I built it (set to check) When I compare it comes up identical.

    In one database I alter the contraint and make it nocheck. Compare now and they are different because of the nocheck.

    In the same database I alter the constraint again making it check. Compare again and I would expect them to be identical, but they are not. They still show up as being different by the nocheck flag on the constraint. But if I generate the constraint it doesn't generate the nocheck flag and the constraint works if I try to edit in bad data..
    Russell Kahler
    onContact Software
    Cedarburg WI 53012
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Russell,

    Are you modifying the constraint through Enterprise Manager using the 'check existing data on creation' checkbox? In my experience, this has no effect as I'd just tried it.

    I scripted the table with the constraint in Enterprise Manager, saved that script to a file, changed the check existing data setting using the table designer and saved the table, then scripted it again in Enterprise Manager and the result was two identical scripts.

    I have a feeling that Enterprise Manager's constraint editor is broken and you should probably try changing these constraints 'the hard way' in Query Analyzer instead.
  • No, I am scripting out the constraint change from check to nocheck. The thing is the constraint is fine. It now checks just SQL Compare shows that it should not.
    Russell Kahler
    onContact Software
    Cedarburg WI 53012
  • Hi Russell

    Sorry for the delay in getting back to you but I have been trying to replicate your circumstances. So far all the tests i have run seem to work.

    Can you send over a snapshot of your schemas and an example of the scripts you use to support@red-gate.com for me to look at.

    Can you also let me know the versions of SQL Compare and SQL Server you are using.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • For what it's worth, I have had the same problem. I have a topic open on SQL-Server Central about it.

    http://www.sqlservercentral.com/forums/ ... eid=191010

    I came to the conclusion that WITH NOCHECK was being invalidly being added to the scripting of the table by SQL-Compare after performing

    ALTER TABLE tbl_name NOCHECK CONSTRAINT ALL

    ALTER TABLE tbl_name CHECK CONSTRAINT ALL
  • Thanks for all the information about this problem. It has now bee escalated to the developers and test team - I hope that a fix will be available soon and will try to post a response here and at SQL server central when available.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • Thanks for looking into it.

    Steve
  • The problem is that
    ALTER TABLE at_plPlatted_Lot 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 at_plPlatted_Lot WITH CHECK CHECK CONSTRAINT all

    SQL Compare detects these differences thus correctly.
    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Thanks for the work around. I will try it out.

    Steve
This discussion has been closed.