Allow to specify constraint name for default values

[dehghani-mehdi]

Hello,

Is there any way to specify constraint name for default values, for example:

        builder.Property(x => x.CreatedDate)
            .IsRequired()
            .HasColumnType("DATETIME")
            .HasDefaultValueSql("GETDATE()")
            .HasConstraintName("DF_MyConstraint");

Thanks

[divega]

@dehghani-mehdi There isn't currently a way to specify a default constraint name.

From the perspective of EF Core, this is not necessary because SQL Sever will generate one for you and we can always address the constraint (e.g. to drop it) without the name through the column (because there can only be one default constraint per column).

However we understand there may be other reasons this can be useful (in fact, you are the second person asking for this). It would be very helpful if you could explain why you need it.

[ErikEJ]

One reason I have experienced is major issues using sqlpackage / SSDT with unnamed constraints

[dehghani-mehdi]

I always using T-SQL instead of wizard for creating tables (or altering them) and I always specify constraint names, I like to have clean names for all things (we have naming convention in our team)
Also I have auto-update system (replacing files/ running updated SQL on the fly) for my projects and in some versions I have to drop some constraint and I need to know the name of them. finding the constraint is possible for specific column but not clean as

ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]

Maybe the reason that I have is not big enough, but adding this feature will be great.

[divega]

Two click-stops:

1. Generate a deterministic name and specify it in the DDL so that SSDT diffs are stable
2. Allow customizing the name

[CanadianBeaver]

@dehghani-mehdi However we understand there may be other reasons this can be useful (in fact, you are the second person asking for this).

And I am a 3rd person

[rclark381]

My team and I would also like this feature for the reasons mentioned. It provides flexibility for working with a database from different sources (besides through EF Core). Convention in constraint names makes that much easier.

[FaizulHussain]

Count me on too. We too give constraints meaningful names, it gives easy recognition and upgrades via dacpac to multiple databases seamless

[Greg-Smulko]

Another reason is the ability to compare automatically two database schemas. With names of default constraints generated automatically there is a lot of noise reported.

[xrkolovos]

It would be nice to have it in ForeignKey Attribute

[dyslexicanaboko]

Glad I found this post, I was about to open my own for the same topic. I think as a general rule of thumb "If I can do it in tSQL I should be able to do it in EF" is what should be observed. I don't like not knowing what my constraints are called, I like to specify everything in order to stay within explicit over implicit. Implicit makes me nervous.

I feel like SQL Server itself has already dropped the ball by giving constraint names the most inconsistent names possible - so I am just reacting to previous bad experiences of having many client databases with different constraint naming resembling a quarter GUID.

[gambled]

Add me to the list requesting this feature. We too have a coding standard with naming conventions for constraint for not only the reason mentioned by others in this thread, but also for portability/decreased ramp up time of resources from one product to another. It is quite handy when the design document is not detailed enough to call out every single data model rule.

[mikemertes]

Also requesting this feature. This has been bothersome to me for many many years.

I have always thought that default constraints created by EF should be explicitly named in the format "DF_TableName_ColumnName" instead of defaulting to SQL Server generated names (and that behavior should be configurable so the preferred method will be used). Additionally, it should be possible to user-define default constraint names with fluent api to override the context default.

My reasoning being that I should be able to use code first to model an existing schema that was hand designed with traditional tools (SSMS) and get a structurally identical schema when allowing EF to create a new instance in a different database. As someone else mentioned above, if I diff the source schema against a newly generated schema there is a lot of noise because the names don't match.

Creating or renaming the default constraints with migrations feels kludgy.

[mmichtch]

Very much in favor for this feature, comparing sachems generate too much nose because of auto generated constraint names

[christamlyn-bridge]

we can always address the constraint (e.g. to drop it) without the name through the column

@divega We have default constraints generated by migrations which now have an auto generated name. How do we drop these constraints through the column name only in a migration?

[davisnw]

I would like it even better if there were also an e.g.
[Default(Value = {myDefaultValue}, Constraint="DF_MyconstraintName")]
attribute that could be specified on the property in the entity class.

Or perhaps some additional options could meet that need using the [Column(...)] attribute.

[rebeccapowell]

Another +1. Having different pseudo-randomly named constraints on every different developer's machine, and in each deployment environment is horrific.

[Saibamen]

+1

[sarvasana]

+1

[thamathar]

+1

The need for this?
Well when I create table I all ways give name to the constraints, I know that SQL can generate them, but I prefer to give them the name,

For example we can give a name to a constraint using efcore, don't know why on the default value constraint (never the less, it is a constraint) we can't

[jreusserm]

add a +1 for me

[ricoisme]

+1

[sarvasana]

Guys, messages like +1 make github notifications pretty useless.

[thamathar]

Any one has found a workarround for this?!
I know it is possible to Edit the generated file, and add a query in there to give a proper name to the default constraint, but that would defeat the purpose of the auto generated file

Been trying to find a way to fix but but with no luck so far

[thamathar]

After spending some time on this, there is a work arround for this.

We just need to define the default value with the method the EF provide us, replace the IMigrationsSqlGenerator on the DbContextOptionsBuilder then we need to override the:

protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder, bool terminate = true)

Before he create the table, just go through the columns, get the default and save it, then pass the default value to null
After just add to the builder the "sql script"

ALTER TABLE TABLE ADD CONSTRAINT DEFAULT_VALUE_OF_TABLE_OF_COLUMN DEFAULT VALUE FOR COLUMN

This way it is possible to give a constraint name to the default value that we want, giving us total control over this

If needed I can provide the code for this

[gdoutre]

Hi thamathar. Yes can you please provide the code for this. It will be very useful to have deterministic names for default value constraints.

[thamathar]

This is the logic behind of what I did explain, just created this now from home to help out.

public class MSqlDbContext : DbContext
  {

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder
        .UseSqlServer("your connection string")
        .ReplaceService<IMigrationsSqlGenerator, MyMSqlMigrationSqlGenerator>();
    }

    private class MyMSqlMigrationSqlGenerator : SqlServerMigrationsSqlGenerator
    {
      public MyMSqlMigrationSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations)
      {
      }

      protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder, bool terminate = true)
      {
        Dictionary<string, string> defaultValues = new Dictionary<string, string>();
        foreach (var column in operation.Columns)
        {
          // If you use the DefaultValueSql
          if (!string.IsNullOrWhiteSpace(column.DefaultValueSql))
          {
            // We pass now the information that we need
            defaultValues.Add(column.Name, column.DefaultValueSql);
            // Now we pass it to NULL to discard the normal behavior
            column.DefaultValueSql = null;
          }
        }
        base.Generate(operation, model, builder, terminate);
        // Now we go through all values inside the defaultValues so we can do what we need
        foreach (var defaultValue in defaultValues)
        {
          builder
            .AppendLine($"ALTER TABLE {operation.Name} ")
            .AppendLine($"ADD CONSTRAINT NAME_FOR_CONSTRAINT_{operation.Name}_{defaultValue.Key} ")
            .AppendLine($"DEFALT {defaultValue.Value} FOR {defaultValue.Key};")
            .EndCommand();
        }
      }
    }
  }

This will override the normal behavior and remenber to do this for AlterColumn and AddColumn also, the logic its the same

Edit.
It seams the tag for code is a little crazy, sry about that

[avtc]

Another workaround, allowing named constrains also for not null bit/int/etc columns without DefaultValueSql specified:

public class MSqlDbContext : DbContext
{

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("your connection string")
            .ReplaceService<IMigrationsSqlGenerator, MyMSqlMigrationSqlGenerator>();
    }

    private class MyMSqlMigrationSqlGenerator : SqlServerMigrationsSqlGenerator
    {
        private string _currentColumnName;
        private string _currentTableName;
		
        public MyMSqlMigrationSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations)
        {
        }

        protected override void ColumnDefinition(
            string schema,
            string table,
            string name,
            ColumnOperation operation,
            IModel model,
            MigrationCommandListBuilder builder)
        {
            _currentTableName = table;
            _currentColumnName = name;
            base.ColumnDefinition(schema, table, name, operation, model, builder);
        }

        protected override void DefaultValue(
            object defaultValue,
            string defaultValueSql,
            string columnType,
            MigrationCommandListBuilder builder)
        {
            if (!string.IsNullOrEmpty(_currentTableName)
                && !string.IsNullOrEmpty(_currentColumnName)
                && !(defaultValueSql == null && defaultValue == null))
            {
                builder.Append($" CONSTRAINT DF__{_currentTableName}__{_currentColumnName}");
            }
            base.DefaultValue(defaultValue, defaultValueSql, columnType, builder);
        }
    }
}

[FastTracOnline]

This feature seems to have been pending for 4 years now with no resolution. Seems like a fair amount of interest. I know this bugs the heck out of me and was a huge issue on my last contract. Losing functionality is not progress in my book. I see someone even wrote most of the code for you guys so why the lack of responsiveness? Do you need some help?

[sarvasana]

@FastTracOnline I decided to no longer manage schemas with EF but with https://github.com/rr-wfm/MSBuild.Sdk.SqlProj
This allows perfect control over schema.

[roji]

@FastTracOnline we have many competing priorities, and although this issue has received some upvotes, it isn't even in the 1st page of most-requested features. In addition, there's a workaround to this, i.e. editing the scaffolded migrations to add the constraint name manually; that certainly isn't a perfect solution, but in many other cases there's no workaround at all.

I see someone even wrote most of the code for you guys so why the lack of responsiveness?

The code above may be enough for some cases, but to provide a full, comprehensive fix would probably require more designing and work. But you should definitely use the code above if it solves your problem.

[ajcvickers]

@FastTracOnline This issue has 46 votes (👍). This puts it in 29th position in the list of most requested features. See the planning process for more information on how we decide what to work on.

[avtc]

Updated workaround (fixed alter table), allowing named constrains also for not null bit/int/etc columns without DefaultValueSql specified:

public class MSqlDbContext : DbContext
{

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("your connection string")
            .ReplaceService<IMigrationsSqlGenerator, MyMSqlMigrationSqlGenerator>();
    }

    private class MyMSqlMigrationSqlGenerator : SqlServerMigrationsSqlGenerator
    {
        private string _currentColumnName;
        private string _currentTableName;
		
        public MyMSqlMigrationSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations)
        {
        }

        protected override void ColumnDefinition(
            string schema,
            string table,
            string name,
            ColumnOperation operation,
            IModel model,
            MigrationCommandListBuilder builder)
        {
            _currentTableName = table;
            _currentColumnName = name;
            base.ColumnDefinition(schema, table, name, operation, model, builder);
        }

        protected override void Generate(
            AlterColumnOperation operation,
            IModel model,
            MigrationCommandListBuilder builder)
        {
            _currentTableName = operation.Table;
            _currentColumnName = operation.Name;
            base.Generate(operation, model, builder);
        }

        protected override void DefaultValue(
            object defaultValue,
            string defaultValueSql,
            string columnType,
            MigrationCommandListBuilder builder)
        {
            if (!string.IsNullOrEmpty(_currentTableName)
                && !string.IsNullOrEmpty(_currentColumnName)
                && !(defaultValueSql == null && defaultValue == null))
            {
                builder.Append($" CONSTRAINT DF__{_currentTableName}__{_currentColumnName}");
            }
            base.DefaultValue(defaultValue, defaultValueSql, columnType, builder);
        }
    }
}

[fairking]

As a workaround to drop a randomly generated constraint:

DECLARE @ObjectName NVARCHAR(100);

SELECT @ObjectName = OBJECT_NAME([default_object_id]) 
FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[MyTable]') AND [name] = 'MyColumn';

EXEC('ALTER TABLE [MyTable] DROP CONSTRAINT ' + @ObjectName);

You just replace [MyTable] and MyColumn with yours.

Then you can reapply your own constrain names.

[wzuqui]

+1

[AirtonBorges]

+1

[CanadianBeaver]

They have not added the constraint name in 6 years?..

[dw-ob]

+1

[diomedes02]

+1

[sciros]

+1 this should be a thing. Looks like it's made it into the top 20 requests but likely still a ways out from being worked on.

[Margasoiu-Luca]

+1
Would be a small change that would help teams with naming conventions.

[MichaelSL]

@roji @ajcvickers I'll add another use case for this one:
we have to track all database changes due to compliance requirements. Every time after the deploy we generate a diff between old (pre-deploy) and new (post-deploy) schema definition for all our dbs. Some databases are created for new tenants and in those databases the default value constraint always has a new name. Our report shows this db as changed, but the only thing different is the constraint name.

[kukhalilov]

+1

[00012256]

+1

[asbjornu]

It would be nice to have it in ForeignKey Attribute

Indeed, all constraints created by EF Core should be explicitly nameable. I don't mind the default behavior where constraints are implicitly auto-named by SQL Server, but I do mind not being able to explicitly name them myself.

[Charles113]

I need this because i want to compare the generated SQL Schema of two databases to see if they're equal.
If everything has different names then its hard to spot differences.

[CanadianBeaver]

@AndriySvyryd, what is wrong with this feature for so many years?

[Tdue21]

+1 from here as well. As other have mentioned, schema comparison is one major reason for this.
I cannot remember the exact case, but I have also seen issues with generated constraint names whenever we've had to drop the default constraints for whatever reason.

[jscarle]

+1 as this would help to ensure database schemas are comparable and stable between environments.