NOCHECK added
gknierim
Posts: 31
So, I've created create and upgrade scripts using the Packager. I got them to run successfully (with a few tweaks) but when I did a SQL Compare against the Master database of what it should look like and the upgraded database, the upgraded database had NOCHECK added for some of the constraints on the tables. Is this a bug with packager or SQL Compare?
Comments
In addition, SQL Data Compare has an option "Force constraints to be re-enabled with CHECK". It doesn't look like Packager exposes this though.
I do recall there being the odd bug reported in this area (although not directly in relation to SQL Packager) so if the above doesn't help and can supply some more detailed information to reproduce what you saw, I can see about logging a bug if it's indeed incorrect.
Redgate Software
I've had the same problem WITH NOCHECK with SQL Compare. I will sync up the databases to make sure that it is not present, and then when I come back the next time (week later) and do the SQL Compare again, the NOCHECK somehow has been added back. So I'm not sure that there is a bug there but I've seen this issue lots of times. I manage alot of database so I keep thinking that it was another database that I did this on. I will need to investigate this further to make sure it is consistent.
After I run the scripts made by packager, I went into SSMS and scripted the table to a new query window and it scripts out the ALTER TABLE WITH NOCHECK but then right after it, it adds a WITH CHECK in a separate statement. So I'm not sure what that is all about either.
One (ref: SC-3419) was raised as a result of this forum post
Another, SC-4077, relates to the "Ignore with nocheck" option in Compare also then ignoring if the constraint is enabled or not. This was by design though due to the way SQL handles these.
Not sure if that helps - if you want to mail in some step-by-step changes of what you had configured to start with, and how it changed, I can run through it and log any bugs that need to be logged.
Redgate Software
We narrowed it down to the following steps:
1. Select a table for data to be packaged that has no data in it, that has a foreign key constraint
2. Run the package to create the script.
Results: The script contains:
1. a foreign key DROP CONSTRAINT for that table's constraint at the beginning of the data section,
2. then (rightly) no INSERTs for the table,
3. then a foreign key constraint ADDed WITH NOCHECK at the end of the data section
Work-arounds: Prior to running the packager to create the script:
1. Add at least 1 row of data to the table (WITH NOCHECK disappears from ADD CONTSTRAINT statement), or
2. Un-select the table for data to be packaged (no DROP or ADD CONSTRAINT)
The following does NOT work around the issue:
1. Tools, Options... Data Options, Synchronization Behavior:
2. Un-check: Disable foreign keys
Because INSERTs will fail due to order in which they are performed (alphabetical) and foreign key constraints.
I am curious whether the RedGate team considers this a bug and will address it. We would like to mark tables to synchronize data that may or may not have any data in them when the packager is run without messing up schema by re-creating constraints WITH NOCHECK.
Redgate Software