HABTM - uniqueness constraint

Question

I have two models with a HABTM relationship - User and Role.

• user - has_and_belongs_to_many :roles
• role - belongs_to :user

I want to add a uniqueness constraint in the join (users_roles table) that says the user_id and role_id must be unique. In Rails, would look like:

validates_uniqueness_of :user, :scope => [:role]

Of course, in Rails, we don't usually have a model to represent the join relationship in a HABTM association.

So my question is where is the best place to add the constraint?

Answer 1

You can add uniqueness to join table

add_index :users_roles, [ :user_id, :role_id ], :unique => true, :name => 'by_user_and_role'

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

Your database will raise an exception then, which you have to handle.
I don't know any ready to use rails validation for this case, but you can add your own validation like this:

class User < ActiveRecord::Base
has_and_belongs_to_many :roles, :before_add => :validates_role

I would just silently drop the database call and report success.

def validates_role(role)
  raise ActiveRecord::Rollback if self.roles.include? role
end

ActiveRecord::Rollback is internally captured but not reraised.

Edit

Don't use the part where I'm adding custom validation. It kinda works but there is better alternatives.

Use :uniq option on association as @Spyros suggested in another answer:

class Parts < ActiveRecord::Base
  has_and_belongs_to_many :assemblies, :uniq => true, :read_only => true
end  

(this code snippet is from Rails Guides v.3). Read up on Rails Guides v 3.2.13 look for 4.4.2.19 :uniq

Rails Guide v.4 specifically warns against using include? for checking for uniqueness because of possible race conditions.

The part about adding an index to join table stays.