A migration to add unique constraint to a combination of columns

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.

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

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][1]


[1]:

[To see links please register here]

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.)

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:

`db/schema.rb` generated:

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`

`add_index :people, [:firstname, :lastname, :dob], unique: true`

If you are creating a new table just add unique: true

class CreatePosts < ActiveRecord::Migration[6.0]
def change
create_table :posts do |t|
t.string :title, unique: true
t.text :body
t.references :user, foreign_key: true
t.timestamps
end

add_index :posts, :user_id, unique: true
end
end