Is it possible to add a uniqueness constraint accross two different but associated models?-CodePudding

I have the models Account and User. Both models have an email attribute.

An Account has_many :users and a User belongs_to :account

I would like to validate the uniqueness of the email accross both models when an Account is being created so the Account email is invalid if it's taken by a User (since the account email later becomes the admin user email).

I added a scope to the email constraint in the Account model but it is not working (the form is not being rejected).

Account model:

has_many :users
validates :email,   uniqueness: { scope: :users, case_sensitive: false }

What is the correct way to implement this? Do I need to add an index to the DB?

CodePudding user response：

This is an alternative method based on a separate table and a polymorphic assocation:

class CreateEmailAddresses < ActiveRecord::Migration[6.1]
  def change
    create_table :email_addresses do |t|
      t.string :email, unique: true
      t.references :entitity, polymorphic: true
      t.timestamps
    end
    add_index :email_addresses, [:entity_type, :entity_id], unique: true
  end
end

class EmailAddress < ApplicationRecord
  validates_uniqueness_of :email
  validates_uniqueness_of :entity_id, scope: :entity_type
  belongs_to :entity, polymorphic: true
end

class User < ApplicationRecord
  has_one :email_address, 
    as: :entity,
    dependent: :destroy
  delegate :email, to: :email_address
  accepts_nested_attributes_for :email_address
end

class Account < ApplicationRecord
  has_one :email_address, 
    as: :entity,
    dependent: :destroy
  delegate :email, to: :email_address
  accepts_nested_attributes_for :email_address
end

It avoids having to restructure your domain or create a user simply to create a email but will cause issues with authentication libraries such as Devise as well as lacking a real foreign key to guarentee referential integrity which can lead to orphaned records.

IMO not a great solution as it most likely will create as many problems as it solves.

CodePudding user response：

This an only be achieved with a

Since there is no way (AFAIK) to create indices across tables so you might want to just restructure your domain and add an "owner" (call it whatever you want) to the accounts table:

class AddOwnerToAccounts < ActiveRecord::Migration[6.1]
  def change
    add_reference :accounts, :owner, null: false, foreign_key: { to_table: 'users' }
  end 
end

class Account < ApplicationRecord
  has_many :users
  belongs_to :owner, 
    class_name: 'User',
    inverse_of: :owned_accounts
  delegate :email, to: :owner
end

class User < ApplicationRecord
  belongs_to :account
  has_many :owned_accounts,
    class_name: 'Account',
    foreign_key: :owner_id,
    inverse_of: :owner
end