title: SQL DDL: Recursive table with two foreign keys (MySQL)
body: <p>I have a <code>subject</code> table that is recursive because a subject can be a prerequisite of another subject:</p>
  
  <ul>
  <li>A subject can have no prerequisites</li>
  <li>A subject can have 1 prerequisite</li>
  <li>A subject can have 2 prerequisites</li>
  </ul>
  
  <p>Since there are subjects with 2 prerequisites, I made the <code>prerequisite_1</code> and the <code>prerequisite_2</code> columns FKs pointing to the <code>subject_code</code> column:</p>
  
  <pre><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 &gt;= 0 AND no_of_units &lt; 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;
  </code></pre>
  
  <p>I tried the code on SQL Fiddle and the table is not being created.</p>
  
  <p>How do I create a recursive table with 1 PK column and 2 FK columns pointing back to the table's own PK?</p>