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?
Add a comment
|
6 Answers
You can also use change_column_null:
change_column_null :table_name, :column_name, false
4 Comments
Dorian
I had to change it for a bunch of columns and this doesn't require specifying the column type for each column, much better!
Merovex
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.
jpw
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.
user2992971
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.
For Rails 4+, nates' answer (using change_column_null) is better.
Pre-Rails 4, try change_column.
5 Comments
Nathan Wallace
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
Nic Nilov
Note that this generates an
IrreversibleMigration which may not be what you want.
Mark
@NicNilov are you talking about the answer OR Nathan Wallace's comment?
Nic Nilov
@Mark I was talking about the answer, sorry for not being specific enough.
Mark
@NicNilov no dw I did think that though I just wanted to double check :)
Add column with default value
Remove default value
add_column :orders, :items, :integer, null: false, default: 0
change_column :orders, :items, :integer, default: nil
1 Comment
Milan
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!
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
Comments
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.
Comments
If need simultaneously change NOT NULL constraints in the table in single query, use change_null table definition:
change_table :table_name, bulk: true do |t|
t.change_null :first_column_name, false
t.change_null :second_column_name, true
end
It will generate such SQL:
ALTER TABLE table_name
ALTER COLUMN first_column_name SET NOT NULL,
ALTER COLUMN second_column_name DROP NOT NULL;
If pass extra argument:
t.change_null :third_column_name, false, '123'
It will add UPDATE query:
UPDATE table_name
SET third_column_name = '123'
WHERE third_column_name IS NULL;
