mysql - 防止多对多连接表在与 self 连接时出现重复的规范方法

Question

我正在为数据库项目建模一所学校，并且我有多对多关系，加入 course表与自身建立先决条件。该表(简化)如下所示:

CREATE TABLE course_prereqs (
  course_id INT NOT NULL,
  prereq_id INT NOT NULL,

  PRIMARY KEY (course_id, prereq_id),
  FOREIGN KEY (course_id) REFERENCES course (course_id),
  FOREIGN KEY (prereq_id) REFERENCES course (course_id)
)

我想知道的是可以防止这两种情况的最简单、也是最用户友好的方式 (1, 2), and (2, 1)是表中的先决条件，因为它会导致循环依赖。

我在想等同于

-- in a trigger though since MySQL doesn't have check
CHECK (course_id < prereq_id) 

虽然这有效，但我觉得最终用户总是确保 course_id 可能会很烦人。始终小于prereq_id .

是否有更好的方法，或者我是否高估了必须确保 course_id 带来的不便？总是更小？

Answer 1

这似乎是一个应用程序问题。当说“类(class) #1 是类(class) #2 的先决条件”时，稍后说“类(class) #2 是类(class) #1 的先决条件”是不正确的。也就是说，为什么需要数据库检查一些不应该发生的事情？

另一方面，如果“关系”是自反的(1->2 意味着 2->1)，那么我会这样做:

INSERT IGNORE (a, b) VALUES (LEAST($this, $that), GREATEST($this, $that));

这样就有一个“规范”的顺序来存储关系。测试变得更加困惑。

或者...存储这两个关系。行数只有两倍，但查找性能并没有明显受到影响。

在这两种情况下，您都需要两个索引:

PRIMARY KEY(a, b),
INDEX      (b, a)