In a join table, what's the best workaround fo

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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'

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]