unique_multicol_index.markdown

A multi-column unique index

This example walks through enforcing a unique constraint on multiple columns.

As an example, let’s look at an appointment application for a vet surgery. Each customer has one or more pets. To ensure we don’t enter a customer’s pet twice we want to make sure a pet’s name is unique for that customer. This means the customer Jill can have a pet named Sparkles. Another customer, Bob, can also have a pet named Sparkles, but a customer cannot have more than one pet named Sparkles: Bob cannot have two cats named Sparkles.

Here’s an example schema:

Customers

name	type
id	integer
name	string

The pets table has a foreign key for the customer it belongs to.

Pets

name	type
id	integer
customer_id	integer
name	string

Create the tables:

CREATE TABLE "customers" (
    "id" SERIAL PRIMARY KEY, 
    "name" varchar
);

CREATE TABLE "pets" (
    "id" SERIAL PRIMARY KEY, 
    "customer_id" integer, 
    "name" varchar
);

(I’m using Postgres in this examples but SQLite also supports this concept with slightly different syntax).

Now to enforce the uniqueness constraint with a UNIQUE index on two columns: name and user_id.

CREATE UNIQUE INDEX "index_pets_on_name_and_customer_id" ON "pets" ("name", "customer_id");

Add some customers:

INSERT INTO customers (name) VALUES ('Jill'), ('Bob');

Creating a pet for Jill works:

INSERT INTO pets (customer_id, name) VALUES (1, 'Sparkles');

and for Bob:

INSERT INTO pets (customer_id, name) VALUES (2, 'Sparkles');

But I can’t create a second pet for Bob named Sparkles:

INSERT INTO pets (customer_id, name) VALUES (2, 'Sparkles');
ERROR:  duplicate key value violates unique constraint "index_pets_on_name_and_customer_id"
DETAIL:  Key (name, customer_id)=(Sparkles, 2) already exists.

Rails migrations

Rails also supports creating a unique, multicolumn index with the add_index method (docs) in a database migration:

class CreateCustomersAndPetsTable < ActiveRecord::Migration[5.2]
  def change
    create_table :customers do |t|
      t.string :name
    end

    create_table :pets do |t|
      t.references :customer
      t.string :name
    end

    add_index :pets, [:name, :customer_id], unique: true
  end
end