Removing a default constraint after adding a new column in code first

Question

When adding a new non-nullable column to a table using code first migrations, it will automatically create a default value for you. This makes sense because existing rows need to have a value for the new column (since it can't be null). That's fine, but after that value is assigned everywhere, I don't want it anymore. It's non-nullable, because I want to make sure that a value is always explicitly inserted. If everything defaults to '' or 0 then I'll have magic strings. So anyway, I can up with a solution, that I'm not thrilled of, but it works.

After adding the column, I then drop the default constraint.

public override void Up()
{
    AddColumn("dbo.SomeTable", "NewColumn", c => c.Int(nullable: false));
    Sql(Helpers.DropDefaultConstraint("dbo.SomeTable", "NewColumn"));
}

...

public static string DropDefaultConstraint(string table, string column)
{
    return string.Format(@"
        DECLARE @name sysname

        SELECT @name = dc.name
        FROM sys.columns c
        JOIN sys.default_constraints dc ON dc.object_id = c.default_object_id
        WHERE c.object_id = OBJECT_ID('{0}')
        AND c.name = '{1}'

        IF @name IS NOT NULL
            EXECUTE ('ALTER TABLE {0} DROP CONSTRAINT ' + @name)
        ",
        table, column);
}

PROS: Once the helper method is implemented, I just need to add one simple line to drop the constraint. CONS: Seems unnecessary to create an index just to delete it.

Another approach would be to alter the generated migration, so we add it has nullable, update all the values and then make it non-nullable.

public override void Up()
{
    AddColumn("dbo.SomeTable", "NewColumn", c => c.Int());
    Sql("UPDATE dbo.SomeTableSET NewColumn= 1");
    AlterColumn("dbo.SomeTable", "NewColumn", c => c.Int(nullable: false));
}

PROS: Seems simpler/cleaner

CONS: Have to alter my constraints temporarily (I assume this runs in a transaction and thus we shouldn't be allowing bad data in). The update might be slow on big tables.

Which method is preferable? Or is there a better way that I'm missing?

Note: I have demonstrated the case where you are adding and cleaning column definitions in one go. If you are just cleaning up defaults from previous migrations, the second approach isn't helpful.

Solution 1 works great for me. The best I have found so far. Just want to add that you can specify the default-value explicitely instead of relying on EF to provide one: c.String(nullable: false, defaultValue: "n/a") , for example. In case you want to process the old data in a controlled manner. — Daniel Hillebrand
– Daniel Hillebrand, Commented Mar 27, 2018 at 11:37

Andriy M · Accepted Answer · 2012-06-03 19:44:09Z

12

Personally I can see nothing wrong with the first approach. Yes, you have to create a default constraint to add a non-nullable column to a non-empty dataset. And yes, you have to delete it afterwards if you need to make sure the new column is always added explicitly in the future, as per your requirement.

And even if you still have issues with this approach, the problem is, there's most likely no other alternative apart from your second approach. And the cost of updating a possibly large table with a default value would seem to me greater than the cost of creating and immediate dropping a default constraint.

I might consider a slight modification, though: I might create the constraint in SQL to avoid the fuss of looking up the default name assigned by the engine, something like this:

Sql("ALTER TABLE tablename
  ADD columnname type NOT NULL
  CONSTRAINT DF_tablename_columnname DEFAULT defaultvalue");

(Could be rewritten to use a helper function.)

Dropping a constraint would then be as trivial as executing a single ALTER TABLE statement:

Sql("ALTER TABLE tablename
  DROP CONSTRAINT DF_tablename_columnname");

(Again, a helper function could easily be used here.)

But that all might be a T-SQL developer in me speaking louder than a C# one. So you might very well go with the code like the example you've posted.

answered Jun 3, 2012 at 19:44

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Friedman Over a year ago

Thanks for validating my thoughts. I did consider creating the constraint myself, but I prefer to use AddColumn as it is easy and consistent to read and also allows for some other constraints, like unique. I decided to stick with option 1. Thanks.

TylerH · Accepted Answer · 2020-12-10 21:51:39Z

Solution using EF code first to remove default value constraint from column- If columns is no more having NULL values[Not Null], you can use below Code First approach to remove below SQL script from your table. Set "defaultValueSql: null" in your migration file.

Remove below Default column value constraint ----> ALTER TABLE [dbo].[Attachments] ADD DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [DocumentUniqueId]

Apply this Code First C#---->

protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<Guid>(
                name: "ColumnName",
                schema: "dbo",
                table: "TableName",
                type: "uniqueidentifier",
                nullable: false,
                **defaultValueSql: null,**
                oldClrType: typeof(Guid),
                oldType: "uniqueidentifier");
        }

I didn't get anywhere so I am posting it to help others, helpful for database cleanup.

Collectives™ on Stack Overflow

Removing a default constraint after adding a new column in code first

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related