Rails Uniqueness Between 3 Columns When 2 Columns are Foreign Keys to the Same Table

Question

I have two tables, Assets and Relationships. They look like this (plus other columns I'm omitting for brevity):

# Table name: relationships
#
#  id             :uuid             not null, primary key
#  asset1_id      :uuid             not null
#  asset2_id      :uuid             not null
#  type           :string           not null

# Table name: assets
#
#  id             :uuid             not null, primary key
#  type           :string           not null
#  name           :string           not null

I want relationships of a type between two assets to be unique. So for example, let's say I have a relationship of type membership.

Relationship.create!(type: 'membership', asset1_id: '61d58a49-86a9-4d7f-b069-2ed1fa27b387', asset2_id: '1856df48-3193-45de-bef0-122cd9f58d7b')

If I try to create that record again, I can easily prevent it with validates :type, uniqueness: { scope: [:asset1_id, :asset2_id] } and add_index :relationships, [:type, :asset1_id, :asset2_id], unique: true, however when I use these the following case is not prevented:

Relationship.create!(type: 'membership', asset1_id: '1856df48-3193-45de-bef0-122cd9f58d7b', asset2_id: '61d58a49-86a9-4d7f-b069-2ed1fa27b387')

Note that this is the same as the previous record, only with the order of the asset ids reversed.

How can I prevent this (preferably at the DB level)?

Rajdeep Singh · Answer 1 · 2020-02-24T16:02:50.277

1

You can do it at application level by adding custom validation

validates :type, uniqueness: { scope: [:asset1_id, :asset2_id] }
validate :reverse_type_uniqueness

def reverse_type_uniqueness
  duplicate_present = self.class.where(type: type, asset1_id: asset2_id, asset2_id: asset1_id).exists?
  errors.add(:base, "Duplicate present") if duplicate_present?
end

To implement 2 sided unique index at DB level, here is an example, not very straight forward though

https://dba.stackexchange.com/questions/14109/two-sided-unique-index-for-two-columns

edited Feb 24 '20 at 16:02

answered Feb 24 '20 at 15:31

Rajdeep Singh

17,621
6
53
78

The application level you provided does not seem to work, but the DB link looks useful. I will do some research and see what a comparable solution for Postgres looks like. – dyeje Feb 24 '20 at 15:55
@dyeje Did you give it a try? – Rajdeep Singh Feb 25 '20 at 09:45

score 0 · Answer 2 · answered Feb 24 '20 at 15:26

If you want to validate it at the DB level, you need to setup a compound index on both fields on your join table. More info can be found here: How to implement a unique index on two columns in rails

Assuming you call your join table memberships, try the following migration:

add_index :memberships, [:relationship_id, :asset_id], unique: true

Alternatively, to let rails handle the validation:

class Membership < ActionRecord::Base
  validates_uniqueness_of :relationship_id, scope: :membership_id
  ...
end

More reading on the rails validation: https://apidock.com/rails/ActiveRecord/Validations/ClassMethods/validates_uniqueness_of

Rails Uniqueness Between 3 Columns When 2 Columns are Foreign Keys to the Same Table

2 Answers2