使用 SQL 定义一对多关系

Question

我正在寻找一种在两个表之间建立一对多关系的方法。下面解释了表结构，但我试图忽略与问题无关的所有内容。

表 objects 有 1 列，称为 uuid。

表 contents 有 3 列，分别称为 content、object_uuid 和 timestamp。

基本思想是向 objects 中插入一行并从数据库获取一个新的 uuid。然后，使用此 uuid 存储在 contents 中的每一行，以将内容与对象关联起来。

现在我尝试使用数据库来强制执行：

• contents 中的每一行都引用 objects 中的一行（应该使用外键）
• objects 中没有行 存在，contents 中至少没有行

这些约束应在提交事务时强制执行。

普通触发器无济于事，可能是因为当写入 objects 表中的一行时，contents 中还没有一行。 Postgres 确实有所谓的约束触发器，可以推迟到事务结束。可以使用它们，但它们似乎是某种内部构造，不适合日常使用。

想法或解决方案应该是标准 SQL（首选）或与 Postgres 一起使用（版本无关紧要）。感谢您的任何意见。

Answer 1

您的主要问题是除了外键约束之外；没有约束可以引用另一个表。

最好的选择是对其进行稍微非规范化，并在 object 上有一列，其中包含引用它的 contents 的数量。您可以创建一个触发器来保持最新状态。

contents_count INTEGER NOT NULL DEFAULT 0

除非您对谁可以更新此列设置一些用户安全措施，否则这不会是牢不可破的。但是，如果您通过触发器使其保持最新，并且您想要避免的只是意外损坏，那么这应该足够了。

编辑：根据评论，CHECK 约束不可推迟。如果删除所有内容，即使目的是在同一事务中添加更多内容，此解决方案也会引发错误。

Answer 2

也许你想做的就是更加正常化一点。您需要第三个表，它引用其他表的元素。表对象应该有自己的uuid，表内容也应该有自己的uuid，并且不引用表对象。第三个表应该只包含对其他两个表的引用，但主键是两个引用的组合。
例如，您有一个表 objects 的 uuid，并且您想要该 uuid 的所有内容，假设第三个表具有列 object_uuid 和 content_uuid，并且表内容有自己的串行列，名为uuid，您的查询应该是这样的：

SELECT * FROM thirdtable,contents 
   WHERE thirdtable.content_uuid = contents.uuid AND thirdtable.object_uuid=34;

然后您可以在每个表上使用插入触发器

CREATE TRIGGER my_insert_trigger AFTER INSERT OR UPDATE ON contents
   FOR EACH ROW EXECUTE PROCEDURE my_check_function();

，然后在函数 my_check_function() 中删除第三个表中不存在的 objects 中的每一行。我回答的时候其他人先回答了，如果你们喜欢我的解决方案，我可以帮助你们制作 my_check_function() 函数。