跨多列的唯一值约束 [英] Unique value constraint across multiple columns

问题描述

假设，我有下表:

CREATE TABLE "user" (
    id     BIGINT PRIMARY KEY NOT NULL,
    phone1 VARCHAR,
    phone2 VARCHAR
);

我需要实现以下限制:表中的所有电话号码(如果有)必须是唯一的.

And I need to implement the following limitation: all phone numbers (if any) in table must be unique.

即数据库不应允许以下任何一种情况:

i.e database should not allow any of the following situations:

id | phone1 | phone2
1  | 111    | 111

id | phone1 | phone2
1  | 111    | NULL
2  | 111    | NULL  

id | phone1 | phone2
1  | 111    | NULL
2  | NULL   | 111 

我知道如何为前两个示例实现约束，但我坚持使用第三个示例.有没有办法做到这一点?

I know how to implement constraints for first two examples, but I'm stuck with the third one. Is there any way to do this?

推荐答案

试试老方法:

db=# create unique index on "user" (least(phone1,phone2), greatest(phone1,phone2));
CREATE INDEX
Time: 14.507 ms
db=# insert into "user" values(1,111,111);
INSERT 0 1
Time: 35.017 ms

rest 会失败:

db=# insert into "user" values(2,111,null);
ERROR:  duplicate key value violates unique constraint "user_least_greatest_idx"
DETAIL:  Key ((LEAST(phone1, phone2)), (GREATEST(phone1, phone2)))=(111, 111) already exists.
Time: 10.323 ms
db=# insert into "user" values(2,null,111);
ERROR:  duplicate key value violates unique constraint "user_least_greatest_idx"
DETAIL:  Key ((LEAST(phone1, phone2)), (GREATEST(phone1, phone2)))=(111, 111) already exists.
Time: 5.553 ms
db=# insert into "user" values(1,111,111);
ERROR:  duplicate key value violates unique constraint "user_pkey"
DETAIL:  Key (id)=(1) already exists.
Time: 11.067 ms

这篇关于跨多列的唯一值约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！