Cannot add a NOT NULL column with default value NULL in Sqlite3
This is (what I would consider) a glitch with SQLite. This error occurs whether there are any records in the table or not.
When adding a table from scratch, you can specify NOT NULL, which is what you're doing with the ":null => false" notation. However, you can't do this when adding a column. SQLite's specification says you have to have a default for this, which is a poor choice. Adding a default value is not an option because it defeats the purpose of having a NOT NULL foreign key - namely, data integrity.
Here's a way to get around this glitch, and you can do it all in the same migration. NOTE: this is for the case where you don't already have records in the database.
class AddDivisionIdToProfile < ActiveRecord::Migration def self.up add_column :profiles, :division_id, :integer change_column :profiles, :division_id, :integer, :null => false end def self.down remove_column :profiles, :division_id endend
We're adding the column without the NOT NULL constraint, then immediately altering the column to add the constraint. We can do this because while SQLite is apparently very concerned during a column add, it's not so picky with column changes. This is a clear design smell in my book.
It's definitely a hack, but it's shorter than multiple migrations and it will still work with more robust SQL databases in your production environment.
You already have rows in the table, and you're adding a new column division_id
. It needs something in that new column in each of the existing rows.
SQLite would typically choose NULL, but you've specified it can't be NULL, so what should it be? It has no way of knowing.
See:
- Adding a Non-null Column with no Default Value in a Rails Migration (2009, no longer available, so this is a snapshot at archive.org)
- Adding a NOT NULL Column to an Existing Table (2014)
That blog's recommendation is to add the column without the not null constraint, and it'll be added with NULL in every row. Then you can fill in values in the division_id
and then use change_column
to add the not null constraint.
See the blogs I linked to for an description of a migration script that does this three-step process.
If you have a table with existing rows then you will need to update the existing rows before adding your null
constraint. The Guide on migrations recommends using a local model, like so:
Rails 4 and up:
class AddDivisionIdToProfile < ActiveRecord::Migration class Profile < ActiveRecord::Base end def change add_column :profiles, :division_id, :integer Profile.reset_column_information reversible do |dir| dir.up { Profile.update_all division_id: Division.first.id } end change_column :profiles, :division_id, :integer, :null => false endend
Rails 3
class AddDivisionIdToProfile < ActiveRecord::Migration class Profile < ActiveRecord::Base end def change add_column :profiles, :division_id, :integer Profile.reset_column_information Profile.all.each do |profile| profile.update_attributes!(:division_id => Division.first.id) end change_column :profiles, :division_id, :integer, :null => false endend