Cannot add a NOT NULL column with default value NULL in Sqlite3
database, ruby-on-rails, sqlite, sqlite3-ruby
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.
Problem
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