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

pez_dispenser picture pez_dispenser · May 19, 2009 · Viewed 9.3k times · Source
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

tvanfosson picture tvanfosson · May 19, 2009

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'