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?

Summertree

Please mark the answer when you have a minute, since it is correct – Pitiful

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

Click

Okay... I got a no method error for add_references, but I tried add_reference (singular) instead and that error went away. However, I still got what looks like basically the same error: StandardError: An error has occurred, this and all later migrations canceled: 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") – Summertree

I am sorry. the method name is add_reference and not add_references. I'll update it. – Click

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

Offset

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

Sita

Answer 4

class AddUserIdToCard < ActiveRecord::Migration[5.2]
  def change
    add_foreign_key :cards, :users, column: :user_id, primary_key: :"id", on_delete: :cascade
  end
end

Try this migration. I was facing the same problem then I fixed it.

Inkblot