-1

I am currently working through my MySQL task and have come across a small hiccup whilst using foreign keys. The problem is as follows:

CREATE TABLE IF NOT EXISTS entries(
student_id INT(10) UNSIGNED NOT NULL,
subject_name INT(10) UNSIGNED NOT NULL,
exam_date VARCHAR(20) NOT NULL,
PRIMARY KEY (exam_date),
FOREIGN KEY (student_id) REFERENCES  student (student_id),
FOREIGN KEY (subject_name) REFERENCES subject (subject_name));

After i enter this code Error 1215 pops up saying that i cannot add foreign key constraints. The error is shown below:

ERROR 1215 (HY000): Cannot add foreign key constraint

The table i am trying to link the foreign key is this:

CREATE TABLE IF NOT EXISTS subjects(
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
subject_name VARCHAR(20) NOT NULL,
level_of_entry VARCHAR(20),
exam_board VARCHAR(40) NOT NULL,
PRIMARY KEY(student_id));

I have no issues with this table and simply wonder how i could solve my dilemma?

gen_Eric
  • 223,194
  • 41
  • 299
  • 337
LordZiggy
  • 17
  • 5
  • 1
    First create both table student and subject, then create entries table. And be care about typo (FOREIGN KEY (subject_name) REFERENCES subject (subject_name));) Here you are adding student and created table students. – Rakesh Kumar Jul 18 '16 at 13:35

2 Answers2

2

You have subject_name INT(10) in one table and subject_name VARCHAR(20) in the other Doublecheck types, when creating foreign keys in MySQL.

Varrah
  • 80
  • 1
  • 9
1

entries table subject_name is of datatype int where as in subjects table the datatype of subject_name is varchar. in order to add a foreign key constraint the 2 datatypes must match

apomene
  • 14,282
  • 9
  • 46
  • 72