SERIAL in SQLite

#rails #webdev #database #sqlite

Today I received the following error when creating a new Rails ActiveRecord with CourseStatus.create!(name: 'Klettern für Anfänger') in a SQLite database:

SQLite3::ConstraintException: NOT NULL constraint failed: course_statuses.id

The problem was that the schema.rb defined the primary key as serial (originally created by a Rails 5.0 migration):

create_table "course_statuses", id: :serial do |t|
  t.string "name", limit: 255
  t.datetime "created_at"
  t.datetime "updated_at"
end

Since SQLite is dynamically typed, the id primary key will simply be declared with the non-existent type serial. And this means that the SQLite adapter will not mark the primary key to be auto-incrementable. Only INTEGER types can be auto-incremented in SQLite.

The solution is to remove all id: :serial from the schema.rb (then db:schema:load db:schema:dump) and leave the decision about how to create the tables completely up to the database adapter.