Ruby on Rails: How do I add a not null constraint to an existing column using a migration?

Question

In my Rails (3.2) app, I have a bunch of tables in my database but I forgot to add a few not null constraints. How can I write a migration which adds not null to an existing column?

Eulalie

Answer 1

For Rails 4+, nates' answer (using change_column_null) is better.

Pre-Rails 4, try change_column.

Blanca

Be careful with this approach -- if you had other attributes about that column (for example a :limit constraint), you need to repeat those attributes when using change_column, or they will be lost. For this reason, I prefer to use change_column_null – Club

Note that this generates an IrreversibleMigration which may not be what you want. – Elegist

@NicNilov are you talking about the answer OR Nathan Wallace's comment? – Twomey

@Twomey I was talking about the answer, sorry for not being specific enough. – Elegist

@NicNilov no dw I did think that though I just wanted to double check :) – Twomey

Answer 2

You can also use change_column_null:

change_column_null :table_name, :column_name, false

Macey

I had to change it for a bunch of columns and this doesn't require specifying the column type for each column, much better! – Noyes

This is the better answer. In my database, I was adding a null constraint on a column with pre-existing null values. change_column would not update those values. Per the documentation, change_column_null has an optional fourth value which is the new value for the update. – Delindadelineate

interesting side effect.... rolling back the migration will set the field to the opposite (false -> true). So if you create the migration for several fields to add a null constraint, and some fields ALREADY had a null constraint, then rollback the migration, it will REMOVE the null constraint from any field that already had it. – Ambages

The forth option will set the default value for those entries, where the column is null indeed. But be carefull! It can cause downtime if it's a big table. Better first to backfill those records in batches, and then add a constraint without forth option. – Saintpierre

Answer 3

1. Add column with default value

2. Remove default value

add_column :orders, :items, :integer, null: false, default: 0
change_column :orders, :items, :integer, default: nil

Burglar

this is correct solution when you need to add new column that is not null, you need to first define that it has default value because SQLLite will complain (Cannot add a NOT NULL column with default value NULL), and then remove it! – Dariodariole

Answer 4

If you are using it on a new create migration script/schema here is how we can define it

class CreateUsers < ActiveRecord::Migration[5.2]
  def change
    create_table :users do |t|
    t.string :name, null: false     # Notice here, NOT NULL definition
    t.string :email, null: false
    t.string :password, null: false
    t.integer :created_by
    t.integer :updated_by 

    t.datetime :created_at
    t.datetime :updated_at, default: -> { 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' }
   end
  end
end

Salome

Answer 5

In my approach, I add NOT NULL constraint to columns i need in my existing migrated migration. After that, I reset all my migrations by using this command:

rake db:migrate:reset

This will drop the database, create it again and run all the migrations. You can check your changes in schema.rb.

If you have few columns in simple migrations, you can use this approach.

Hokum