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!

One thought on “Adding a Non-null Column with no Default Value in a Rails Migration

  1. Thank you, it’s perfect;
    class AddWechatUserIdToWechatMessages < ActiveRecord::Migration
    def change
    #add_column :wechat_messages, :wechat_user_id, :integer, { null: false }
    #SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL
    add_column :wechat_messages, :wechat_user_id, :integer, { null: false , default: 0 }
    change_column :wechat_messages, :wechat_user_id, :integer, { null: false }
    end
    end

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>