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)
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.
Error Message from Sql Compare:
Is this a parsing bug in Sql Compare's handling of SSDT Tables (Files)