NOCHECK added

gknierimgknierim 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

  • 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.
    Systems Software Engineer

    Redgate Software

  • 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 do have a couple of bugs logged in this area, but I'm not 100% sure they relate to what you're seeing:

    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.
    Systems Software Engineer

    Redgate Software

  • We have encountered this as well, in the latest SQL Packager 6.4.0.8.

    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.
  • I can run through it and log any bugs that need t
    g.gifo be logged.
  • 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.
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.