Bizarre Foreign Key Behavior

clamk123

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

James B

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.