Adding a Non-null Column with no Default Value in a Rails Migration

This is something that I’ve often needed to do: add a new column to the DB that has a non-null constraint, but also doesn’t have a default value. There are a some options:

• Forget the DB constraint and use `validates_presence_of` in the model
• Add a default value for the new column with non-null and then remove the default
• Add the column without a default value, then alter it to be non-null

The first method of simply using `validates_presence_of` won’t cut it for me because that doesn’t actually make guarantees on the data stored in the DB from interfaces outside the of application.

Adding the new non-null column with a default value and then altering it to remove the default would probably be the best choice if you just need a standard value for all your historic data.

The third method is how I did it, and I like it best for any data that can be computed to a reasonable value to start out.Here’s the source for my migration:

class AddLoginMetricsToAccounts < ActiveRecord::Migration
  def self.up
    add_column :accounts, :last_login, :datetime
    add_column :accounts, :total_logins, :integer, :null => false, :default => 1

    Account.reset_column_information

    Account.all.each do |account|
      account.last_login = account.created_at
      account.save!
    end

    change_column :accounts, :last_login, :datetime, :null => false
  end

  def self.down
    remove_column :accounts, :total_logins
    remove_column :accounts, :last_login
  end
end

I’m adding a last_login column that I want to be non-null, but because it has no default value most DBs won’t allow the new column to be added (it violates data integrity). So the thing is to add the column without a a non-null constraint, populate it with acceptable values, and then to change the column to include the constraint.

Hope this comes in handy!

Author: Daniel X

Heretic priest of the Machine God. I enjoy crawling around in Jeff Bezo's spaceship, bringing technology to the people, and long walks outside of time and space. View all posts by Daniel X