How to add reference column migration in Rails 6 with SQLite

Question

What is the correct way to add reference column migration in Rails 6 without getting SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL?

I can hack it to get it working; but, I am preparing a tutorial for a grad class, so I want to make sure I'm doing it "by the book".

The starting point is a Post class (think "blog post"). I want to add an Author class and set up an 1-to-many relationship between authors and posts. After adding the author class and running the corresponding migration, I then create a migration to add an Author reference to Post:

rails g migration AddAuthorToPost author:references

This command generates:

class AddAuthorToPost < ActiveRecord::Migration[6.0]
  def change
    add_reference :posts, :author, null: false, foreign_key: true
  end
end

The problem is, of course, that SQLite complains because it won't tolerate the potential for a null foreign key --- even if the Post table is empty: (How to solve "Cannot add a NOT NULL column with default value NULL" in SQLite3?)

I looked back at the previous year's tutorial (prepared by a different instructor) and the generator did not add null: false to the migration. (See also Add a reference column migration in Rails 5)

Removing null: false from the migration allows the migration to run; but, "disabling safety features" doesn't seem appropriate in a classroom setting :)

Is there a better way to do this?

The usual process is (1) add the column with `null: true`, (2) initialize it with proper values, (3) set it to `null: false`. If the table is empty then there's nothing to do for (2). Most databases will let you do all three at once (as your migration is trying to do) with an empty table but I guess SQLite isn't smart enough for that. — mu is too short, Feb 05 '20 at 18:07

score 2 · Accepted Answer · answered Feb 05 '20 at 17:38

By default, a foreign_key is required (on app level, not on database).

To disable, in config/application.rb add

config.active_record.belongs_to_required_by_default = false

Or

class YourModel < ApplicationRecord
  belongs_to :another_model, optional: true
end

How to add reference column migration in Rails 6 with SQLite

1 Answers1

Linked