SSDT doesn't like how FK constraints are handled

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

Comments

  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi Andre,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Do you have SQL Source Control linked to the same folder that contains the database project file (the .sqlproj file)? We have some settings that improve compatibility with SSDT, and those are automatically triggered if we see the database project file in the root folder that we're linked to.

    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)
    Development Lead
    Redgate Software
  • I'm using:
    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.
  • James BJames B Posts: 1,124 Silver 4
    I've just run through this to double check, and it looks like the bug reference Mike (EDIT, sorry, *Alex*!) mentioned (SOC-6031) is indeed the correct one. The behaviour is the same when linking to the .sqlproj folder.

    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.
    Systems Software Engineer

    Redgate Software

  • Thanks for the update. I will avoid making changes to tables via SQL Source Control until this is supported.
Sign In or Register to comment.