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.

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'

Answer 2

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 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.

Answer 4

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]