What are the challenges you face when working across database platforms? Take the survey
Options

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

a_ted_wa_ted_w Posts: 5 Bronze 1
edited September 3, 2019 9:27PM in SQL Change Automation
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.

Best Answer

  • Options
    a_ted_wa_ted_w Posts: 5 Bronze 1
    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.)

Answers

  • Options
    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.
  • Options
    ilundhildilundhild Posts: 1 New member
    edited September 4, 2019 3:32PM
    @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
  • Options
    DiogoDiogo Posts: 67 Silver 5
    edited September 4, 2019 7:25PM
    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.
  • Options
    RickCRickC Posts: 3 Bronze 1
    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?
  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    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
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    RickCRickC Posts: 3 Bronze 1

    @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.  ;)

Sign In or Register to comment.