时间戳

首页 > 解决方案 > 如何管理跨两个表的唯一列

postgresql - 如何管理跨两个表的唯一列

问题描述

我有一个带有主键的 Files 表file_id。

用户可以上传各种文件，这些文件将附加到许多不同的表格中。有人可能会使用不相关的 file_id 手动向我的 API 发布请求，这在技术上允许他们访问在非常特定的情况下不应该访问的文件。

不幸的是，没有一种好的方法可以重构过去的工作方式以防止这种情况发生。

我的逻辑是，只需使文件 id 在两个表中都是唯一的。问题解决了。如果有人尝试发送不应该使用 file_id 的请求，我们会看到它已经存在于另一个表中并发送回 500。

但是，我不确定解决此问题的最佳方法是什么......

我的第一个想法是添加一个约束。虽然我不确定你会如何管理这个。我所做的大多数研究都提供了不推荐的奇怪解决方法。虽然理想情况下，如果有一种干净的方法可以做到这一点，这是我更愿意这样做的方式。

我的第二个是添加如下内容：

insert into table_a (file_id)
select file_id where not exists (select file_id from table_b where table_b.file_id = file_id);

然后对 table_b 反之亦然

这对我来说似乎也有点草率。感觉这应该是架构本身的解决方案，而不是用于插入的查询。

标签： postgresql

解决方案

如果没有太多表，您可以从表中为每个表添加外键，而不是包含文件并以这种方式对关联进行建模。

我们称这些列tablea_id和tableb_id.

要确保一个文件不能与多个表关联，请添加一个检查约束：

ALTER TABLE files ADD CHECK ((tablea_id IS NULL AND tableb_id IS NOT NULL) OR 
                             (tablea_id IS NOT NULL AND tableb_id IS NULL));

如果要确定与任一表中的任何行关联的文件不超过一个，请在tablea_id和上放置唯一约束tableb_id。

推荐阅读