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

by

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.

More Related Contents:

  1. Change from SQLite to PostgreSQL in a fresh Rails project
  2. sqlite3 gem fails to install
  3. Boolean Validation
  4. cannot load such file — sqlite3/sqlite3_native (LoadError) on ruby on rails
  5. Deploying RoR app to Heroku with SQLite 3 fails
  6. Rails :include vs. :joins
  7. Using Rails, how can I set my primary key to not be an integer-typed column?
  8. Why can you not have a foreign key in a polymorphic association?
  9. Postgres could not connect to server
  10. How do I install sqlite3 for Ruby on Windows?
  11. Composite primary keys versus unique object ID field
  12. Generic Ruby solution for SQLite3 “LIKE” or PostgreSQL “ILIKE”?
  13. How to sort text in sqlite3 with specified locale?
  14. Alias for column names in Rails
  15. Clean way to find ActiveRecord objects by id in the order specified
  16. Rails 3 SQLite3 Boolean false
  17. Multiple databases in Rails
  18. Ruby: SQLite3::BusyException: database is locked:
  19. Heroku deployment failed because of sqlite3 gem error
  20. What is the best way to seed a database in Rails?
  21. Connecting to multiple databases in ruby on rails
  22. HEROKU – cannot run git push heroku master [duplicate]
  23. A migration to add unique constraint to a combination of columns
  24. Pushing Rails with SQLite3 to Heroku fails [duplicate]
  25. How do I set up the database.yml file in Rails?
  26. delete_all vs destroy_all?
  27. Why can’t I install the SQLite gem?
  28. How do I avoid a race condition in my Rails app?
  29. Rails: store translations in database
  30. Errors of pushing rails app to Heroku error occurred while installing sqlite3, and Bundler cannot continue [duplicate]

Leave a Comment