本文介绍了Postgres 跨表的唯一组合约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有三张桌子 -
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 + 文件名是唯一的.到目前为止,我最好的解决方案是将文件名添加到 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 跨表的唯一组合约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!