How to implement composite primary keys in rails

Question

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!

Answer 1

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.