问题:多对多中的唯一性

我无法弄清楚谷歌的哪些术语,所以帮助标记这个问题或只是以相关问题的方式向我指出会有所帮助。

我相信我有一个典型的多对多关系:

CREATE TABLE groups (
  id integer PRIMARY KEY);

CREATE TABLE elements (
  id integer PRIMARY KEY);

CREATE TABLE groups_elements (
  groups_id integer REFERENCES groups,
  elements_id integer REFERENCES elements,
  PRIMARY KEY (groups_id, elements_id));

我想要一个约束,对于给定的一组元素_id,只能有一个组_id。

例如,以下是有效的:

groups_id | elements_id
        1 | 1
        1 | 2
        2 | 2
        2 | 3

以下内容无效,因为第 1 组和第 2 组将是等效的。

groups_id | elements_id
        1 | 1
        1 | 2
        2 | 2
        2 | 1

不是每个元素子集都必须有一个组(这不是幂集),但可能会形成新的子集。我怀疑我的设计不正确,因为我实际上是在谈论将组添加为单个实体。

如何为元素子集创建标识符而不存在重复子集的风险?

解答

来自用户 ypercube 的这个链接最有帮助:unique constraint on a set。简而言之,每个人都说的有点是正确的。

这是一个权衡的问题,但这里是最好的选择:

a) 将散列或元素值的其他组合添加到 groups 表并使其唯一,然后使用触发器填充 groups_elements 表。这种方法的优点是它保留查询能力并强制执行约束,只要您拒绝对组_elements 的裸更新。缺点是它增加了复杂性,并且您现在已经在数据库中引入了诸如“如何唯一地表示一组元素”之类的逻辑。

b) 保持表格原样并使用您的访问层控制对 groups_elements 的访问,无论是存储过程还是其他方式。这具有保留查询能力并保持数据库本身简单的优点。但是,这意味着您正在将分析约束移至访问层,这必然意味着您的访问层需要更复杂。还有一点就是,它把数据应该是什么和数据本身分开了,这有利有弊。如果您需要更快地访问集合是否已经存在,您可以单独解决该问题。