In Postgresql, force unique on combination of two columns

sql postgresql unique
PearsonArtPhoto picture PearsonArtPhoto · Jan 8, 2013 · Viewed 101.3k times · Source

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

Answer

Clodoaldo Neto picture Clodoaldo Neto · Jan 8, 2013 
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)
)

Related questions

Postgres unique constraint vs index

As I can understand documentation the following definitions are equivalent: create table foo ( id serial primary key, code integer, label text, constraint foo_uq unique (code, label)); create table foo ( id serial primary key, code integer, label text); create unique …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More