CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4rails-migrations
Nick
Nick

Reputation: 3090

Rails: PG::UndefinedTable: ERROR: relation "..." does not exist

On migration I get the following error message:

PG::UndefinedTable: ERROR:  relation "actioncodes" does not exist
: ALTER TABLE "organizations" ADD CONSTRAINT "fk_rails_4ecaa2493e"
FOREIGN KEY ("actioncode_id")
  REFERENCES "actioncodes" ("id")

I have the following migration file for Organizations:

class CreateOrganizations < ActiveRecord::Migration
  def change
    create_table :organizations do |t|
      t.string     :name,         null: false,    limit: 40
      t.references :actioncode,   index: true,    foreign_key: true
      t.boolean    :activated
      t.datetime   :activated_at

      t.timestamps null: false
    end
  end
end

And for Actioncodes I have the migration file:

class CreateActioncodes < ActiveRecord::Migration
  def change
    create_table :actioncodes do |t|
      t.string  :code,          null: false,  limit: 20
      t.string  :description,                 limit: 255

      t.timestamps null: false
    end
  end
end
class AddIndexToActioncodesCode < ActiveRecord::Migration
  def change
    add_index :actioncodes, :code,  unique: true
  end
end

The organization model file includes: belongs_to :actioncode.

While the actioncodes model file includes: has_many :organizations.

Any idea what could be causing the error message?

If I remove index: true, foreign_key: true from the migration file, it migrates without errors. And when I replace that line with the incorrect line t.references :actioncode_id, index: true, foreign_key: true, it gives the error below, where the last line ("ids") suggests Rails somehow seems to have problem with the name of the table?

PG::UndefinedTable: ERROR:  relation "actioncode_ids" does not exist
: ALTER TABLE "organizations" ADD CONSTRAINT "fk_rails_604f95d1a1"
FOREIGN KEY ("actioncode_id_id")
  REFERENCES "actioncode_ids" ("id")

Upvotes: 11

Views: 27974

Answers (4)

Promise Preston
Promise Preston

Reputation: 28920

I had this same challenge when working on a Rails 6 application in Ubuntu 20.04.

I am working on a College Portal where I have model called School and another called Program. The model Program belongs to the model School, so it is required that I add a school references column to the programs table.

I used this command this create the migration file:

rails generate model School name:string logo:json motto:text address:text

rails generate model Program name:string logo:json motto:text school_id:references

However, when I run the command: rails db:migrate

I was getting the error:

== 20201208043945 CreateSchools: migrating ====================================
-- create_table(:schools)
   -> 0.0140s
== 20201208043945 CreateSchools: migrated (0.0141s) ===========================

== 20201208043958 CreatePrograms: migrating ===================================
-- create_table(:programs)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR:  relation "school_ids" does not exist

Here's how I solved it:

The issue was from the command:

rails generate model Program name:string logo:json motto:text school_id:references

It is supposed to be school:references and not school_id:references, so during the migration Rails is unable to find the table school_ids.

So I modified the command to:

rails generate model Program name:string logo:json motto:text school:references

And it worked fine.

That's all.

I hope this helps

Upvotes: 0

Ilene
Ilene

Reputation: 484

I was also getting this error. If you are using a test database to run rspec make sure you run rake db:test:prepare in the terminal before running rspec.

Upvotes: 6

mu is too short
mu is too short

Reputation: 434665

The foreign_key: true in this line:

t.references :actioncode,   index: true,    foreign_key: true

tells Rails to create a foreign key inside the database. A foreign key:

constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.

So it is some logic inside the database (where it belongs) that ensures you can't put invalid values in your actioncode column and that you can't remove entries from the actioncodes table that are being used elsewhere.

In order to create the constraint, the referenced table (actioncodes) needs to exist before you refer to it. Looks like your migrations are trying to create organizations before actioncodes so all you need to do is rename the CreateOrganizations migration file so that its timestamp prefix comes after the one for CreateActioncodes. The prefix is just a timestamp in the format YYYYMMDDhhmmss so change the CreateOrganizations timestamp to the CreateActioncodes timestamp with one more second.

Upvotes: 10

Prakash Murthy
Prakash Murthy

Reputation: 13067

So the issue is happening because CreateOrganizations migration is being run before CreateActioncodes is executed.

CreateActioncodes is to be run first thereby ensuring that the action codes table exists.

The order in which migrations are run is based on the time stamp of the migration - as indicated in the name of the file. 20141014183645_create_users.rb will run before 20141014205756_add_index_to_users_email.rb as the timestamp of the second one - 20141014205756 is after that of the first one - 20141014183645.

Make sure the time-stamps of the CreateOrganizations migration is after that of CreateActioncodes migration.

Either you could manually change the timestamp in the file names. Or delete these migration files, and create them in the correct order.

Upvotes: 21

Related Questions