Synchronization fails after renaming table
grchristin
Posts: 2
We changed the names of a couple of tables in our DB. When trying to synchronize our development DB to our test production DB we received errors that it couldn't create some constraints because they already existed and rolled back any changes. I had to manually rename the tables, re-run the compare and then run the synchronization. It then worked. Bug? Should SQL Compare 4 be able to handle it?
Comments
Hi,
Constraint names must be unique in the database. This is an ugly limitation of SQL Server, especially since constraints can only be accessed via their parent tables. You are right that we are not handling such conflicts. On the other hand it is an interesting question about how we should handle it. Clearly we cannot just drop constraints from tables that were not selected for synchronization. Similarly we should not move constraints to other tables. One thing SQL Compare can do, is that if the renamed table is selected, it can drop it/or resolve the constraint problem by making it the same as the source table (if there is any). Therefore these conflicts require manual intervention. A similar problem occurs when one tries to migrate a table (say "herTable") to another database, but that database already has another non-table object (e.g. a function) called "herTable". Once again the only solution that can be used from SQL Compare would be to explicitly select the "herTable" function as well, in which case it will be dropped as expected, and the synchronization will succeed. But, clearly, we cannot select objects to be deleted automatically.
Regards,
Andras
Red Gate Software Ltd.