[Ruby on Rails][SQLite3] SQLException: Cannot add a NOT NULL column with default value NULL
In Ruby on Rails, I encountered an error when trying to add a role column with NOT NULL constraint to the Users table.
■ Migration file that caused the error
db/migrate/20120415053115_add_role_to_users.rb
class AddRoleToUsers < ActiveRecord::Migration
def change
add_column :users, :role, :string, :null => false
end
end
An error occurred when running rake db:migrate.
■ Error Message
$ rake db:migrate
== AddRoleToUsers: migrating ============================
-- add_column(:users, :role, :string, {:null=>false})
rake aborted!
An error has occurred, this and all later migrations canceled:
SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL: ALTER TABLE "users" ADD "role" varchar(255) NOT NULL
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
In SQLite3, it seems you need to specify a DEFAULT value other than NULL when adding a NOT NULL constraint.
If you don’t specify DEFAULT, NULL gets inserted, which seems to be the cause of this error.
When adding a NOT NULL constraint, please specify a DEFAULT value other than NULL・Source: Cactus: Modifying Tables and Adding Columns in SQLite - ALTER TABLE
So, you can solve this by adding the column first, then using change_column to change the role column to NOT NULL as follows:
class AddRoleToUsers < ActiveRecord::Migration
def change
add_column :users, :role, :string, :null => false
change_column :users, :role, :string, :null => false
end
end
Also, simply specifying a DEFAULT value is another approach.
class AddRoleToUsers < ActiveRecord::Migration
def change
add_column :users, :role, :string, :null => false, :default => ""
end
end
That’s all from the Gemba.
【Reference】