跨表的Postgres唯一组合约束 [英] Postgres unique combination constraint across tables

问题描述

我有三个表-

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是唯一的.到目前为止，我拥有的最好的解决方案是将文件名添加到product_attachment表中，但是我想知道是否有避免这种情况的方法.

I want to ensure that when these are natural-joined, product_id + filename is unique. The best solution I have so far involves adding filename to the product_attachment table, but I'm wondering if there's a way to avoid that.

推荐答案

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

If the filename column is not unique you can add a custom constraint on your product_attachment table. Note that this will execute the query below on every insert and update, which is not ideal performance wise.

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))

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