Search code examples
mysqldatabaserecursionddlcreate-table

SQL DDL: Creating a recursive table (MySQL)

I am trying to create a recursive entity named Subject. A subject can be a prerequisite of another subject, that is why it is recursive. Here is what I have so far:

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 CHAR(7),
    PRIMARY KEY (subject_code),
    FOREIGN KEY (prerequisite) REFERENCES subject(subject_code)
)ENGINE=INNODB;

Is the above the correct way to create a recursive table?

Solution

  • Yes.

    All you really need is a "Father" column to relate with the parent subject. Your prerequisite column does the trick.

    You can check an example here. The idea is always the same.