New bug in comparing Foreign Key Relationships

JohnnyT

Thank you for adding the "Use DROP and CREATE instead of ALTER" option that we requested. This will be a big help when deploying update scripts to clients who may already have pre-release versions of stored procedures installed on their live databases.

However, this option introduced a serious new bug when comparing foreign key constraints. If a FK constraint is not enforced (Enforce Foreign Key Constraint = No), the generated deployment script neglects to disable enforcement of that constraint after it is rebuilt. So those constraints now have "Enforce Foreign Key Constraint" = Yes. I can reproduce this bug.

This bug is not present when I uncheck the new "Add object existence checks" and "Use DROP and CREATE instead of ALTER" options. It's also not present in SQL Compare 9. These properly generate the "Disabling constraints on [tablename]" sections in the deployment script.

One question... Why does this new "Use DROP and CREATE instead of ALTER" cause many things such as FK constraints to be dropped and created? I thought this option would only affect server side code such as stored procedures and functions. Hopefully it's not dropping and recreating tables without migrating data to the rebuilt tables.

JohnnyT

After a little more research, I found the bug. The deployment script does try to disable enforcement of the FK constraint, but the logic added by the "Add object existence checks" is incorrect. Here's an example. I have two tables named "items" and "cartrule" which have an unenforced relation defined in it. With this option checked, the deployment script drops the constraint...

PRINT N'Dropping foreign keys from [dbo].[cartrule]'
GO
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_cartrule_items]', 'F') AND parent_object_id = OBJECT_ID(N'[dbo].[cartrule]', 'U'))
ALTER TABLE [dbo].[cartrule] DROP CONSTRAINT[FK_cartrule_items]
GO

Then it rebuilds it...

PRINT N'Adding foreign keys to [dbo].[cartrule]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_cartrule_items]', 'F') AND parent_object_id = OBJECT_ID(N'[dbo].[cartrule]', 'U'))
ALTER TABLE [dbo].[cartrule] WITH NOCHECK  ADD CONSTRAINT [FK_cartrule_items] FOREIGN KEY ([department], [category], [item]) REFERENCES [dbo].[items] ([department], [category], [item]) NOT FOR REPLICATION
GO

Then it attempts to mark it as unenforced...

PRINT N'Disabling constraints on [dbo].[cartrule]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_cartrule_items]', 'F') AND parent_object_id = OBJECT_ID(N'[dbo].[cartrule]', 'U'))
ALTER TABLE [dbo].[cartrule] NOCHECK CONSTRAINT [FK_cartrule_items]
GO

However, the logic added by the "Add object existence checks" option is not correct. It should be "IF EXISTS", not "IF NOT EXISTS". The way it is now, it remains enforced. Thanks.

Brian Donahue

Hi,

I'm not sure this is a consequence of the existence checks option - it looks to me this is just part of the script that runs when a foreign key needs to be dropped and re-added. Foreign Keys are not considered as "free-standing" objects by SQL Compare and shouldn't be affected by the check existence option.

The last time I checked, SQL Compare always created foreign keys with NOCHECK so as to prevent failure in the schema script when existing data was inconsistent. This should only affect the creation of the key, and not disable the key permanently, though.

JohnnyT

Thanks for the response, Brian. Regardless of the reason to drop and re-add a foreign key, it is a reproducible bug that occurs when "Add object existence checks" and "Use DROP and CREATE instead of ALTER" options are both enabled. Non-enforced foreign key relations are erroneously made enforced after running the generated deployment script. As I pointed out above, the cause is the incorrect IF NOT EXISTS wrapper that is added to script, preventing it from specifying the NOCHECK option on the relations. When these two options are not enabled, the NOCHECK is properly specified, so the problem is definitely in these new options.

Hopefully this can be fixed with a patch release soon, as using these two new options can really screw up deployments. I'm just glad I caught this before I deployed it to my 200+ clients.

Thanks,
John

Brian Donahue

I think there is a minunderstanding - I'm not making any dispute about this I am just saying I don't think it's happening for the reason you think it's happening.

For instance, the IF NOT EXISTS is not incorrect - it's checking to see the FK doesn't exist before it puts the FK back.

I think that when you use the existence check it probably causes the table to be rebuilt and that would require the FK to be taken off and put back on.

And then when it does that it applies the key with nocheck so the script will succeed even if the data doesn't conform.

JohnnyT

Thanks Brian. The thing is, the relation does exist because it was re-created further up the script. The NOCHECK option on the ADD CONSTRAINT applies only to checking for conforming data. The constraint still remains enforced. It needs to be marked as unenfoced, but the IF NOT EXISTS is preventing it. So the "ALTER TABLE [dbo].[cartrule] NOCHECK CONSTRAINT [FK_cartrule_items] " line to disable enforcement does not get processed. When I manually remove the IF NOT EXISTS lines and let the ALTER TABLE NOCHECK CONSTRAINT lines get processed, they databases now match. When I generate a deployment script without these two new options enabled, there are no IF NOT EXISTS lines at all and the ALTER TABLE NOCHECK CONTSTRAINT lines are always processed.

Brian Donahue

OK, we should have a patch for this soon. Sorry for the misunderstanding.

Chris Auckland

And here is said patch:

ftp://support.red-gate.com/patches/SQL_ ... .0.181.zip

Let us know how you get on with it.

JohnnyT

Thank you Chris and Brian. I ran a couple tests and it works perfectly. I appreciate your quick turnaround on providing a patch.