CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
LightBlue
LightBlue

Reputation: 167

First db migration on Heroku: undefined table/relation doesn't exist for foreign key

Error message: PG::UndefinedTable: ERROR: relation "resolvers" does not exist

Here's the migration that is spitting out the error:

class AddResolverRefToBugs < ActiveRecord::Migration[5.0]
  def change
    add_reference :bugs, :resolver, foreign_key: true
  end
end

Now, I get that there is no Resolver table, since the actual table is the User table; resolver_id is my foreign_key column. So I just have a resolver_id field for one of my models that references the Users model, and I've not had any issues with my app in the dev environment.

class Bug < ApplicationRecord
  # Associations
  belongs_to :user
  belongs_to :resolver, class_name: 'User', optional: true

Am I supposed to specify something, somewhere such that PG would know :resolver (the resolver_id column) is in fact the Users table?

My error message contains the line: : ALTER TABLE "bugs" ADD CONSTRAINT "fk_rails_2d3f1765bb" FOREIGN KEY ("resolver_id") REFERENCES "resolvers" ("id")

So it does look like it's referencing the wrong thing.

In one relevant StackOverflow question, I noticed that he/she has "nested" references...

create_table :english_grades do |t|
  t.references :teacher, references: :users
  t.references :student, references: :users

...where users is specified. I'm not sure if that is relevant here.

Presumably my schema my migration might have to look like:

t.references :resolver, references: users

The docs for add_references don't mention anything about this.

That person also has foreign keys specified in the migration, where I do see a link that goes from the foreign key field to the actual table. Presumably I'd need something like this:

add_foreign_key :bugs, :users, column: :resolver_id

But based on what I read online, it's not necessary to use add_foreign_key.

I would appreciate any explanation here.

Note: Some have suggested running db:reset first, but that doesn't work for me, as I get the error this person is getting.

Upvotes: 1

Views: 261

Answers (1)

LightBlue
LightBlue

Reputation: 167

I am still unsure as to why Postgres throws an error here while Sqlite does not. However, one way to properly reference the resolver column to the Users table was using foreign_key: {to_table: :users}:

add_reference :bugs, :resolver, foreign_key: {to_table: :users}

After getting rid of the original add_reference migration. PG did not complain while going through the migrations with this line.

(Rails 5)

Upvotes: 1

Related Questions