362

I would like to set up a table in PostgreSQL such that two columns together must be unique. There can be multiple values of either value, so long as there are not two that share both.

For instance:

CREATE TABLE someTable (
    id int PRIMARY KEY AUTOINCREMENT,
    col1 int NOT NULL,
    col2 int NOT NULL
)

So, col1 and col2 can repeat, but not at the same time. So, this would be allowed (Not including the id)

1 1
1 2
2 1
2 2

but not this:

1 1
1 2
1 1 -- would reject this insert for violating constraints
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
PearsonArtPhoto
  • 38,970
  • 17
  • 111
  • 142

4 Answers4

449
CREATE TABLE someTable (
    id serial PRIMARY KEY,
    col1 int NOT NULL,
    col2 int NOT NULL,
    UNIQUE (col1, col2)
)

autoincrement is not postgresql. You want a integer primary key generated always as identity (or serial if you use PG 9 or lower. serial was soft-deprecated in PG 10).

If col1 and col2 make a unique and can't be null then they make a good primary key:

CREATE TABLE someTable (
    col1 int NOT NULL,
    col2 int NOT NULL,
    PRIMARY KEY (col1, col2)
)
S.R
  • 2,411
  • 1
  • 22
  • 33
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 9
    I like the suggestion of a primary key over unique here, because we do not allow NULL values in this case. From the PostgeSQL docs: "Note that a unique constraint does not, by itself, provide a unique identifier because it does not exclude null values.)" https://www.postgresql.org/docs/8.1/static/ddl-constraints.html#AEN2038 – ndequeker Feb 03 '17 at 13:53
  • How can I implement this in schema definition? – wagng Mar 22 '18 at 13:48
  • 6
    In some scenarios you might want a surrogate key to be used as a primary key rather than a combination of columns. In particular to improve performance when doing joins on big data volumes. I personally went for the UNIQUE CONSTRAINT solution below. – Alexis.Rolland Jan 13 '19 at 07:54
  • 1
    Is it possible to enforce a unique constraint on just one permutation, such as unique(col1, col2 = '1')? – Vikram Khemlani Nov 12 '19 at 19:20
  • postgres: https://stackoverflow.com/questions/16236365/postgresql-conditionally-unique-constraint sql server: https://stackoverflow.com/a/5149263/5986661 – Omkar Neogi Jan 03 '20 at 18:42
  • But don’t sequential integers as primary keys outperform combinations as primary keys in indexing? Performance-wise. – Константин Ван Apr 02 '22 at 13:21
  • Would this treat col 1 as and 2 as unique as well? Do I have to make each col and the combination unique to achieve this use case? In other words, I want col 1 to be unique,.and col 2 to be unique and lastly the combination be unique. – awm Sep 17 '22 at 22:24
277

Create unique constraint that two numbers together CANNOT together be repeated:

ALTER TABLE someTable
ADD UNIQUE (col1, col2)
PearsonArtPhoto
  • 38,970
  • 17
  • 111
  • 142
djangojazz
  • 14,131
  • 10
  • 56
  • 94
40

If, like me, you landed here with:

  • a pre-existing table,
  • to which you need to add a new column, and
  • also need to add a new unique constraint on the new column as well as an old one, AND
  • be able to undo it all (i.e. write a down migration)

Here is what worked for me, utilizing one of the above answers and expanding it:

-- up

ALTER TABLE myoldtable ADD COLUMN newcolumn TEXT;
ALTER TABLE myoldtable ADD CONSTRAINT myoldtable_oldcolumn_newcolumn_key UNIQUE (oldcolumn, newcolumn);

---

ALTER TABLE myoldtable DROP CONSTRAINT myoldtable_oldcolumn_newcolumn_key;
ALTER TABLE myoldtable DROP COLUMN newcolumn;

-- down
Tay Hess
  • 563
  • 5
  • 11
35

Seems like regular UNIQUE CONSTRAINT :)

CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c));

More here

Timur Sadykov
  • 10,859
  • 7
  • 32
  • 45
  • 1
    Does this add an index for `a` and an index for `c` independently? Because I need to quickly find based on `a` sometimes, and quickly find based on `c` sometimes. – CMCDragonkai Sep 16 '19 at 13:08
  • @CMCDragonkai This index on a,c internally serve for two types of queries. A query with 'a' as where clause and a query with columns a and c present in the where clause will be benefited from the above index. In both ways, column 'a' should be present, as it's the look-up column. – Akhil Mathew Aug 13 '21 at 08:00