Multiple Column Foreign Key Ruby on Rails

Question

I have the following models:

Company, Orders, Invoices

Company has many Orders and also has many Invoices. Orders have one Invoice and belong to a company. Invoices belong to both order and company. Consequently, Orders reference company_id and Invoices reference company_id. I want to ensure invoice company_id is the same as its parent order's company_id.

Note: Postgres supports this. How to use rails to implement? https://www.postgresql.org/docs/9.3/ddl-constraints.html#DDL-CONSTRAINTS-FK

1. Would I create a foreign key that ensures Invoice order_id + company_id exists in Orders as id + company_id?
2. What's the best way to achieve this?
3. Can this be done using migrations?

Answer 1

YAGNI

You're really overthinking and overcomplicating this. What you can do instead is get rid of the unessicary duplication caused by invoices.company_id and just setup an indirect assocation:

class Company < ApplicationRecord
  has_many :orders
  has_many :invoices, through: :orders
end

class Order < ApplicationRecord
  belongs_to :company
  has_many :invoices
end

class Invoice < ApplicationRecord
  belongs_to :order
  # joins the company through the orders.company_id column
  has_one :company, 
    through: :order
end

This avoids the whole issue altogether and all you need is the simple foreign keys created by the references/belongs_to migration macro.

While what you're proposing could perhaps be done via composite foreign key there is no actual advantage to this approach.

Can this be done using migrations?

The migrations DSL doesn't actually support it but you can always can run any arbitrary SQL. However the Ruby schema dumper will most likely not be able to reproduce it when parsing the schema so your foreign keys will be "lost in translation" when recreating the db from the schema unless you switch to SQL schema dumps.

  def up
    execute <<-SQL
      ALTER TABLE invoices
      ADD CONSTRAINT fk_invoices_orders_order_id_company_id
      FOREIGN KEY (order_id, company_id)
      REFERENCES orders(id, company_id)
      ON DELETE RESTRICT
    SQL
  end
  def down
    execute <<-SQL
      ALTER TABLE invoices
      DROP CONSTRAINT fk_invoices_orders_order_id_company_id
    SQL
  end