Red Gate forums :: View topic - New bug in comparing Foreign Key Relationships
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

New bug in comparing Foreign Key Relationships

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
JohnnyT



Joined: 11 Nov 2009
Posts: 17

PostPosted: Fri Jan 20, 2012 5:50 pm    Post subject: New bug in comparing Foreign Key Relationships Reply with quote

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.
Back to top
View user's profile Send private message
JohnnyT



Joined: 11 Nov 2009
Posts: 17

PostPosted: Fri Jan 20, 2012 6:21 pm    Post subject: Reply with quote

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:
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:
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:
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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6674

PostPosted: Mon Jan 23, 2012 4:57 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
JohnnyT



Joined: 11 Nov 2009
Posts: 17

PostPosted: Mon Jan 23, 2012 5:09 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6674

PostPosted: Mon Jan 23, 2012 5:26 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
JohnnyT



Joined: 11 Nov 2009
Posts: 17

PostPosted: Mon Jan 23, 2012 5:47 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6674

PostPosted: Mon Jan 23, 2012 6:04 pm    Post subject: Reply with quote

OK, we should have a patch for this soon. Sorry for the misunderstanding.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Mon Jan 23, 2012 6:10 pm    Post subject: Reply with quote

And here is said patch:

ftp://support.red-gate.com/patches/SQL_Compare/SQL_Compare_10.0.0.181.zip

Let us know how you get on with it.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
JohnnyT



Joined: 11 Nov 2009
Posts: 17

PostPosted: Mon Jan 23, 2012 7:11 pm    Post subject: Reply with quote

Thank you Chris and Brian. I ran a couple tests and it works perfectly. I appreciate your quick turnaround on providing a patch.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group