title: Removing a default constraint after adding a new column in code first
body: <p>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.</p>
  
  <p>After adding the column, I then drop the default constraint.</p>
  
  <pre><code>public override void Up()
  {
      AddColumn("dbo.SomeTable", "NewColumn", c =&gt; c.Int(nullable: false));
      Sql(Helpers.DropDefaultConstraint("dbo.SomeTable", "NewColumn"));
  }
  </code></pre>
  
  <p>...</p>
  
  <pre><code>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);
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>public override void Up()
  {
      AddColumn("dbo.SomeTable", "NewColumn", c =&gt; c.Int());
      Sql("UPDATE dbo.SomeTableSET NewColumn= 1");
      AlterColumn("dbo.SomeTable", "NewColumn", c =&gt; c.Int(nullable: false));
  }
  </code></pre>
  
  <p>PROS: Seems simpler/cleaner</p>
  
  <p>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.</p>
  
  <p>Which method is preferable? Or is there a better way that I'm missing? </p>
  
  <p>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.</p>