增强相关实体的唯一性 [英] Enforce Uniqueness of Related Entities

问题描述

在关系数据库（SQL）中，我有一个父实体，该父实体可以具有0..n个相关的子实体。父实体在某种程度上由其相关子实体的集合唯一地标识，因此我不应该拥有两个具有相同子集合的相似父代。

In a relational database (SQL), I have a parent entity that can have 0..n related child entities. The parent entity is uniquely identified in part by its collection of related child entities, such that I should not be able to have two similar parents with the same collection of children.

所以我可以让父母1的孩子1和孩子2，以及父母2的孩子2和孩子3，但是我不能再让另一个父母与孩子2和孩子3一起

So I could have Parent 1 with Child 1 and Child 2, and Parent 2 with Child 2 and Child 3, but I cannot have another parent with Child 2 and Child 3.

理想情况下，我想使用数据库约束来强制这种唯一性。我曾考虑过与父母一起存储所有子记录的哈希，但是想知道是否有更简单/更标准的方法来完成此操作。

Ideally, I would like to enforce this uniqueness using a database constraint. I've considered storing a hash of all child records with the parent, but was wondering if there was an easier / more standard way of accomplishing this.

有什么想法吗？

推荐答案

这种约束比较棘手，因为SQL没有关系相等运算符，即没有简单的方法来求A = B，其中A和B是行集。标准SQL确实支持嵌套表，但不幸的是SQL Server不支持。

This kind of constraint is tricky because SQL has no relational equality operator, i.e. no simple way of evaluting A=B where A and B are sets of rows. Standard SQL does support nested tables but unfortunately SQL Server does not.

一个可能的答案是如下谓词，该谓词检查表中是否有相同的族：

One possible answer is a predicate like the following, which checks for any identical families in a table:

NOT EXISTS (
    SELECT 1
    FROM family f, family g
    WHERE f.child = g.child
    AND f.parent <> g.parent
    GROUP BY f.parent, g.parent
    HAVING COUNT(*) = (SELECT COUNT(*) FROM family WHERE parent = f.parent)
    AND COUNT(*) = (SELECT COUNT(*) FROM family WHERE parent = g.parent)
    )

请注意，此查询不会尝试处理没有孩子的家庭。用集合论的术语来说，两个空集合必须是相同的。如果您想允许没有孩子的家庭，则必须决定是否将两个没有孩子的家庭视为相同。

Notice that this query doesn't attempt to deal with childless families. In set-theoretic terms two empty sets are necessarily identical. If you want to allow for childless families then you would have to decide whether two childless families should be deemed identical or not.

SQL并不是一种真正的关系语言，它与关系语言应该具备的能力相比还差很多。教程D是一种真实的关系语言的示例，它确实支持关系相等和关系值属性。原则上，在教程D中，您可以将每个族表示为relvar中单个属性的值。该家庭属性也可以是关键，因此不允许重复的家庭。

SQL is not a truly relational language and it falls well short of what a relational language ought to be capable of. Tutorial D is an example of a real relational language that does support relational equality and relation-valued attributes. In Tutorial D you can in principle represent each family as a value of a single attribute in a relvar. That family attribute can also be a key and therefore duplicate families would not be allowed.

这篇关于增强相关实体的唯一性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！