Changes to the schema - after sync: constraint WITH NOCHECK
toebens
Posts: 10
hi,
i created a new database and compared my existing one with the new one with sql compare 7.
i run the sync so the whole schema was published to the new, previously empty, database.
i recompared both with sql compare 7 and everything was equal.
then i used sql data compare 6.1 (as i recently had some problems with v. 7.0 but i suppose it has the same problem):
i published nearly all data from my existing one to the new one (except the data for some unimportant tables)
i recompared both with data compare 6.1 and the tables i selected were equal to each other.
opening up sql compare 7 again and comparing the databases it had some differences with contrains:
the tables on my old DB had:
ALTER TABLE [dbo].[ProfilePoints] ADD CONSTRAINT [FK_ProfilePoints_Competence] FOREIGN KEY ([CompetenceID]) REFERENCES [dbo].[Competence] ([CompetenceID])
compared to the new DB i have this:
ALTER TABLE [dbo].[ProfilePoints] WITH NOCHECK ADD CONSTRAINT [FK_ProfilePoints_Competence] FOREIGN KEY ([CompetenceID]) REFERENCES [dbo].[Competence] ([CompetenceID])
sql data compare obviously changes constraints - it doesn't properly re-create them after data sync.
please fix this asap.
thanks
i created a new database and compared my existing one with the new one with sql compare 7.
i run the sync so the whole schema was published to the new, previously empty, database.
i recompared both with sql compare 7 and everything was equal.
then i used sql data compare 6.1 (as i recently had some problems with v. 7.0 but i suppose it has the same problem):
i published nearly all data from my existing one to the new one (except the data for some unimportant tables)
i recompared both with data compare 6.1 and the tables i selected were equal to each other.
opening up sql compare 7 again and comparing the databases it had some differences with contrains:
the tables on my old DB had:
ALTER TABLE [dbo].[ProfilePoints] ADD CONSTRAINT [FK_ProfilePoints_Competence] FOREIGN KEY ([CompetenceID]) REFERENCES [dbo].[Competence] ([CompetenceID])
compared to the new DB i have this:
ALTER TABLE [dbo].[ProfilePoints] WITH NOCHECK ADD CONSTRAINT [FK_ProfilePoints_Competence] FOREIGN KEY ([CompetenceID]) REFERENCES [dbo].[Competence] ([CompetenceID])
sql data compare obviously changes constraints - it doesn't properly re-create them after data sync.
please fix this asap.
thanks
Comments
This is a known issue with SQL Data Compare synchronizations when the "drop primary keys, indexes, and unique constraint" option is used. Because the keys are dropped and recreated, there is a chance that the script will fail and rollback if the data migrated was inconsistent.
For this reason, the constraints disabled by SQL Data Compare are created WITH NOCHECK to ensure that the data migrated over remains as consistent as it had been in the source database. We have extensively evaluated this decision and for technical reasons it is very unlikely that this behavior will change.
On the plus side, you can use the "ignore WITH NOCHECK" option in SQL Compare's project options and you won't see this difference in the schema comparison any more.
It is ok that the sync script will disable the constraints to perform the actual inserts BUT it would be cool if it could re-create/re-store the constraints like they were before (exactly), so there would not be a need to set "ignore WITH NOCHECK" or to re-compare and sync with sql compare (not sql data compare) again