SSDT doesn't like how FK constraints are handled
AndreMessier
Posts: 4
Hi,
I'm using a SSDT Database project and have SQL Source Control pointing at the project. I've run into a problem where SSDT complains about how foreign key constraints are formatted by SQL Source Control. SSDT formats them inline with the table definition like something like this:
CREATE Table dbo.mytable
(
[Column1] [int] NOT NULL IDENTITY(1,1),
[ForeignKey1] [int],
CONSTRAINT [FK_mytable_ForeignKey1] FOREIGN KEY ([ForeignKey1]) REFERENCES [dbo].[myFkTable] ([Id])
)
while SSC4 formats it with an alter table statement like:
CREATE Table dbo.mytable
(
[Column1] [int] NOT NULL IDENTITY(1,1),
[ForeignKey1] [int]
)
ALTER TABLE dbo.mytable ADD
CONSTRAINT [FK_mytable_ForeignKey1] FOREIGN KEY ([ForeignKey1]) REFERENCES [dbo].[myFkTable] ([Id])
When I try to build the database project, I get an error "SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements."
I can manually fix this by either reformatting the create script to declare the constraints as part of the initial CREATE script, or by putting GO statements before each ALTER statement. Is there any way to get SSC4 to do either of those automatically?
Thanks
I'm using a SSDT Database project and have SQL Source Control pointing at the project. I've run into a problem where SSDT complains about how foreign key constraints are formatted by SQL Source Control. SSDT formats them inline with the table definition like something like this:
CREATE Table dbo.mytable
(
[Column1] [int] NOT NULL IDENTITY(1,1),
[ForeignKey1] [int],
CONSTRAINT [FK_mytable_ForeignKey1] FOREIGN KEY ([ForeignKey1]) REFERENCES [dbo].[myFkTable] ([Id])
)
while SSC4 formats it with an alter table statement like:
CREATE Table dbo.mytable
(
[Column1] [int] NOT NULL IDENTITY(1,1),
[ForeignKey1] [int]
)
ALTER TABLE dbo.mytable ADD
CONSTRAINT [FK_mytable_ForeignKey1] FOREIGN KEY ([ForeignKey1]) REFERENCES [dbo].[myFkTable] ([Id])
When I try to build the database project, I get an error "SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements."
I can manually fix this by either reformatting the create script to declare the constraints as part of the initial CREATE script, or by putting GO statements before each ALTER statement. Is there any way to get SSC4 to do either of those automatically?
Thanks
Comments
It looks like this is a known issue with internal reference SOC-6031. Just to get some more specific information for the team- what specific version of SQL Source Control 4 are you using and what versions of Visual Studio, SSMS and SQL Server are you using?
Kind regards,
Alex
Have you visited our Help Center?
See https://documentation.red-gate.com/display/SOC4/SSDT+projects for more information.
(please note - as mentioned in the documentation, linking to SSDT projects is an unsupported feature)
Redgate Software
SSC 4.1.8.41
SSMS 12.0.4213.0
SQL Server 10.50.6220.0
Visual Studio Enterprise 2015 Version 14.0.24720.00 Update 1
SQL Source Control is linked to the folder that contains my .sqlproj file.
I do understand that linking to SSDT is unsupported. I appreciate any help you can give me on this but totally understand that this is not a defect in SSC.
I'm not sure right now when we're working on improving compatibility with ssdt projects, but when we do, this should be on the list of things to tackle.
Redgate Software