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!

Threatt

First of all, Why do you want to use a composite primary key instead of following Rails conventions and just have an id columns and belongs_to associations to the organization and the department? Composite primary keys have implications to many other parts of the app: Rails link helper might not work with composite keys, finder work differently, foreign keys are harder to use... – Groin

Option 2 is the best call here. Option 1 is what you do if you have no other choice, like you're saddled with a legacy database you can't change. – Antiphon

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.

Pained

Okay, yeah I'm just leaning about database stuff..but I know rails can get nasty when you deviate from its conventions. I'll go with the index approach then, thanks! – Threatt

Plus you'll probally figure out that reality dictates that the relationship should be m2m pretty soon anyways.... – Vanessa

Great answer - I am actually deciding on this one thing for a project that i am working on. Not sure if there are any drawbacks but will take that route for now. Thanks. – Gravante

I wonder what a department number has to do with ids... – Whopping

What about list partitioned tables? it`s not possible to do everything by ID. – Complementary

I never claimed everything was possible to do with ids. I claimed that rails works easier if you have them, and that you should depart from that only if you know what you're doing. – Pained