A migration to add unique constraint to a combination of columns

Question

add_index :people, [:firstname, :lastname, :dob], :unique => true

You may want to add a constraint without an index. This will depend on what database you're using. Below is sample migration code for Postgres. (tracking_number, carrier) is a list of the columns you want to use for the constraint.

class AddUniqeConstraintToShipments < ActiveRecord::Migration
  def up
    execute <<-SQL
      alter table shipments
        add constraint shipment_tracking_number unique (tracking_number, carrier);
    SQL
  end

  def down
    execute <<-SQL
      alter table shipments
        drop constraint if exists shipment_tracking_number;
    SQL
  end
end

There are different constraints you can add. Read the docs

According to howmanyofme.com, "There are 46,427 people named John Smith" in the United States alone. That's about 127 years of days. As this is well over the average lifespan of a human being, this means that a DOB clash is mathematically certain.

All I'm saying is that that particular combination of unique fields could lead to extreme user/customer frustration in future.

Consider something that's actually unique, like a national identification number, if appropriate.

(I realise I'm very late to the party with this one, but it could help future readers.)

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

Hi You may add unique index in your migration to the columns for example

add_index(:accounts, [:branch_id, :party_id], :unique => true)

or separate unique indexes for each column

In the typical example of a join table between users and posts:

create_table :users
create_table :posts

create_table :ownerships do |t|
  t.belongs_to :user, foreign_key: true, null: false
  t.belongs_to :post, foreign_key: true, null: false
end

add_index :ownerships, [:user_id, :post_id], unique: true

Trying to create two similar records will throw a database error (Postgres in my case):

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_ownerships_on_user_id_and_post_id"
DETAIL:  Key (user_id, post_id)=(1, 1) already exists.
: INSERT INTO "ownerships" ("user_id", "post_id") VALUES ($1, $2) RETURNING "id"

e.g. doing that:

Ownership.create!(user_id: user_id, post_id: post_id)
Ownership.create!(user_id: user_id, post_id: post_id)

Fully runnable example: https://gist.github.com/Dorian/9d641ca78dad8eb64736173614d97ced

db/schema.rb generated: https://gist.github.com/Dorian/a8449287fa62b88463f48da986c1744a