SQL Change Automation giving "constraint name must be specified" errors

a_ted_w

I just installed version 4.0.19246.10324 of SCA and I'm now getting errors for Default Constraints WITHOUT names.
The really strange thing is it only flags SOME of the Defaults without names, but not all of them.

a_ted_w

Turns out that the ones that were NOT flagged were Defaults on #tempTables or @tableVariables, this was not obvious on first pass.
The only fix for these errors was to add the Constraint Name to the Defaults. (most were for "Deploy Once" scripts that had already been Deployed.)

Diogo

Hi @atedw2 we did some improvements on this functionality for the version you mentioned.
Could you please post some examples of the default constraints that are not being flagged? It will also help to see if they're wrapped in some BEGIN/END, IF/ELSE or TRY/CATCH block as that's what where our change focused on.

ilundhild

@Diogo
I'll just comment that I am seeing the same issue.  Here's a pretty close example to what was causing issues for us:

IF NOT EXISTS(

    SELECT *

    FROM sys.columns 

    WHERE [Name] = N'NewBit'

      AND Object_ID = Object_ID(N'MyTable'))

BEGIN

    ALTER TABLE dbo.MyTable

    ADD [NewBit] BIT NOT NULL DEFAULT ((0))

END

Diogo

ilundhild the script you are showing should produce the error message as using constraints with system named for constraints is a bad practice because this way the constraint name will not be deterministic as our error message mentions and that will cause your constraint name to vary across different environments.

RickC

This morning, I updated VS2019 to v16.4.5 and immediately afterwards, I updated SCA to v4.2.20064.1605.

Now, my SCA database projects won't build or deploy. The error messages is as follows.

c:\path\to\semver\folder\001_MyMigrationScript.sql(7,49): Error:  : A constraint name must be specified (before the constraint definition eg. CONSTRAINT [MyConstraintName] DEFAULT (0)). This is needed in order to ensure that database builds are deterministic.

This error message is in an old migration script that has already been deployed to production. It is complaining because there is a PRIMARY KEY clause on an identity column rather than a separate CONSTRAINT [PK_TableName] PRIMARY KEY (ID) clause with an explicit name.

Editing the migration script will not change the database since the script will not be deployed again. Besides, changing an historic migration script goes completely against the purpose of a migration-script based approach anyway, doesn't it?

@Diogo , you reponded to @ilundhild saying "...using constraints with system named (sic) for constraints is a bad practice..." OK. We get that. However, SCA should not -- and, indeed, cannot -- suddenly declare that things that happened in the past must be fixed before moving forward. This breaks the DevOps flow.

I am working on a hotfix that I need to get out the door quickly and I can't even build my database project? Yikes!

How do we get a fix for this immediately, if not sooner?

Sergio R

I am sorry that this is causing issues in your case, this behavior was changed in version 4.0.19246 as a result of a bug fix (SCA-2637).

We've always intended not to allow unnamed constraints and we already detected and prevented this from building in most cases, however as a result of the bug mentioned above this validation was not being applied correctly to nested CREATE\ALTER table statements.

Going forward the best course of action here would be to explicitly name the unnamed constraints in the historical migration scripts.

In the meantime and since you're on a schedule, you can downgrade to the latest version that doesn't have this fix:

Download link: https://download.red-gate.com/checkforupdates/SQLChangeAutomation/SQLChangeAutomation_4.0.19242.10233.exe

RickC

@Sergio R

Downgrading – and being stuck there – is not an option. So, I am left with only one option -- changing history. I have to tell you, this is at the very least unsatisfying and possibly just downright wrong.

On a (slightly) more positive note, now that I’ve made the decision to go to the dark side (i.e., changing historical records), I am relieved to find that we only have one unnamed constraint in our database. Still, this is an annoyance that should never have happened.

Red Gate should consider the impact that bug fixes like SCA-2637 have on their customers. Sometimes doing the right thing from a product feature perspective (i.e., preventing unnamed constraints) is not the right thing to do for your customers -- without whom Red Gate would not be so successful. Perhaps an introduction of this as a warning that later escalates to an error would have been the kinder, gentler and more customer-friendly approach.