title: Rails 5 - How to migrate foreign keys to PostgreSQL?
body: <blockquote>
    <p>A travel expense must have information on which cost center paid for
    the ticket, and which cost center paid for the traveling costs.</p>
  </blockquote>
  
  <p>I've ran the following migration, attempting to create two foreign keys to map to <code>cost_centers</code>, which worked fine for <strong>SQLite</strong>:</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>this, however, doesn't work for <strong>PostgreSQL</strong>:</p>
  
  <pre><code>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")
  )
  </code></pre>
  
  <p>It attempts to set <strong>FOREIGN KEY</strong> ("<strong>air_ticket_cost_center_id</strong>") as <strong>REFERENCE</strong> of "<strong>air_ticket_cost_centers</strong>" ("id"), which should have been a reference to <strong>cost_centers</strong> instead.</p>
  
  <p>TLDR: On PostgreSQL, in order to reference the same existing table twice on another table without conflicts, how can I set my migration?</p>
  
  <p>Thanks in advance!</p>
  
  <p><strong>UPDATE</strong> - Including Full Stack Trace</p>
  
  <pre><code>== 20170109120056 AddCostCentersToExpenseTravels: migrating ===================
  -- add_column(:expense_travels, :air_ticket_cost_center_id, :integer)
     -&gt; 0.0014s
  -- add_column(:expense_travels, :travel_cost_center_id, :integer)
     -&gt; 0.0007s
  -- add_foreign_key(:expense_travels, :cost_centers, {:column=&gt;:air_ticket_cost_center_id})
     -&gt; 0.0000s
  -- add_foreign_key(:expense_travels, :cost_centers, {:column=&gt;:travel_cost_center_id})
     -&gt; 0.0000s
  -- add_index(:expense_travels, :air_ticket_cost_center_id)
     -&gt; 0.0030s
  -- add_index(:expense_travels, :travel_cost_center_id)
     -&gt; 0.0033s
  == 20170109120056 AddCostCentersToExpenseTravels: migrated (0.0091s) ==========
  
    db:schema:dump --&gt;   0.250000   0.010000   0.260000 (  0.267062)
    db:_dump --&gt;   0.250000   0.010000   0.260000 (  0.267429)
    db:migrate --&gt;   0.710000   0.070000   0.780000 (  1.101759)
    db:abort_if_pending_migrations --&gt;   0.040000   0.010000   0.050000 (  0.040145)
    db:seed --&gt;   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 &lt;top (required)&gt;'
  /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 `&lt;top (required)&gt;'
  /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 `&lt;main&gt;'
  </code></pre>