[SOLVED] Cannot add a NOT NULL column with default value NULL in Sqlite3

Cannot add a NOT NULL column with default value NULL in Sqlite3

I am getting the following error while trying to add a NOT NULL column to an existing table. Why is it happening ?. I tried rake db:reset thinking that the existing records are the problem, but even after resetting the DB, the problem persists. Can you please help me figure this out.

Migration File

class AddDivisionIdToProfile < ActiveRecord::Migration
  def self.up
    add_column :profiles, :division_id, :integer, :null => false
  end

  def self.down
    remove_column :profiles, :division_id
  end
end

Error Message

SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL: ALTER TABLE "profiles" ADD "division_id" integer NOT NULL

Solution

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.