Postgresql Force is unique in combining two columns

Question

I would like to set up a table in postgresql so that the two columns together are unique. There can be several values ​​of any value if two sections do not exist.

For example:

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

So col1 and col2 can be repeated, but not at the same time. This way it will be allowed (not including 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 1

 CREATE TABLE someTable ( id serial primary key, col1 int NOT NULL, col2 int NOT NULL, unique (col1, col2) ) 

autoincrement not postgresql. Do you want serial .

If col1 and col2 are unique and cannot be null, then they make a good primary key:

 CREATE TABLE someTable ( col1 int NOT NULL, col2 int NOT NULL, primary key (col1, col2) ) 

Answer 2

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

 ALTER TABLE someTable ADD UNIQUE (col1, col2) 

Answer 3

It seems like a regular UNIQUE CONSTRAINT :)

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

More here