MySQL won't let me add multiple foreign keys
<p>So I'm working on a project in school in which we use MySQL to make a database and complete various tasks about school subjects and exam boards. One of my questions was to:</p>

<p>"Create and populate a third table called <em>entries</em>, again using query scripts. This table should contain foreign keys to allow sensible links to be made with the other two tables, together with the dates of each exam."</p>

<p>When carrying out this task I tried this code out</p>

<pre><code>CREATE TABLE IF NOT EXISTS entries(

subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

subject_name VARCHAR(20) NOT NULL,

level_of_entry VARCHAR(10) NOT NULL,

exam_board VARCHAR(10) NOT NULL,

date_of_exam DATETIME NOT NULL,

PRIMARY KEY (date_of_exam),

FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)

);
</code></pre>

<p>And that worked fine, but when I tried this:</p>

<pre><code>CREATE TABLE IF NOT EXISTS entries(

subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

subject_name VARCHAR(20) NOT NULL,

level_of_entry VARCHAR(10) NOT NULL,

exam_board VARCHAR(10) NOT NULL,

date_of_exam DATETIME NOT NULL,

PRIMARY KEY (date_of_exam),

FOREIGN KEY (subject_id) REFERENCES subjects(subject_id),

FOREIGN KEY (subject_name) REFERENCES subjects(subject_name)

);
</code></pre>

<p>It threw up and error message stating:</p>

<pre><code>"ERROR 1215 (HY000): Cannot add foreign key constraint"
</code></pre>

<p>Any clues on how to add multiple foreign key statements without it causing an error. Also I did try using the ALTER TABLE function and that didn't work,</p>

<p>Many thanks</p>

<p>Andrew.</p>