Source Controlling Data removes DROP CONSTRAINT

sillimans

Hi,

To save the trouble of everyone in my department manually clicking on which tables to source control data, I've been saving insert scripts toTFS's data folder, so when users click on "get latest", the data is ready to source control along with obejcts. Because the database has many tables, I've been having to drop constraints and add them with nocheck to avoid any foreign key errors. However, when I click on get latest in source control, I see any alter table statements are removed - it's just the insert.

I know those are changes to objects, not data, but I'm using data compare to generate the insert scripts which includes altering the constraints. I figured Source Controlling Data uses data compare but I guess it's not the case? Is the problem that I'm having one with TFS or Redgate?

Thanks.

michmela44

This is a bad bug/oversight.

Every time we "Get Latest" we now have "WITH NOCHECK" added on every table that had data changes....

Does Red Gate really think that all constraints should be disabled on the database? Why is that default across all of the tools when moving data?

Since I can't pick and choose which pieces of Schema changes I want to check in, it becomes a huge mess of manual updates that have to happen to remove the "WITH NOCHECK".

I've tried setting the defaults in Data Compare to force the constraints to be re-enabled, but SQL Source Control ignores them.

Seems obvious that for each database linked to Source Control, there should be a set of options for both Schema and Data compare similar to what is in the UI of each separate product so we can control how the sync takes place. Contraints, Case-Sensitive naming, column order, etc.

sillimans

Mine seems to be the opposite problem of yours. . .I want to add constraints with nocheck. There are over 1000 tables in our database, and it's impossible to source control all data. Sometimes it's necessary to grab data for one table I'm working on without also source controlling parent table. However, I can't because the "ADD CONSTRAINT WITH NOCHECK" is removed from my data files in TFS.

michmela44

I see. I misread what you wrote, however, I would say we both still have the same problem. SQL Source Control chooses to do what it wants and doesn't let us control how the sync happens the way SQL Compare and SQL Data Compare do.

michmela44

http://redgate.uservoice.com/forums/390 ... are-option

Any chance someone from Red Gate will actually look at this?