How to change a nullable column to not nullable in a Rails migration?

Question

I created a date column in a previous migration and set it to be nullable. Now I want to change it to be not nullable. How do I go about doing this assuming there are null rows in that database? I'm ok with setting those columns to Time.now if they're currently null.

Answer 1

If you do it in a migration then you could probably do it like this:

# Make sure no null value exist
MyModel.where(date_column: nil).update_all(date_column: Time.now)

# Change the column to not allow null
change_column :my_models, :date_column, :datetime, null: false

Answer 2

In Rails 4, this is a better (DRYer) solution:

change_column_null :my_models, :date_column, false

To ensure no records exist with NULL values in that column, you can pass a fourth parameter, which is the default value to use for records with NULL values:

change_column_null :my_models, :date_column, false, Time.now

Answer 3

Rails 4 (other Rails 4 answers have problems):

def change
  change_column_null(:users, :admin, false, <put a default value here> )
  # change_column(:users, :admin, :string, :default => "")
end

Changing a column with NULL values in it to not allow NULL will cause problems. This is exactly the type of code that will work fine in your development setup and then crash when you try to deploy it to your LIVE production. You should first change NULL values to something valid and then disallow NULLs. The 4th value in change_column_null does exactly that. See documentation for more details.

Also, I generally prefer to set a default value for the field so I won't need to specify the field's value every time I create a new object. I included the commented out code to do that as well.

Answer 4

Create a migration that has a change_column statement with a :default => value.

change_column :my_table, :my_column, :integer, :default => 0, :null => false

See: change_column

Depending on the database engine you may need to use change_column_null

Answer 5

Rails 4:

def change
  change_column_null(:users, :admin, false )
end

Answer 6

In Rails 4.02+ according to the docs there is no method like update_all with 2 arguments. Instead one can use this code:

# Make sure no null value exist
MyModel.where(date_column: nil).update_all(date_column: Time.now)

# Change the column to not allow null
change_column :my_models, :date_column, :datetime, null: false

Answer 7

You can't use add_timestamps and null:false if you have existing records, so here is the solution :

def change
  add_timestamps(:buttons, null: true)

  Button.find_each { |b| b.update(created_at: Time.zone.now, updated_at: Time.zone.now) }

  change_column_null(:buttons, :created_at, false)
  change_column_null(:buttons, :updated_at, false)
end

Answer 8

Per the Strong Migrations gem, using change_column_null in production is a bad idea because it blocks reads and writes while all records are checked.

The recommended way to handle these migrations (Postgres specific) is to separate this process into two migrations.

One to alter the table with the constraint:

class SetSomeColumnNotNull < ActiveRecord::Migration[6.0]
  def change
    safety_assured do
      execute 'ALTER TABLE "users" ADD CONSTRAINT "users_some_column_null" CHECK ("some_column" IS NOT NULL) NOT VALID'
    end
  end
end

And a separate migration to validate it:

class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.0]
  def change
    safety_assured do
      execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "users_some_column_null"'
    end
  end
end

The above examples are pulled (and slightly altered) from the linked documentation. Apparently for Postgres 12+ you can also add NOT NULL to the schema and then drop the constraint after the validation has been run:

class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.0]
  def change
    safety_assured do
      execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "users_some_column_null"'
    end

    # in Postgres 12+, you can then safely set NOT NULL on the column
    change_column_null :users, :some_column, false
    safety_assured do
      execute 'ALTER TABLE "users" DROP CONSTRAINT "users_some_column_null"'
    end
  end
end

Naturally, this means your schema will not show that the column is NOT NULL for earlier versions of Postgres, so I'd also advise setting a model level validation to require the value to be present (though I'd suggest the same even for versions of PG that do allow this step).

Further, before running these migrations you'll want to update all existing records with a value other than null, and make sure any production code that writes to the table is not writing null for the value(s).