SQL：跨两列唯一值

Question

考虑两个列，值不唯一是否有约束条件，例如 -

id  |  secondid
+---------------+
 3  |         4
 4  |         5

id  |  secondid
+---------------+
 3  |         4
 5  |         4

id  |  secondid
+---------------+
 4  |         4
 4  |         4

所有上述情况都不可行，因为无论是在id还是secondid中，4都出现了两次，但像这样的情况就可以了。

id  |  secondid
+---------------+
 1  |         3
 2  |         4

由于两列中的所有值都是唯一的，因此这种情况是可以接受的。是否有一种不使用任何包在PostgreSQL中实现它的方法？

- Spongerooski

---

@mu 太短了：另一个请求涉及元组。他们想要防止(1,2)和(2,1)同时出现在表中，因为这是相同的元组，只是值反转了。然而，Spongerooski的请求是关于单个ID的。例如，他们想要防止(1,2)，(3,2)，因为一个ID出现了多次。 - Thorsten Kettner

@ThorstenKettner 谢谢，第二遍阅读让我觉得它比我最初想的更有趣。 - mu is too short

@Spongerooski……如果你需要这样做，那么我怀疑你的数据模型存在问题。你似乎只有一个ID，应该有一张表将该值放在其中一列中。 - Gordon Linoff

戈登说得有道理。真正的问题，因此解决方案可能真正在数据模型中。你应该认真考虑这一点（而不仅仅是跟随戈登的答案，在技术上完美地解决了问题）。 - Thorsten Kettner

Answer 1

您可以通过排除约束和检查约束的组合来实现这一点。检查约束用于防止在一行中出现重复。

create table t (
    id int,
    id2 int,
    check (id <> id2),
    exclude using gist ( (array[id, id2]) with &&)
);

排除约束是通过检查指定的运算符，确保新行中的列和表中所有已有的行都不会返回“true”来实现的。它不会检查当前行内的值，这就是为什么还需要 check 约束的原因。 这里 是一个 db<>fiddle 示例。

- Gordon Linoff

---

我似乎遇到了一个错误：“数据类型integer[]没有访问方法“gist”的默认运算符类”，我需要安装任何扩展吗？ - Spongerooski

@Spongerooski . . . https://www.postgresql.org/docs/current/intarray.html. - Gordon Linoff

Answer 2

你希望有一个唯一限制条件，该条件可以像针对单个列一样作用于这两个列。我认为这并不是直接实现的。（其他人可以纠正我。）

你可以创建另一个表。

create table check_unique_id (id int primary key);

每次在表中插入一行时，触发器会在check_unique_id表中创建两行。如果一个ID出现两次，那么其他表将引发异常。请注意，保留HTML标签。

- Thorsten Kettner

---

我本来希望有一种直接的方法，但在我看来这似乎有点hacky，但如果没有其他方法，我肯定会选择这个，谢谢:D - Spongerooski

我认为您可以使用 EXCLUDE 约束来实现此目的。如果您 CREATE EXTENSION intarray，然后 exclude using gist ((array[id, secondid]) with &&)。 - mu is too short

@mu 太短了：聪明。Gordon使用相同的想法发布了一个答案。看起来完美无缺。 - Thorsten Kettner