MySQL 对两个使用相同外键 ID 的唯一键进行约束

Question

我想创建一个 MySQL 表来保存用户之间的关系数据。用户A与B之间以及用户B与A之间的关系可以不同。

示例：

1. 鲍勃（与）爱丽丝的关系：0.9 - 鲍勃喜欢爱丽丝的东西。
2. 爱丽丝（与）鲍勃的关系：0.5 - 爱丽丝发现鲍勃的东西平庸。

我的问题：

我已经在引用用户表中的 user_ids 的两个外键上实现了两个约束作为唯一键。我可以这样做吗？它们是否被视为两个单独的唯一密钥？

如何实现一个约束，该约束仅允许每个 user_id 每个（从）UserA（到）UserB 关系和（从）UserB（到）UserA 关系出现一次？我以正确的方式处理这件事吗？

SQL：

CREATE TABLE relationships (
  relationship_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  from_user_id MEDIUMINT UNSIGNED NOT NULL,
  to_user_id MEDIUMINT UNSIGNED NOT NULL,
  relationship_level DECIMAL(1,1) NOT NULL,
  PRIMARY KEY (relationship_id),

  FOREIGN KEY (from_user_id) REFERENCES users (user_id)
    ON DELETE CASCADE ON UPDATE NO ACTION,

  FOREIGN KEY (to_user_id) REFERENCES users (user_id)
    ON DELETE CASCADE ON UPDATE NO ACTION,

  CONSTRAINT from_to_relationship UNIQUE KEY (from_user_id, to_user_id),
  CONSTRAINT to_from_relationship UNIQUE KEY (to_user_id, from_user_id),

  INDEX relationship_from_to (relationship_id, from_user_id, to_user_id, relationship_level),
  INDEX relationship_to_from (relationship_id, to_user_id, from_user_id, relationship_level)

) ENGINE=INNODB;

我希望有人能提供帮助。

Answer 1

仅保留这些UNIQUE 约束之一 - 无需同时拥有两者。当一行失败 UNIQUE KEY (from_user_id, to_user_id) 时，它也会失败 UNIQUE KEY (to_user_id, from_user_id)，反之亦然，因此它们在逻辑上是等效的。即使只有一个 UNIQUE 约束，当尝试表示 Alice 和 Bob 之间的关系时，最多可以有一个 {Alice, Bob} 行，and 最多可以有一个 {鲍勃，爱丽丝}行。

至于性能（即双向遍历关系），您可能需要考虑索引 {from_user_id, to_user_id} （用于“向前”遍历）和/或 {to_user_id, from_user_id}< /code> （用于“向后”遍历）。您甚至可以放弃代理主键 (relationship_id) 并采用自然 PK，从而降低对索引的需求（辅助索引对于聚集表来说非常昂贵，请参阅 了解 InnoDB 聚集索引，“缺点”部分聚类”）。

在我看来，您的表格应该如下所示：

CREATE TABLE relationships (
    from_user_id MEDIUMINT UNSIGNED NOT NULL,
    to_user_id MEDIUMINT UNSIGNED NOT NULL,
    relationship_level DECIMAL(1,1) NOT NULL,
    PRIMARY KEY (from_user_id, to_user_id), -- InnoDB is clustered, so naturally "covers" relationship_level as well.
    FOREIGN KEY (from_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    FOREIGN KEY (to_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    INDEX relationship_to_from (to_user_id, from_user_id, relationship_level) -- Including relationship_level may or may not be a good idea.
) ENGINE=INNODB;

注意：是否在 INDEX 中包含 relationship_level 取决于您是否想要 仅索引扫描 也以“向后”方向进行。 “前进”方向自然被PK覆盖（因为InnoDB是