nepsilon/pg-unique.md

Last active July 9, 2016 00:35

Select an option

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/nepsilon/00de8a339d0348a53b7c.js"></script>
Save nepsilon/00de8a339d0348a53b7c to your computer and use it in GitHub Desktop.

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

Raw

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