SC 3 - SQL Compare may disable check constraints

Brian DonahueBrian Donahue Posts: 6,590 New member
edited June 28, 2005 10:23AM in Knowledge Base
  • Date: 28 JUL 2005
  • Versions Affected: 3.x
SQL Compare has an option, available from the Comparison->Options menu item, to ignore WITH NOCHECK on constraints. This option makes it possible to ignore whether or not a constraint checks existing data when it is created.

This does not ignore disabled constraints, however, which are created using the NOCHECK option. If the constraint is disabled in the first database, SQL Compare will disable it in the second database, regardless of the WITH NOCHECK option.

Consider the following table:
First database:
CREATE TABLE [dbo].[SyncByDateTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
)
ALTER TABLE ADD CONSTRAINT [CK_Me] CHECK ([ID] > 0)
alter table [dbo].[SyncByDateTest] nocheck constraint [CK_Me]
Second database:
CREATE TABLE [dbo].[SyncByDateTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
)
ALTER TABLE WITH NOCHECK ADD CONSTRAINT [CK_Me] CHECK ([ID] > 0)
The last command run against the first database disables the constraint called CK_Me. When SQL Compare synchronizes the databases, it will push this change to the second database even if you use the ignore WITH NOCHECK option.
Sign In or Register to comment.