SQL DDL: рекурсивная таблица с двумя внешними ключами (MySQL)

Question

У меня есть subject таблица, которая является рекурсивной, потому что предмет может быть предпосылкой другого предмета:

• У субъекта не может быть предпосылок
• У предмета может быть 1 предпосылка
• Предмет может иметь 2 предпосылки

Поскольку есть предметы с 2 предпосылками, я сделал prerequisite_1 и prerequisite_2 столбцы ФК, указывающие на 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 и таблица не создается.

Как создать рекурсивную таблицу с одним столбцом PK и двумя столбцами FK, указывающими на собственный PK таблицы?

Answer 1

Вам нужно разделить их на два отдельных внешних ключа, и вы, вероятно, должны (но не обязаны) назвать их;

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 2

Вместо того, чтобы ставить предпосылки в subject table, используйте таблицу отношений "многие ко многим":

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 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;