How to implement composite primary keys in rails

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.