In a join table, what's the best workaround for Rails' absence of a composite key?

Question

create_table :categories_posts, :id => false do |t|
  t.column :category_id, :integer, :null => false
  t.column :post_id, :integer, :null => false
end

I have a join table (as above) with columns that refer to a corresponding categories table and a posts table. I wanted to enforce a unique constraint on the composite key category_id, post_id in the categories_posts join table. But Rails does not support this (I believe).

To avoid the potential for duplicate rows in my data having the same combination of category_id and post_id, what's the best workaround for the absence of a composite key in Rails?

My assumptions here are:

1. The default auto-number column (id:integer) would do nothing to protect my data in this situation.
2. ActiveScaffold may provide a solution but I'm not sure if it's overkill to include it in my project simply for this single feature, especially if there is a more elegant answer.

Coda

Answer 1

Add a unique index that includes both columns. That will prevent you from inserting a record that contains a duplicate category_id/post_id pair.

add_index :categories_posts, [ :category_id, :post_id ], :unique => true, :name => 'by_category_and_post'

Extrados

Thanks. From reading various blog posts I thought a composite index was not possible and that this syntax did not exist. – Coda

This will give a poor UI experience if the user tries to enter a duplicate rec. – Marinna

@Larry - Couldn't I still use the validation logic from your answer and combine it with this Rails syntax for composite indexes? – Coda

Yes, that's what my original answer was saying...you should do both. Note that the index is added in the migration. "add_index" is only called in migrations, not in models. – Marinna

@Larry - Yes, I've been looking for a solution for a migration which is why this answer works perfectly. But I will still add your validation to my model. Thanks again. – Coda

Would just like to add that I had trouble getting the constraint to fire when adding duplicates because I forgot the name. Make sure you add it! – Fabrianna

Answer 2

It's very hard to recommend the "right" approach.

1) The pragmatic approach

Use validator and do not add unique composite index. This gives you nice messages in the UI and it just works.

class CategoryPost < ActiveRecord::Base
  belongs_to :category
  belongs_to :post

  validates_uniqueness_of :category_id, :scope => :post_id, :message => "can only have one post assigned"
end

You can also add two separate indexes in your join tables to speed up searches:

add_index :categories_posts, :category_id
add_index :categories_posts, :post_id

Please note (according to the book Rails 3 Way) the validation is not foolproof because of a potential race condition between the SELECT and INSERT/UPDATE queries. It is recommended to use unique constraint if you must be absolutely sure there are no duplicate records.

2) The bulletproof approach

In this approach we want to put a constraint on the database level. So it means to create a composite index:

add_index :categories_posts, [ :category_id, :post_id ], :unique => true, :name => 'by_category_and_post'

Big advantage is a great database integrity, disadvantage is not much useful error reporting to the user. Please note in creating of composite index, order of columns is important.

If you put less selective columns as leading columns in index and put most selective columns at the end, other queries which have condition on non-leading index columns may also take advantage of INDEX SKIP SCAN. You may need to add one more index to get advantage of them, but this is highly database dependant.

3) Combination of both

One can read about combination of both, but I tend to like the number one only.

Clava

This should be considered the best abswer, as it proposes the model and data integrety. – Succussion

I disagree with the recommendation to use only number one, but as all the pros and cons are mentioned, this still deserves an upvote. – Cudgel

Answer 3

I think you can find easier to validate uniqueness of one of the fields with the other as a scope:

FROM THE API:

validates_uniqueness_of(*attr_names)

Validates whether the value of the specified attributes are unique across the system. Useful for making sure that only one user can be named "davidhh".

  class Person < ActiveRecord::Base
    validates_uniqueness_of :user_name, :scope => :account_id
  end

It can also validate whether the value of the specified attributes are unique based on multiple scope parameters. For example, making sure that a teacher can only be on the schedule once per semester for a particular class.

  class TeacherSchedule < ActiveRecord::Base
    validates_uniqueness_of :teacher_id, :scope => [:semester_id, :class_id]
  end

When the record is created, a check is performed to make sure that no record exists in the database with the given value for the specified attribute (that maps to a column). When the record is updated, the same check is made but disregarding the record itself.

Configuration options:

* message - Specifies a custom error message (default is: "has already been taken")
* scope - One or more columns by which to limit the scope of the uniquness constraint.
* case_sensitive - Looks for an exact match. Ignored by non-text columns (true by default).
* allow_nil - If set to true, skips this validation if the attribute is null (default is: false)
* if - Specifies a method, proc or string to call to determine if the validation should occur (e.g. :if => :allow_validation, or :if => Proc.new { |user| user.signup_step > 2 }). The method, proc or string should return or evaluate to a true or false value.

Vocalise

as @izap said in his answer: the validation is not foolproof because of a potential race condition between the SELECT and INSERT/UPDATE queries. – Number

Answer 4

I implement both of the following when I have this issue in rails:

1) You should have a unique composite index declared at the database level to ensure that the dbms won't let a duplicate record get created.

2) To provide smoother error msgs than just the above, add a validation to the Rails model:

validates_each :category_id, :on => :create do |record, attr, value|
  c = value; p = record.post_id
  if c && p && # If no values, then that problem 
               # will be caught by another validator
    CategoryPost.find_by_category_id_and_post_id(c, p)
    record.errors.add :base, 'This post already has this category'
  end
end

Marinna

According to tvanoffson's answer, a composite index is indeed possible and he has provided the syntax for it. In that case, your suggestion to declare the composite index at the database level (while a good suggestion) would be unnecessary. My assumption was that composite indexes were not possible in Rails but perhaps I was incorrect. – Coda

Tvanfosson's solution is the same as my #1 -- the index is being declared at the db level, not at the Rails or Ruby level. Rails will only be able to report a "SQL error" when a dup is submitted. That's why you want to also validate in the model (at the Rails level). – Marinna

When you said "at the database level" I thought you meant to create it in the database without using the add_index method in Rails. The reason for my question was I didn't know composite indexes were possible. But I don't see why I can't add your validation logic to the syntax from tvanoffson's answer. Sorry if that's what you intended. But I didn't understand that from your answer. – Coda

Answer 5

A solution can be to add both the index and validation in the model.

So in the migration you have: add_index :categories_posts, [:category_id, :post_id], :unique => true

And in the model: validates_uniqueness_of :category_id, :scope => [:category_id, :post_id] validates_uniqueness_of :post_id, :scope => [:category_id, :post_id]

Macguiness