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.

Answer 1

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.