Potential Bug: Comparing against SSDT misses primary keys.

mmurrell

I have an SSDT project which contains a table defined as follows:

CREATE TABLE [dbo].[Reports] (
    [ReportId]  INT           IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (MAX) NOT NULL,
    [Path]      VARCHAR (MAX) NOT NULL,
    [Enabled]   BIT           NOT NULL,
    [SortOrder] INT           NOT NULL,
    CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED ([ReportId] ASC)
);

CREATE TABLE [dbo].[ReportLegends] (
    [LegendColumnId]    INT           IDENTITY (1, 1) NOT NULL,
    [ReportId]          INT           NOT NULL,
    [ColumnName]        VARCHAR (MAX) NOT NULL,
    [ColumnDescription] VARCHAR (MAX) NOT NULL,
    [Enabled]           BIT           NOT NULL,
    [SortOrder]         INT           NOT NULL,
    [Category]          VARCHAR (MAX) DEFAULT ('') NOT NULL,
    [isUpdated]         BIT           NULL,
    CONSTRAINT [PK_ReportLegends] PRIMARY KEY CLUSTERED ([LegendColumnId] ASC),
    CONSTRAINT [FK_ReportLegends_Reports] FOREIGN KEY ([ReportId]) REFERENCES [dbo].[Reports] ([ReportId])
);

When Sql Compare (10.4.8.87) generates a script to create these tables in Sql Server, it creates both tables without the primary key. It does, however, attempt to create the foreign key which fails due to a lack of a primary key.

PRINT N'Creating [dbo].[ReportLegends]'
GO
CREATE TABLE [dbo].[ReportLegends]
(
[LegendColumnId] [INT] NOT NULL IDENTITY(1, 1),
[ReportId] [INT] NOT NULL,
[ColumnName] [VARCHAR] (max) NOT NULL,
[ColumnDescription] [VARCHAR] (max) NOT NULL,
[Enabled] [BIT] NOT NULL,
[SortOrder] [INT] NOT NULL,
[Category] [VARCHAR] (max) NOT NULL DEFAULT (''),
[isUpdated] [BIT] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[Reports]'
GO
CREATE TABLE [dbo].[Reports]
(
[ReportId] [INT] NOT NULL IDENTITY(1, 1),
[Name] [VARCHAR] (max) NOT NULL,
[Path] [VARCHAR] (max) NOT NULL,
[Enabled] [BIT] NOT NULL,
[SortOrder] [INT] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[ReportLegends]'
GO
ALTER TABLE [dbo].[ReportLegends] ADD CONSTRAINT [FK_ReportLegends_Reports] FOREIGN KEY ([ReportId]) REFERENCES [dbo].[Reports] ([ReportId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION

Error Message from Sql Compare:

The following error message was returned from the SQL Server:

[1776] There are no primary or candidate keys in the referenced table 'dbo.Reports' that match the referencing column list in the foreign key 'FK_ReportLegends_Reports'.
Could not create constraint. See previous errors.

The following SQL command caused the error:

ALTER TABLE [dbo].[ReportLegends] ADD CONSTRAINT [FK_ReportLegends_Reports] FOREIGN KEY ([ReportId]) REFERENCES [dbo].[Reports] ([ReportId])

Is this a parsing bug in Sql Compare's handling of SSDT Tables (Files)