How to force UNIQUE on a combination of columns, with PostgreSQL? — First published in fullweb.io issue #25

pg-unique.md

How to force UNIQUE on a combination of columns with PostgreSQL?

Sometimes you want 2 columns to be UNIQUE, but together, not each on their side. Here is how to add this constraint with PostgreSQL:

Given the following table:

CREATE TABLE t_word (
  id serial primary key,
  col1 int NOT NULL,
  col2 int NOT NULL
);

Add UNIQUE constraint on col1 + col2 with:

ALTER TABLE t_word ADD UNIQUE (col1, col2); 

Bonus: If they can't be NULL either just make them a PRIMARY KEY with:

ALTER TABLE t_word ADD PRIMARY KEY (col1, col2);