在Postgresql中，强制两个列的组合为unique

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.

我想在postgresql中设置一个表，这样两个列必须是唯一的。任何一个值都可以有多个值，只要没有两个值同时共享。

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)

所以，col1和col2可以重复，但不能同时重复。所以，这是允许的(不包括id)

but not this:

但并不是这样的:

1 1
1 2
1 1 -- would reject this insert for violating constraints

3 个解决方案

#1

101

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.

自动增量不是postgresql。你想要一个系列。

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

如果col1和col2是唯一的，并且不能为空，那么它们就是一个很好的主键:

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

#2

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

创建唯一的约束，两个数字一起不能重复:

ALTER TABLE someTable
ADD UNIQUE (col1, col2)

#3

Seems like regular UNIQUE CONSTRAINT :)

似乎正则唯一约束:)

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

More here

更多的在这里

#1

101

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