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?

Thanks for your post- it's hard to say for sure where the problem occurred. Both products have an option to ignore "WITH NOCHECK" which may be related though (Tools > Options in SQL Packager, and Edit Project > Options in SQL Compare)

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.

I've seen the option to ignore WITH NOCHECK but I want that to be enabled. I think that would be an important option not to ignore.

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.

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.

Thanks for the reproduction steps supplied - I've logged a bug for the development team to investigate (ref. SPA-588). I don't currently have an ETA for this to be looked at unfortunately as they are currently working on other areas, however I'll update this thread as and when I hear more.