Rails 5 - How to migrate foreign keys to PostgreSQL?

Question

A travel expense must have information on which cost center paid for the ticket, and which cost center paid for the traveling costs.

I've ran the following migration, attempting to create two foreign keys to map to cost_centers, which worked fine for SQLite:

  def change
    add_reference :expense_travels, :air_ticket_cost_center, index: true
    add_reference :expense_travels, :travel_cost_center, index: true

    add_foreign_key :expense_travels, :cost_centers, column: :air_ticket_cost_center_id
    add_foreign_key :expense_travels, :cost_centers, column: :travel_cost_center_id
  end

this, however, doesn't work for PostgreSQL:

rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
PG::UndefinedTable: ERROR: relation "air_ticket_cost_centers" does not exist
: CREATE TABLE "expense_travels" ("id" serial primary key, "expense_id" integer, "person_id" integer, "manager" character varying, "travel_type_id" integer, "air_ticket_amount" decimal(15,2), "air_ticket_cost_center_id" integer, "travel_amount" decimal(15,2), "travel_cost_center_id" integer, "total_amount" decimal(15,2), "start_date" date, "end_date" date, "itinerary" text, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_51368cd47f"
FOREIGN KEY ("expense_id")
REFERENCES "expenses" ("id")
, CONSTRAINT "fk_rails_4c8cafa3f7"
FOREIGN KEY ("person_id")
REFERENCES "people" ("id")
, CONSTRAINT "fk_rails_41906a97fa"
FOREIGN KEY ("travel_type_id")
REFERENCES "travel_types" ("id")
, CONSTRAINT "fk_rails_c4144cce64"
FOREIGN KEY ("air_ticket_cost_center_id")
REFERENCES "air_ticket_cost_centers" ("id")
, CONSTRAINT "fk_rails_8d209073b1"
FOREIGN KEY ("travel_cost_center_id")
REFERENCES "travel_cost_centers" ("id")
)

It attempts to set FOREIGN KEY ("air_ticket_cost_center_id") as REFERENCE of "air_ticket_cost_centers" ("id"), which should have been a reference to cost_centers instead.

TLDR: On PostgreSQL, in order to reference the same existing table twice on another table without conflicts, how can I set my migration?

Thanks in advance!

UPDATE - Including Full Stack Trace

== 20170109120056 AddCostCentersToExpenseTravels: migrating ===================
-- add_column(:expense_travels, :air_ticket_cost_center_id, :integer)
   -> 0.0014s
-- add_column(:expense_travels, :travel_cost_center_id, :integer)
   -> 0.0007s
-- add_foreign_key(:expense_travels, :cost_centers, {:column=>:air_ticket_cost_center_id})
   -> 0.0000s
-- add_foreign_key(:expense_travels, :cost_centers, {:column=>:travel_cost_center_id})
   -> 0.0000s
-- add_index(:expense_travels, :air_ticket_cost_center_id)
   -> 0.0030s
-- add_index(:expense_travels, :travel_cost_center_id)
   -> 0.0033s
== 20170109120056 AddCostCentersToExpenseTravels: migrated (0.0091s) ==========

  db:schema:dump -->   0.250000   0.010000   0.260000 (  0.267062)
  db:_dump -->   0.250000   0.010000   0.260000 (  0.267429)
  db:migrate -->   0.710000   0.070000   0.780000 (  1.101759)
  db:abort_if_pending_migrations -->   0.040000   0.010000   0.050000 (  0.040145)
  db:seed -->   0.170000   0.020000   0.190000 (  0.215987)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR:  relation "air_ticket_cost_centers" does not exist
: CREATE TABLE "expense_travels" ("id" serial primary key, "expense_id" integer, "person_id" integer, "manager" character varying, "travel_type_id" integer, "air_ticket_amount" decimal(15,2), "air_ticket_cost_center_id" integer, "travel_amount" decimal(15,2), "travel_cost_center_id" integer, "total_amount" decimal(15,2), "start_date" date, "end_date" date, "itinerary" text, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_51368cd47f"
FOREIGN KEY ("expense_id")
  REFERENCES "expenses" ("id")
, CONSTRAINT "fk_rails_4c8cafa3f7"
FOREIGN KEY ("person_id")
  REFERENCES "people" ("id")
, CONSTRAINT "fk_rails_41906a97fa"
FOREIGN KEY ("travel_type_id")
  REFERENCES "travel_types" ("id")
, CONSTRAINT "fk_rails_c4144cce64"
FOREIGN KEY ("air_ticket_cost_center_id")
  REFERENCES "air_ticket_cost_centers" ("id")
, CONSTRAINT "fk_rails_8d209073b1"
FOREIGN KEY ("travel_cost_center_id")
  REFERENCES "travel_cost_centers" ("id")
)
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/usr/local/rvm/gems/ruby-2.3.0/gems/activesupport-5.0.0.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:283:in `create_table'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:845:in `block in method_missing'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `block in say_with_time'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `say_with_time'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:834:in `method_missing'
/home/yadayada/workspace/yadayada/sources/db/migrate/20161201174226_create_expense_travels.rb:3:in `change'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:788:in `exec_migration'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:772:in `block (2 levels) in migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:771:in `block in migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:770:in `migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:950:in `migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1211:in `block in execute_migration_in_transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `block in ddl_transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/transactions.rb:211:in `transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `ddl_transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1210:in `execute_migration_in_transaction'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1183:in `block in migrate_without_lock'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `each'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `migrate_without_lock'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `block in migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1298:in `with_advisory_lock'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1005:in `up'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:983:in `migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/usr/local/rvm/gems/ruby-2.3.0/gems/activerecord-5.0.0.1/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
/usr/local/rvm/gems/ruby-2.3.0/gems/rake-benchmark-1.0.0/lib/rake/benchmark.rb:8:in `block in execute_with_benchmark'
/usr/local/rvm/gems/ruby-2.3.0/gems/rake-benchmark-1.0.0/lib/rake/benchmark.rb:7:in `execute_with_benchmark'
/usr/local/rvm/gems/ruby-2.3.0/gems/rake-11.3.0/exe/rake:27:in `<top (required)>'
/usr/local/rvm/gems/ruby-2.3.0/bin/ruby_executable_hooks:15:in `eval'
/usr/local/rvm/gems/ruby-2.3.0/bin/ruby_executable_hooks:15:in `<main>'

Answer 1

The sentence add_reference :expense_travels, :air_ticket_cost_center will try to find a table named expense_travels and another one named air_ticket_cost_center and then create a reference between them. That's why you get relation "air_ticket_cost_centers" does not exist

Let's try this:

add_reference :expense_travels, :air_ticket_cost_center, table: :cost_centers, index: true
add_reference :expense_travels, :travel_cost_center, table: :cost_centers, index: true

add_foreign_key :expense_travels, :cost_centers, column: :air_ticket_cost_center_id
add_foreign_key :expense_travels, :cost_centers, column: :travel_cost_center_id

If that still doesn't work, you can't go wrong by splitting up the steps:

add_column :expense_travels, :air_ticket_cost_center_id, :integer
add_column :expense_travels, :travel_cost_center_id, :integer

add_foreign_key :expense_travels, :cost_centers, column: :air_ticket_cost_center_id
add_foreign_key :expense_travels, :cost_centers, column: :travel_cost_center_id

add_index :expense_travels, :air_ticket_cost_center_id
add_index :expense_travels, :travel_cost_center_id