主页 / dba / 问题 / 48568
Accepted
jpmc26
Asked: 2013-08-23 17:11:50 +0800 CST

如何关联同一张表中的两行

  • 772

我有一个表，其中行可以相互关联，从逻辑上讲，两行之间的关系是双向的（基本上是无方向的）。（如果您想知道，是的，这确实应该是一张表。这是完全相同的逻辑实体/类型的两件事。）我可以想到几种方法来表示这一点：

  1. 存储关系及其反向
  2. 以一种方式存储关系，限制数据库以另一种方式存储它，并且有两个 FK 顺序相反的索引（一个索引是 PK 索引）
  3. 使用两个索引以一种方式存储关系并允许插入第二个索引（听起来有点恶心，但是嘿，完整性）
  4. 创建某种分组表，并在原始表上对其进行 FK。（提出了很多问题。分组表只有一个数字；为什么还要有表？使 FK 为 NULLable 或有与单行关联的组？）

这些方式有哪些主要优点和缺点，当然，有没有我没有想到的方式？

这是一个可以使用的 SQLFiddle：http ://sqlfiddle.com/#!12/7ee1a/1/0 。（碰巧是 PostgreSQL，因为那是我正在使用的，但我认为这个问题不是 PostgreSQL 特有的。）它目前存储关系和它的反向只是作为一个例子。

database-design foreign-key

1 个回答

  1. Best Answer

    你设计的很好。需要添加的是使关系无方向的约束。因此，如果没有添加行，您就不能拥有(1,5)一行(5,1)。

    这可以通过桥表上的自引用约束来完成。

    *：它可以在 Postgres、Oracle、DB2 和所有已实现 SQL 标准所描述的外键约束的 DBMS 中完成（延迟，例如在事务结束时检查）。无论如何，实际上并不需要延迟检查，如 SQL-在语句末尾检查它们的服务器并且此构造仍然有效。你不能在 MySQL 中这样做，因为"InnoDB checks UNIQUE and FOREIGN KEY constraint row-by-row"。

    因此，在 Postgres 中，以下内容将符合您的要求：

    CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

    测试于：SQL-Fiddle

    如果您尝试添加一行(1,5)：

    INSERT INTO bridge_x VALUES
(1,5) ;

    它失败了：

    错误：在表“bridge_x”上插入或更新违反外键约束“x_x_directionless”
    详细信息：表“bridge_x”中不存在键（x_id2，x_id1）=（5，1）。：
    插入到bridge_x值（1,5）

    CHECK此外，如果要禁止(y,y)行，可以添加约束：

    ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

    x_id1正如您所提到的，还有其他方法可以实现这一点，例如通过强制较低的 id和较高的 id 在列中仅存储关系的一个方向（一行，而不是两个）x_id2。它看起来更容易实现，但通常会导致以后更复杂的查询：

    CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);
    • 10

相关问题