英文:
Multiple Column Foreign Key Ruby on Rails
问题
- 我应该创建一个外键，确保发票的order_id + company_id 在订单中存在作为 id + company_id 吗？
- 实现这个的最佳方式是什么？
- 是否可以使用迁移来完成？
英文:
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
- Would I create a foreign key that ensures Invoice order_id + company_id exists in Orders as id + company_id?
- What's the best way to achieve this?
- Can this be done using migrations?
答案1
得分: 2
YAGNI
你真的在想得太多，搞得太复杂了。你可以通过消除 invoices.company_id 导致的不必要的重复，而只是设置一个间接关联来解决问题：
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
# 通过 orders.company_id 列连接到公司
has_one :company,
through: :order
end
这样完全避免了整个问题，你只需要使用 references/belongs_to 迁移宏创建的简单外键。
虽然你提出的方法可能可以通过复合外键来实现，但这种方法实际上没有任何优势。
这是否可以通过迁移来实现？
迁移的DSL实际上不支持，但你总是可以运行任何任意的SQL。然而，Ruby模式转储器在解析模式时很可能无法复制它，所以在从模式重新创建数据库时，你的外键将会“失落在翻译中”，除非你切换到SQL模式转储。
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论