Red Gate forums :: View topic - Bizarre Foreign Key Behavior
Return to www.red-gate.com RSS Feed Available

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

Bizarre Foreign Key Behavior

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
clamk123



Joined: 01 Apr 2012
Posts: 28

PostPosted: Thu Apr 19, 2012 5:54 pm    Post subject: Bizarre Foreign Key Behavior Reply with quote

I noticed a strange situation when parsing script files which can cause errors in executing the generated script from SQL Comparison SDK (and the SQL Compare UI).

If you compare two table scripts (not database):

Source:

CREATE TABLE [dbo].[tblTable2]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[value] [varchar] (15) NULL
)
GO
ALTER TABLE [dbo].[tblTable2] ADD CONSTRAINT [pc_tblTable2] PRIMARY KEY CLUSTERED ([id])
GO


Target:

CREATE TABLE [dbo].[tblTable2]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[value] [varchar] (15) NULL
)
GO
ALTER TABLE [dbo].[tblTable2] ADD CONSTRAINT [pc_tblTable2] PRIMARY KEY CLUSTERED ([id])
GO
ALTER TABLE [dbo].[tblTable2] ADD CONSTRAINT [FK_NamedId] FOREIGN KEY (id) REFERENCES tblTable (id)


Then SQLCompare will create a deployment script like:

PRINT N'Dropping foreign keys from [dbo].[tblTable2]'
GO
ALTER TABLE [dbo].[tblTable2] DROP CONSTRAINT[FK_NamedId]
GO


Which is the expected results with a named foreign key.

If you do the same comparison but with a typo in the foreign key constraint for the target (CONSTRAINT spelled incorrectly):

ALTER TABLE [dbo].[tblTable2] ADD CONSTAINT [FK_NamedId] FOREIGN KEY (id) REFERENCES tblTable (id)

It does something much more interesting.

First, it doesn't throw a parsing error, which surprised me. This is the same in the SDK or SQL Compare application. Also, it recognizes it as a foreign key (I assume it partially matches on the word CONSTRAINT and determines through REFERENCES that it's a foreign key). It does, however, generate a name for it, as it doesn't recognize the "named" key. The generated script code will create something similar to:

PRINT N'Dropping foreign keys from [dbo].[tblTable2]'
GO
ALTER TABLE [dbo].[tblTable2] DROP CONSTRAINT[FK__tblTable2__33C56FEA]
GO

In SQLCompare when looking at the source/target comparisons it changes the script of the object on the screen to have the newly generated name, not the name that was in the source script file. When deploying the amount of objects we deploy we wouldn't even notice it, even more so when the name on the screen "appears" to be the real name, but doesn't match the physical file. With the SDK I noticed it, but UI users might not as easily. Unfortunately we do have developers making typos in code regularly.

It creates a new dynamic name for the foreign key each time (as if it is using the dynamic name code for nameless inline foreign keys in tables); this causes errors when the script executes (including cascading errors when objects dependent on it being dropped fails such as table rebuilds).
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Mon Apr 23, 2012 5:35 pm    Post subject: Reply with quote

Thanks for kindly supplying the steps to reproduce the problem here. It's definitely odd behaviour; I'm pretty suprised it didn't just throw a parser error due to the invalid keyword to be honest (if we'd gone to the trouble of fuzzy-matching keywords then I'd assume it would continue as normal).

I've logged a bug for the developers to take a look (ref. SC-5728) although at this point I'm not able to offer any timescale for a fix I'm afraid. We'll post back when there's an update.
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