In Postgresql force unique on combination of two columns

Question

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

User · Accepted Answer

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 serial  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

User · Answer

Create unique constraint that two numbers together CANNOT together be repeated   ALTER TABLE someTable ADD UNIQUE  col1  col2

User · Answer

Seems like regular UNIQUE CONSTRAINT     CREATE TABLE example   a integer  b integer  c integer  UNIQUE  a  c      More here

User · Answer

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

[sql] In Postgresql, force unique on combination of two columns

Examples related to sql

Examples related to postgresql

Examples related to unique