How to drop constraint DF_ automatically in the migration

Question

I have table which looks like this:

create table job_results
(
    id                bigint identity
        constraint pk_job_results
            primary key,
    created           datetime2,
    deleted           datetime2,
    updated           datetime2,
    job_id            varchar(255)      not null
        constraint fk_job_results__job_id
            references jobs,
    location          varchar(1024)     not null,
    sent_successfully bit     default 1 not null,
    send_attempts     tinyint default 0 not null,
    is_partial        tinyint default 1 not null
)

Now, I use flyway for the migrations and I came across the issue, when I'm unable to create migration which would change is_partial from tinyint to bit, because the MSSQL creates constraint, which prevents the migration named DF__job_resul__is_pa__3632CAAD - usually I would drop it, but this constraint has the suffix 3632CAAD which is generated randomly and for that reason, when I clean db and run flyway again, the constraint won't be dropped because it does not exist and I don't know the current suffix to drop it.

So, is there a way, how to drop this particular constraint automatically via some regex or something else?

My current migration is following:

alter table job_results
    alter column is_partial bit not null;
go

And the error message:

SQL State  : S0001
Error Code : 5074
Message    : The object 'DF__job_resul__is_pa__3632CAAD' is dependent on column 'is_partial'.

Answer 1

I eventually managed to use this https://stackoverflow.com/a/10758357/7169288 solution for my case and the final migration is therefore following:

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('job_results') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'is_partial' AND object_id = OBJECT_ID(N'job_results'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE job_results DROP CONSTRAINT ' + @ConstraintName)
go

alter table job_results
    alter column is_partial bit not null;
go