Add DB uniqueness constraint across tables

We saw previously in this post how to add uniqueness database constraint for a single table. But in real-world applications, we have different associations between models, so let’s start with an example:

We have a service platform where a user can buy subscriptions for different services, the requirements are the following:

  • A user can have many subscriptions for different services
  • A user can have only ONE subscription for a set of services

Class diagram

For the presented diagram we will have those four models:

1. User class:

class User < ApplicationRecord 
                                             
  has_many :subscriptions     
                                                                                                                        
end

2. Subscription class with a custom validation:

class Subscription < ApplicationRecord

  belongs_to :user
  has_many :service_subscription_states
  has_many :services, through: :service_subscription_states

  validate :uniqueness_user_service_combination

  private

  def uniqueness_user_service_combination
    existing_subscription = Subscription
      .where.not(id: id)
      .where(user_id: user_id)
      .find do |subscription|
        subscription.services.sort == services.sort
       end

    errors.add(:subscription, "already exists") if existing_subscription
  end

end

3. Joint class for Service and Subscription:

class ServiceSubscriptionState < ApplicationRecord

  belongs_to :service
  belongs_to :subscription

end

4. Service class:

class Service < ApplicationRecord

  has_many :service_subscription_states
  has_many :subscriptions, thourgh: :service_subscription_states

end

Let’s study the current situation, we know that:

  • user_id belongs to subscription table
  • subscription_id and service_id belong to service_subscription_state table

At first, let’s try to make this problem easier to solve by adding a new column to subscription table that contains a hash generated from user_id and service_id. Also, we should add an index for this column.

The migration:

class AddUniqueTokenToSubscriptions < ActiveRecord::Migration[5.0]
  def change
    add_column :subscriptions, :unique_token, :text
    add_index :conversations, :unique_token, unique: true
  end
end

and finally, the method to generate unique_token content:

class Subscription < ApplicationRecord 
 
  .....
  before_save :generate_unique_token

  ....
  private
  ...

  def generate_unique_token
    self.unique_token = Digest::SHA1.hexdigest(
      [user_id, service.map(&:id).sort].flatten.join,
    )
  end

end

In this post, we saw how we solved this challenge by transforming it to a simple problem that we know already how to solve.

That’s it, so keep coding and have fun 🙂


© 2017-2019 Marouen Bousnina. All rights reserved.

Powered by Hydejack v8.5.2