Rails migration with add_foreign_key: 'column "user_id" referenced in foreign key constraint does not exist'

Question

(Rails is version 5.0.0, Ruby 2.3.0p0)

I want to create an association between my Users table and Cards table. I've added belongs_to :user to the Cards model, and has_many :cards to the Users model, and created a migration with:

class AddUserIdToCard < ActiveRecord::Migration[5.0]
  def change
    add_foreign_key :cards, :users, column: :user_id
  end
end

When I run rake db:migrate, I receive the error:

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "user_id" referenced in foreign key constraint does not exist
: ALTER TABLE "cards" ADD CONSTRAINT "fk_rails_8ef7749967"
FOREIGN KEY ("user_id")
  REFERENCES "users" ("id")

Now I initially worked around this problem simply by adding add_column :cards, :user_id, :integer to the migration, but that doesn't really seem very tidy, and I'm worried about problems coming up later. Is there a better way to accomplish this?

Answer 1

You're setting a foreign key for cards table with the column user_id. But you haven't created a reference yet. Create a reference and then add foreign key to maintain referential integrity. Rollback and modify your migration with

1    class AddUserIdToCard < ActiveRecord::Migration[5.0]
2      def change
3        add_reference :cards, :users, index:true
4        add_foreign_key :cards, :users
5      end
6    end

Line 3 will create, in the cards table, a reference to id in the users table (by creating a user_id column in cards).

Line 4 will add a foreign key constraint to user_id at the database level.

For more, read Add a reference column migration in Rails 4

Answer 2

The answer provided is not accurate for Rails 5. Scroll to the add_reference bit of the docs for more, but in the case of the above question, you would use:

    class AddUserIdToCard < ActiveRecord::Migration[5.0]
      def change
        add_reference :cards, :users, foreign_key: true
      end
    end

Answer 3

In rails 6, I believe this is now

  def change
    add_column :cards, :user_id, :integer, index: true
    add_foreign_key :cards, :users
  end