Dr Nic

What is a composite key in Rails?

First, what is an ActiveRecord primary key currently? There are two versions of a primary key: an integer (12) and a string (’12′). That is, Person.find(1) and Person.find(’1′) return the same thing.

This is the theme of the composite key solution – any conceivable notion of 2+ numbers should work as well as any other. So, [12,2] should work, as should ’12,2′, as should ['12','2']. That is, Membership.find(12,2), Membership.find(’12,2′), Membership.find([12, 2]), Membership.find(’12′,’2′), etc, are treated the same.

Normally, if you want to find multiple objects by their primary key, you pass a list, so Person.find(1,2,3) and Person.find(’1′,’2′,’3′) give the same result.

For composite keys, there are many variations that you can use interchangably: Membership.find([12,1],[12,2],[12,3]) is the same as Membership.find(’12,1′,’12,2′,’12,3′) is the same as Membership.find(’12,1;12,2;12,3′).

The id of a normal object is the value of its primary key field. So Person.find(1).id == 1, and Person.find(’1′).id == 1.

To support interchangable use of the id value within your applications, the composite key result is a CompositeIds object, a subclass of Array where to_s method joins the keys by a comma. Want to pass the id of a composite object to your HTML and back to the server in URLs? You won’t need to do anything differently than you do now. The to_param method will return a string joined by commas – e.g. ’12,1′ – which will be happily accepted by your find method when you receive the value in your controller action methods.

The main code change that you will need to consider is that the primary_key method returns a list of column names, instead of a single column name. (Actually it returns a CompositeKeys instance which subclasses Array, and its to_s method joins the values by a comma).

If you find that Composite Primary Keys isn’t working in a sexily smooth way, let me know so the interface can be improved.

Related posts:

  1. Instant new Rails applications with the App Scrolls When I start a new project I want to start...
  2. Using CoffeeScript in Rails and even on Heroku I’m pretty excited about CoffeeScript as a clean-syntax replacement for...
  3. First look at rails 3.0.pre This article is out of date in some aspects....
  4. Rails themes can remember things I was getting annoyed at having to remember all the...
  5. Install any HTML theme/template into your Rails app Have you ever even bothered to Google for “rails...

6 Responses to “What is a composite key in Rails?”

  1. Dr Nic says:

    One thing I didn’t state explicitly:

    @membership = Membership.find(12,1)
    @membership.id
    => [12,1]
    @membership.id.class
    => CompositeIds
    @membership.id.to_s
    => “12,1″
    @membership.to_param
    => “12,1″

  2. IQpierce says:

    Hi Dr. Nic!!!

    First, thank you SO MUCH for developing this. I’m experienced with DB design and inexperienced in both Ruby and Rails, and I was amazed that Rails developers actually believe that composite primary keys are unnecessary, or even undesirable. Thank you so much for supporting this essential feature – it’s shameful that Rails didn’t have it already.

    Now, I believe that I’ve found either a bug, or a place where I’m doing something drastically wrong… I suspect the former.

    I’m using mysql Ver 14.12. I’m using version 0.3.3 of your composite_primary_keys library. Everything seems to work well with it so far, but I’m running into this.

    I have a table “changelists”; in changelist.rb, I call:
    set_primary_keys :cl_num, :port_id
    has_many :cl_files, :foreign_key => [:cl_num, :port_id]

    (As an aside: I find that whenever I try to use “:foreign_keys” instead of “:foreign_key”, I get an error. Is this a bug or my mistake?)

    Then I have the table cl_files, with cl_file.rb:
    set_primary_keys :cl_num, :port_id, :real_path
    belongs_to :changelist, :foreign_key => [:cl_num, :port_id]

    So far this seems to work fine. Maybe I’m doing something wrong that’s obvious to you and not me. (Both the “changelists” and the “cl_files” tables have both the “cl_num” and “port_id” columns.)

    However, when I go into views\changelists\show.rhtml (for a changelist with port_id of 1 and cl_num of 29692) and try to do the following…

    …then I get the following error…

    Mysql::Error: Operand should contain 1 column(s): SELECT * FROM cl_files WHERE (cl_files.cl_num,port_id = 29692,1)

    I don’t think I’ve ever seen this type of structuring of an SQL query before, at least not in MySQL. (I’m referring to the contents of the “where” clause.) My guess is that this is valid syntax on some other database solution, but not in MySQL. (Or else I’m doing something wrong. :)

    My workaround is this: in changelists_controller.rb, in the “def show” portion, I initialize the array of @cl_files myself:

    @cl_files = ClFile.find_all_by_port_id_and_cl_num(params[:port_id],params[:cl_num])

    And then I use @cl_files instead of @changelist.cl_files. This apparently results in a call with no problems, and I get the correct array. Still, it would be nice to be able to access that array simply as @changelist.cl_files!

    Thanks again for developing this, I’ll be following your progress. If there’s a better place for me to report bugs than here, let me know. :)

  3. Dr Nic says:

    Composite foreign keys have been recently added in v0.6.2

    Get these by:

    gem install composite_primary_keys

    Let me know if you have any problems with the new release.

  4. IQpierce says:

    Wow, not only did you reply with lightning speed, but I’m embarassed to say that I found and installed 0.6.2 even before I saw your reply! I came back here to say “never mind”, and you already had the right answer. :)

    My show.rhtml page works fine now! Thanks Dr. Nic! :)

  5. Dr Nic says:

    BTW – I agree that Rails “should” have composite key support, but no one til now has had the desire to write the code. I think it will be better to keep the code outside of Rails until its perfect else changes will get stuck with the core team who – as we know – don’t use CPKs.