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

Batfish

Answer 1

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.

Preeminence

Your assumption about there already being rows in the table sounds correct, and it would be with just about any other rdbms. However, I noted in my answer that SQLite is an exception to this. This error appears even when the table is empty, so I posted a shorter solution. – Pyx

This is not correct - sqlite does not have a way to change columns. I don't know what change_column is, but it's not sqlite. – Guidon

@Benubird, change_column is an API method in Ruby on Rails migrations, which is what the OP was asking about. – Preeminence

The question title is about sqlite3, though. I believe that internally the change_column command is actually deleting the table and rebuilding it, which is definitely something worth being aware of - especially if you have a big table, or if you want to run this on a live db (as it will be unstable while running) – Guidon

@Benubird, yes, the code in the SQLite adapter for Rails does simulate more operations for ALTER TABLE by creating a new table with a new definition, copying data from the old table to the new table, and then dropping the old table. – Preeminence

the given link is currently dead – Newland

Answer 2

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.

Pyx

I'm curious how this works, since there is no ALTER COLUMN in SQLite. – Fulmar

@BrianOrtiz, the technique to do ALTER COLUMN with SQLite is convoluted. You need to copy the table to a temp one, drop the existing table, re-create the table with the modified column, copy the temp table data to the newly created table, and finally drop the temp table. This is most likely what ROR does. And also it's dumb that SQLite doesn't have this built-in. – Nosh

Thanks! Same idea worked for me on a Laravel project. – Ellsworth

Answer 3

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
  end

end

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
  end

end

Reasoned

If you want your migration to be reversible (rake db:rollback) add down method and replace change with up – Easy

Answer 4

You can add a column with a default value:

ALTER TABLE table1 ADD COLUMN userId INTEGER NOT NULL DEFAULT 1

Pepsinogen

the genius answer ! Thanks with one line code. – Gaberdine

Answer 5

The following migration worked for me in Rails 6:

class AddDivisionToProfile < ActiveRecord::Migration[6.0]
  def change
    add_reference :profiles, :division, foreign_key: true
    change_column_null :profiles, :division_id, false
  end
end

Note :division in the first line and :division_id in the second

API Doc for change_column_null

Martica

Answer 6

Not to forget that there is also something positive in requiring the default value with ALTER TABLE ADD COLUMN NOT NULL, at least when adding a column into a table with existing data. As documented in https://www.sqlite.org/lang_altertable.html#altertabaddcol:

The ALTER TABLE command works by modifying the SQL text of the schema stored in the sqlite_schema table. No changes are made to table content for renames or column addition. Because of this, the execution time of such ALTER TABLE commands is independent of the amount of data in the table. They run as quickly on a table with 10 million rows as on a table with 1 row.

The file format itself has support for this https://www.sqlite.org/fileformat.html

A record might have fewer values than the number of columns in the corresponding table. This can happen, for example, after an ALTER TABLE ... ADD COLUMN SQL statement has increased the number of columns in the table schema without modifying preexisting rows in the table. Missing values at the end of the record are filled in using the default value for the corresponding columns defined in the table schema.

With this trick it is possible to add a new column by updating just the schema, operation that took 387 milliseconds with a test table having 6.7 million rows. The existing records in the data area are not touched at all and the time saving is huge. The missing values for the added column come on-the-fly from the schema and the default value is NULL if not otherwise stated. If the new column is NOT NULL then the default value must be set to something else.

I do not know why there is not a special path for ALTER TABLE ADD COLUMN NOT NULL when the table is empty. A good workaround is perhaps to create the table right from the beginning.

Apollo