Foreign Key nockeck in compare

Russell

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.

Daniel Handley

Hi Russell

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

Regadrs
Dan

Russell

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.

Daniel Handley

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

Russell

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.

Brian Donahue

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?

Russell

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..

Brian Donahue

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.

Russell

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.

Daniel Handley

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

sblock

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

Daniel Handley

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

sblock

Thanks for looking into it.

Steve

Andras B

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

sblock

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

Steve