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.all.each do |account|
      account.last_login = account.created_at!

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

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

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.

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 }


Leave a Reply to Franklin Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: