6

I am using 'foriegner' gem in my application to set primary key, now i am in a situation to set composite primary key in a table.

I searched the web for this, but could not able to find a clear solution for this.

Please suggest me if its possible to set composite keys in rails application using foreigner gem or by any other means.

Note : I am using Postgres

thanks.

balanv
  • 10,686
  • 27
  • 91
  • 137
  • 1
    1. Are you sure you need them? 2. If yes, are you sure Rails is the right fit for your application? AFAIK: rails and composite keys are not friends at all it contradicts the Rails philosophy to use them. So you are going to fight the framework very often if you are going to use them. – Alexander Günther Jun 20 '12 at 07:33
  • 1
    Yes i am sure i need to use composite key. So composite keys should not be used in rails.. and if we do use it will it do some problem? – balanv Jun 20 '12 at 07:40
  • 1
    The problem ist that you will miss out a lot of 'Rails Magic' and do a lot of extrawork, like writing sql in migrations which ties it to a specific DB... Other frameworks my offer you better support for such special situations – Alexander Günther Jun 20 '12 at 07:46
  • 1
    Ok.. i should drop the idea of using Composite key..? – balanv Jun 20 '12 at 09:21
  • What you are going to do is your decision. I think you should get aware of the consequences which are coming with your decisions. Fore Instance if you choose to use Composite Keys on Book which is a ActiveRecord model your *_book_path helpers in rails are not going to work out of the box. Your migrations need more work... But if you are not going to use Composite Keys you might end up with bigger issues. I don't know your situation and it's on you to decide first if it's wise what you want to do before doing it. – Alexander Günther Jun 20 '12 at 09:54

2 Answers2

2

ActiveRecord

There is a gem called composite_primary_keys and integrates these features quite will for ActiveRecord.

It supports a wide array of ActiveRecord versions.

# Gemfile
gem 'composite_primary_keys', '=<version for your AR version>'

Use it like the following example:

class Membership < ActiveRecord::Base
  self.primary_keys = :user_id, :group_id
end

membership = Membership.find([1,1])  # composite ids returns single instance
# => <Membership:0x39218b0 @attributes={"user_id"=>"1", "group_id"=>"1"}>

I am happily using it in production, keeping my schema clean and tidy.

Better alternative: Sequel

As always, if you are looking for a great Ruby + PostgreSQL solution, look no further than jeremyevans/sequel.

It comes with a vast amount of features, including composite primary keys. Out of the box.

So if you are starting a new project (on PostgreSQL obviously), save yourself the headache of ActiveRecord and go with Sequel.

class Post < Sequel::Model
  set_primary_key [:category, :title]
end
Overbryd
  • 4,612
  • 2
  • 33
  • 33
1

Short answer: DON'T use composite PKs in Rails (unless you are forced to do so because of a legacy DB).

While there might be solutions with gems that work (currently) this is just not the way that ActiveRecord works. I'd also not use composite PKs outside of Rails unless there is a really good reason. They make a lot of things more complicated.

But: nothing prevents you from having what you think of a a composite primary key as an alternative key and have the Rails default PK (postgres: pseudotype 'serial' which draws its values from a sequence). Just make sure that you add a unique index for those columns that make up your key.

Pascal
  • 8,464
  • 1
  • 20
  • 31
  • There are many good reasons to use composite primary keys. This answer is just plain wrong, and comes out of a defensive ActiveRecord limited point of view. – Overbryd Feb 12 '17 at 09:20
  • That is what I said: in Rails. Unless there is a good reason. Please elaborate...in what cases would you use a composite key? – Pascal Feb 12 '17 at 20:54
  • Your question is probably best answered here http://stackoverflow.com/questions/26078535/composite-primary-keys-is-it-good-or-bad – Overbryd Feb 13 '17 at 09:33
  • @Overbryd thanks for the link! I still disagree though:-) I think the answer is valid for Rails. No matter what your opinion is on composite PKs in SQL in general. Support is suboptimal in Rails so better not use it. IMHO. – Pascal Feb 13 '17 at 10:28
  • ..and there's a lot of valid reasons. ie, working on an existing database crowded with composite PKs. So, simply resuming to stressy "don't use" is not the way... – Andre Figueiredo Jul 19 '17 at 21:22
  • @AndreFigueiredo i've updated my answer and excluded the "legacy DB" case from my answer. I stand by the "don't use composite PKs in Rails" answer for new Rails projects though. – Pascal Jul 20 '17 at 09:50
  • Fair enough, agree with you +1 – Andre Figueiredo Jul 20 '17 at 15:05