跨表的Postgres唯一组合约束

Question

我有三个表格——

file (
  file_id int primary key
  filename text not null
  etc...
)
product (
  product_id int primary key
  etc....
)
product_attachment (
  product_id references product
  file_id references file
)

我希望当这些数据进行自然连接时，product_id + filename是唯一的。

目前我想到最好的解决方案是将filename添加到product_attachment表中，但我想知道是否有避免这种方法的方式。

- qcode peter

Answer 1

如果文件名列不是唯一的，您可以在product_attachment表上添加自定义约束。请注意，这将在每次插入和更新时执行下面的查询，从性能角度来看并不理想。

CREATE OR REPLACE FUNCTION check_filename(product_id integer, file_id integer)
RETURNS boolean AS
$$
    LOCK product_attachment IN SHARE MODE;
    SELECT (COUNT(*) = 0)
    FROM product_attachment pa
    JOIN file f1 ON f1.file_id = pa.file_id
    JOIN file f2 ON f1.filename = f2.filename
    WHERE pa.product_id = $1 AND f2.file_id = $2
$$
LANGUAGE 'plpgsql'

ALTER TABLE product_attachment
ADD CONSTRAINT check_filename CHECK
(check_filename(product_id, file_id))

- FuzzyTree

---

我不清楚你为什么在那里使用了 "for update"，而且我认为测试应该是 count(*) = 0（或者使用带有 "exists" 的子查询可能会产生更好的性能），但这正是我正在寻找的方法，谢谢！ - qcode peter

@qcodepeter 如果我理解文档正确的话，for update会获取表级别锁，但是像你提到的那样显式地锁定表可能更清晰、更安全。顺便说一下，如果有可能更新filename，你可能需要在file表上添加类似的约束条件。 - FuzzyTree

你说得没错，它确实会获取表级锁，但只是一个“行共享”锁，我认为这可能不够。事实上，我们目前不允许更新“文件名”，所以我不会担心这个问题，但如果允许更新的话，那就需要考虑了。 - qcode peter

CHECK约束仅针对本地元组数据，不应引用其他表。文档对此非常明确https://www.postgresql.org/docs/current/ddl-constraints.html。我们曾经这样做过，结果是数据库转储时出现错误。有关表，约束和函数初始化顺序的假设的问题 - 我不记得确切情况。最后我们必须进行更改。 - Pawel Zieminski

对于上述建议的解决方案，您可以使用约束触发器而不是CHECK约束，并将其推迟到事务的结束。这可能是一个足够好的解决方案，但它仍然不能保证唯一性，因为在函数运行之后和事务提交之前存在一个窗口，在并发场景下会违反约束条件。否则，创建另一个表，并通过触发器填充相关列以及唯一索引可能是唯一的方法。 - Pawel Zieminski

Answer 2

为什么不在product_attachment上添加唯一约束？

create unique index idx_product_attachment_2 on product_attachment(product_id, file_id);

这假设文件名是唯一的，您可以通过在该表中定义文件名为唯一来确保。

- Gordon Linoff

---

文件名在整个文件表中不是唯一的，不幸的是这是系统的要求。 - qcode peter