148

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?

Rafael Tavares
  • 5,678
  • 4
  • 32
  • 48
David Robertson
  • 1,809
  • 3
  • 12
  • 12

5 Answers5

303

You can also use change_column_null:

change_column_null :table_name, :column_name, false
nates
  • 8,312
  • 5
  • 32
  • 28
  • 1
    I had to change it for a bunch of columns and this doesn't require specifying the column type for each column, much better! – Dorian Jan 08 '15 at 12:19
  • 1
    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. – Merovex Jan 01 '16 at 05:22
  • 1
    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. – jpw Jan 16 '19 at 18:50
  • 1
    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. – user2992971 Sep 06 '19 at 14:44
97

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

Pre-Rails 4, try change_column.

Dan Wich
  • 4,923
  • 1
  • 26
  • 22
  • 27
    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` – Nathan Wallace Mar 09 '15 at 15:51
  • Note that this generates an `IrreversibleMigration` which may not be what you want. – Nic Nilov Feb 17 '17 at 14:30
  • @NicNilov are you talking about the answer OR Nathan Wallace's comment? – Mark Dec 28 '17 at 16:10
  • @Mark I was talking about the answer, sorry for not being specific enough. – Nic Nilov Dec 28 '17 at 20:03
  • @NicNilov no dw I did think that though I just wanted to double check :) – Mark Dec 28 '17 at 20:04
12

  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
Rafael Tavares
  • 5,678
  • 4
  • 32
  • 48
rndrfero
  • 312
  • 2
  • 6
  • 2
    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! – Milan Feb 01 '17 at 22:32
3

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
Manjunath Reddy
  • 1,039
  • 2
  • 13
  • 22
0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Thien Ngan
  • 96
  • 1
  • 6