如何在Rails中实现复合主键 [英] How to implement composite primary keys in rails

问题描述

我有一个 User 模型，如下所示：

 类User< $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$数据库中的 users 表具有两个外键 organization_id 和 department_id 。如何使这两列成为复合主键？到目前为止，我已经在网上看到了两种方法：
 
 
 
选项1 
 
 
 
使用 Composite_primary_keys  gem 
 
 
 
选项2 
 
 
 
向两个列中的每个列添加索引使用类似这样的东西：
  add_index：users，[：organization_id，：department_id]，唯一：真正的
  
 
 
 
我的问题
 
 
 
唯一标识行的最佳方法是什么在 users 表中，其中一行必须具有 department_id 和 organization_id 以便被唯一标识？为两个列建立索引与简单地使每个列成为表的主键之间有什么区别？ 
 
 
 
谢谢！
解决方案
添加索引。
 
 
 
在rails中，如果您具有 id 作为主键，则一切都会更好。
 
 
 
您有可能 破坏系统...但是最好不要除非您真的知道自己在做什么-如果您询问索引和键之间的区别是什么，然后您就不会...这很酷，顺便说一句，您不需要知道如何与Rails一起工作就可以了...但是，如果您确实需要将会改变一些根本的东西。因为使用 id 以外的任何东西作为主键比较困难。事情破裂了。您将不得不修复它，而无需了解为什么它们会损坏以及为什么首先需要它们... 
 
 
 
拥有 id 作为主键，也具有一个约束，可确保您拥有唯一的 organization_id  +  department_id  ... 
 
 
 
注意：我已经假设您不知道索引之间的区别/ primary-key-这个假设可能不成立，但只是您提出问题的方式的假象... ：） 
在这种情况下...区别在于，如果您只是使用Rails期望的功能，Rails会为您提供各种出色的魔术...如果您不使用它，则是PITA。
 
 
 
否则... 
a主键是唯一标识信息的位。您可能会认为org_id / dept_id永远不会改变...但是您会感到惊讶的是，现实世界中数据在现实生活中的变化频率...以及在更新整个db关系价值时可能会感到多么痛苦确实... 
 
 
 
唯一索引（OTOH）可以按照您想要的方式很好地约束数据...如果有人决定部门，就不必麻烦更新数据现在42必须是部门23。 
另外，索引使您可以按列对查找数据，比对整个数据库进行行扫描要快得多。
 
I have a User model that looks like this:
class User < ApplicationRecord
  belongs_to :organization
  belongs_to :department
end
The users table in the database has the two foreign keys organization_id and department_id. How can I make these two columns a composite primary key? I've seen two approaches online so far:

Option 1

Use the composite_primary_keys gem

Option 2

Add an index to each of the two columns using something like this:
add_index :users, [:organization_id, :department_id], unique: true


My question

What is the best way to uniquely identify a row in the users table where a row must have a department_id and an organization_id in order to be uniquely identified? What's the difference between indexing both columns and simply making each column a primary key of the table? 

Thanks!
解决方案
Add an index.

In rails, everything works better if you have id for the primary key.

It is possible for you to buck the system... but it's better not to unless you really know what you're doing - and if you're asking what the difference is between an index and a key then you don't... which is cool BTW, you don't need to know that in order to do well with Rails... but it really helps if you're going to be changing something fundamental. Because using anything other than id as a primary key is harder. Things break more. You will have to fix it without understanding why they're breaking and why you'd have needed them in the first place...

There is nothing wrong with having id as a primary key and also having a constraint that makes sure you have a unique organisation_id+department_id...

Note: I have made the assumption you don't know the difference between an index/primary-key - this assumption might not hold, but be an artifact of the way you asked the question... if so my apologies. :)
In that case... the difference is that Rails does all kinds of nice magic for you if you just use what it expects... and is a PITA if you don't.

Otherwise...
a primary-key is a uniquely-identifying bit of information. You might think that org_id/dept_id never changes... but you'd be surprised how often real-world data changes in real life...  and how much of a pain it can be to update your entire db's worth of relations when it does...

A unique index (OTOH) nicely constrains the data in the way you want... without having that hassle of having to update stuff if somebody decides department 42 must be department 23 now.
Additionally indexes let you look up data by that column-pair much quicker than doing a row-scan of the entire db would.

                        
这篇关于如何在Rails中实现复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！