Options
Problem with disabled foreign keys (NOCHECK) from scripts
stevek
Posts: 2
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
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
Comments
Redgate Software