A migration to add unique constraint to a combination of columns

user_111489 0
Viewed 88644

What I need is a migration to apply unique constraint to a combination of columns. i.e. for a people table, a combination of first_name, last_Name and Dob should be unique.

ruby-on-rails database
6 Answers

For completeness sake, and to avoid confusion here are 3 ways of doing the same thing:
Adding a named unique constraint to a combination of columns in Rails 5.2+

Let's say we have Locations table that belongs to an advertiser and has column reference_code and you only want 1 reference code per advertiser. so you want to add a unique constraint to a combination of columns and name it.

Do:

rails g migration AddUniquenessConstraintToLocations

And make your migration look either something like this one liner:

class AddUniquenessConstraintToLocations < ActiveRecord::Migration[5.2]
  def change
    add_index :locations, [:reference_code, :advertiser_id], unique: true, name: 'uniq_reference_code_per_advertiser'
  end
end

OR this block version.

class AddUniquenessConstraintToLocations < ActiveRecord::Migration[5.2]
  def change
    change_table :locations do |t|
     t.index ['reference_code', 'advertiser_id'], name: 'uniq_reference_code_per_advertiser', unique: true
    end
  end
end

OR this raw SQL version

class AddUniquenessConstraintToLocations < ActiveRecord::Migration[5.2]
  def change
      execute <<-SQL
          ALTER TABLE locations
            ADD CONSTRAINT uniq_reference_code_per_advertiser UNIQUE (reference_code, advertiser_id);
        SQL
  end
end

Any of these will have the same result, check your schema.rb

user_56412270
Related