New bug in comparing Foreign Key Relationships

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

New bug in comparing Foreign Key Relationships

Postby JohnnyT » Fri Jan 20, 2012 5:50 pm

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
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby JohnnyT » Fri Jan 20, 2012 6:21 pm

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...

Code: Select all
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...

Code: Select all
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...

Code: Select all
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.
JohnnyT
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby Brian Donahue » Mon Jan 23, 2012 4:57 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby JohnnyT » Mon Jan 23, 2012 5:09 pm

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
JohnnyT
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby Brian Donahue » Mon Jan 23, 2012 5:26 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby JohnnyT » Mon Jan 23, 2012 5:47 pm

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.
JohnnyT
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby Brian Donahue » Mon Jan 23, 2012 6:04 pm

OK, we should have a patch for this soon. Sorry for the misunderstanding.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Chris Auckland » Mon Jan 23, 2012 6:10 pm

And here is said patch:

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

Let us know how you get on with it.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby JohnnyT » Mon Jan 23, 2012 7:11 pm

Thank you Chris and Brian. I ran a couple tests and it works perfectly. I appreciate your quick turnaround on providing a patch.
JohnnyT
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest