0

I have researched thoroughly before asking this question including on this site.

I have a students table:

CREATE TABLE IF NOT EXISTS students(
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
middle_name VARCHAR(20),
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
password CHAR(40) NOT NULL,
reg_date DATETIME NOT NULL,
PRIMARY KEY (student_id),
UNIQUE (email));

I also have a subjects table:

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

I am now creating a table to link the above tables:

CREATE TABLE IF NOT EXISTS entries(
exam_date DATETIME NOT NULL,
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (subject_id) REFERENCES subjects (subject_id)
);

My problem is that when I try to declare the foreign keys in the third table called entries, I get an error stating that the subject_id foreign key is not in the table referenced. ERROR 1072 (42000) : Key column 'student_id' doesn't exist in table, even though it is clearly contained inside the the students table and the same applies to 'subject_id' and the subject table. I am certain that my syntax for declaring the foreign keys is correct so I am unsure how to fix the problem. All help is appreciated. Thank you.

Improve this question
6
  • I think you forgot to add student_id and subject_id columns INSIDE entries table.. For now, entries table has only one column exam_date. You are trying to map a foreign key in this entries table on a column that does not exists, that is not defined Commented Nov 28, 2016 at 15:59
  • @Delphine So do I need to act as if I am creating the student_id and subject_id columns like in each of the two tables? Commented Nov 28, 2016 at 16:04
  • Exactly. A foreign key order does not create column. You have to create it and after apply foreign constraint. Be careful about datatype and lenght but, if you declare these two columns as you did forstudents and subjects, it would be perfect ! PS : I posted it as an answer Commented Nov 28, 2016 at 16:06
  • @Delphine Does this include auto_increment? Commented Nov 28, 2016 at 16:09
  • 1
    If its working now then consider accepting Delphine's answer. Commented Nov 28, 2016 at 16:31

1 Answer 1

Reset to default
1

You forgot to create these two columns before applying your foreign key constraints :

CREATE TABLE IF NOT EXISTS entries(
exam_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
subject_id INT UNSIGNED NOT NULL,
exam_date DATETIME NOT NULL,
PRIMARY KEY (exam_id),
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (subject_id) REFERENCES subjects (subject_id)
);

EDIT :

I advise you to add in every table a unique ID column (here : exam_id).

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

On which column ?
I am getting an error saying, 'Incorrect table definition; there can only be one auto column definition and it must be defined as a key'
I've just updated my answer, could you try with this ? (Delete entries table if it already exists)
I now get error 1215, cannot add foreign key constraint.
Delete all your tables and create again. Use Delphine's code for the last table. It's working.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.