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

Question

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

Answer 1

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
  end
end

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.