Adding uniqueness constraint on MySQL database with scope possible?

Question

I've got a Rails app that uses MySQL.

I have a has_many :through association between two models as described below:

class Category < ActiveRecord::Base
  has_many :category_pairings
  has_many :dishes, through: :category_pairings, :inverse_of => :categories
end

class Dish < ActiveRecord::Base
  has_many :category_pairings
  has_many :categories, through: :category_pairings, :inverse_of => :dishes
end

class CategoryPairing < ActiveRecord::Base
  belongs_to :dish
  belongs_to :category
end

So in my category_pairings table I have entries like this:

+---------+-------------+
| dish_id | category_id |
+---------+-------------+
|       3 |           5 |
|       3 |           1 |
|       2 |           1 |
+---------+-------------+

I want to ensure that there is no way you can make another entry like this:

+---------+-------------+
| dish_id | category_id |
+---------+-------------+
|       3 |           5 |
|       3 |           1 |
|       2 |           1 |
|       2 |           1 | <-- Illegal
+---------+-------------+

I know there is a way to do this through Rails, but is there a way to prevent this through MySQL?

I know about using in MySQL:

ALTER TABLE category_pairings
ADD UNIQUE (category_id);

But that'll make it so that you can only have one unique category_id throughout the table.

And if its only possible to do this through Rails, what would my new migration look like in order to do this?

This is what my original migration looked like to create the category_pairings table:

class CreateCategoryPairings < ActiveRecord::Migration
  def change
    create_table :category_pairings do |t|
      t.belongs_to :dish
      t.belongs_to :category

      t.timestamps
    end

    add_index :category_pairings, :dish_id
    add_index :category_pairings, :category_id
  end
end

http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql — deefour, Aug 16 '12 at 17:47

score 2 · Accepted Answer · answered Aug 16 '12 at 18:10

2

You need to add a unique index across both fields like this:

ALTER TABLE category_pairings
ADD UNIQUE (dish_id, category_id);

answered Aug 16 '12 at 18:10

Mike Brant

70,514
10
99
103

Thanks! That did the trick. I also found that you just need to create another migration and you can repeat the `add_index` but this time you can add the `:uniqueness => true`. I was afraid that doing another `add_index` would create a conflict since I did that already in my original migration to create the JOIN TABLE. – FilmiHero Aug 16 '12 at 18:18

Adding uniqueness constraint on MySQL database with scope possible?

1 Answers1