SQL DDL:带有两个外键的递归表(MySQL)

Question

我有一个subject表，它是递归的，因为一个主题可以是另一个主题的先决条件：

• 一个科目不能有任何先决条件
• 一个科目可以有1个先决条件
• 一个科目可以有两个先决条件

由于有两个先决条件的主题，我使prerequisite_1和prerequisite_2列FKs指向subject_code列：

CREATE TABLE subject(
    subject_code CHAR(7),
    subject_desc VARCHAR(255) NOT NULL,
    no_of_units TINYINT UNSIGNED NOT NULL CHECK(no_of_units >= 0 AND no_of_units < 13),
    prerequisite_1 CHAR(7),
    prerequisite_2 CHAR(7),
    PRIMARY KEY(subject_code),
    FOREIGN KEY(prerequisite_1, prerequisite_2) REFERENCES subject(subject_code)
)ENGINE=INNODB;

我在SQL Fiddle上尝试了这些代码，但没有创建表。

如何创建一个递归表，其中1个PK列和2个FK列指向表自己的PK？

Answer 1

不要将先决条件放在subject表中，而是使用多对多关系表：

CREATE TABLE prerequisite (
    subject_code CHAR(7),
    prerequisite CHAR(7),
    PRIMARY KEY (subject_code, prerequisite),
    FOREIGN KEY (subject_code) REFERENCES subject(subject_code),
    FOREIGN KEY (prerequisite) REFERENCES subject(subject_code)
)

这允许任意数量的先决条件。

Answer 2

您需要将它们拆分为两个单独的外键，您可能应该（但不必）为它们命名；

CREATE TABLE subject(
    subject_code CHAR(7),
    subject_desc VARCHAR(255) NOT NULL,
    no_of_units TINYINT UNSIGNED NOT NULL 
       CHECK(no_of_units >= 0 AND no_of_units < 13),
    prerequisite_1 CHAR(7),
    prerequisite_2 CHAR(7),
    PRIMARY KEY(subject_code),
    FOREIGN KEY fk_pr1(prerequisite_1) REFERENCES subject(subject_code),
    FOREIGN KEY fk_pr2(prerequisite_2) REFERENCES subject(subject_code)
) ENGINE=INNODB;

SQLfiddle。

Answer 3

所以，下面是我的评论：

我认为您的语法是错误的，请尝试将每个外键的foreign key内容更改为：

CREATE TABLE subject(
    .........
    CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`prerequisite_1`) REFERENCES `subject` (`subject_code`),
    CONSTRAINT `subject_ibfk_2` FOREIGN KEY (`prerequisite_2`) REFERENCES `subject` (`subject_code`)
)ENGINE=INNODB;