NOCHECK added for tables marked to package data with no data
dustinlw
Posts: 3
See related post: NOCHECK added, http://www.red-gate.com/MessageBoard/vi ... hp?p=53952
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.
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.
