Problem with disabled foreign keys (NOCHECK) from scripts

stevek

Using SQL Compare 6.2.0.271, I am trying to sync a scripts folder with a database, where some of the foreign keys are disabled.

When the scripts contain a disabled foreign key, SQL Compare seems to ignore the disabled part and assumes it is enabled. Below is the scripted table. Note the last line which disables the foreign key.

CREATE TABLE [dbo].[Table_2](
[pkcol2] [uniqueidentifier] NOT NULL,
[fkcol] [uniqueidentifier] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED ( [pkcol2] ASC))
GO

ALTER TABLE [dbo].[Table_2] WITH NOCHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([fkcol])
REFERENCES [dbo].[Table_1] ([pkcol])
GO
ALTER TABLE [dbo].[Table_2] NOCHECK CONSTRAINT [FK_Table_2_Table_1]

If the foreign key is missing in the database, here is the difference SQL Compare displays:

ALTER TABLE [dbo].[Table_2] WITH NOCHECK ADD FOREIGN KEY ([fkcol]) REFERENCES [dbo].[Table_1] ([pkcol])
GO

After I sync the database, SQL Compare reports that the tables are identical even though they are not. In fact, if I fix the database table so that it is identical to the scripted table (foreign key is disabled), SQL Compare wants to enable it.

Note that I do not have the Ignore WITH NOCHECK option set. If I set the Ignore WITH NOCHECK option, it still creates the foreign key as enabled, but ignores differences in enabled status. This is also incorrect behaviour, as disabled foreign keys are not supposed to be ignored.

All of this works fine if the comparison is between two databases - it only occurs syncing from scripts to database.

Steve

Michelle T

The part where SQL Compare doesn't pick up the NOCHECK CONSTRAINT line from scripts has been fixed internally - it will be fixed in the next released version, or if this is a major issue for you, you can email support@red-gate.com to get the alpha of our next version (which isn't as thoroughly tested as our release versions but does include this fix).