MySQL Database Can't Add Foreign Key

Question

I'm having a problem adding some foreign key constraints to my MySQL database. For example, in this Register table (Attendance Register table for recording students attendance) I would like to have fk_unit_id and fk_student_id as foreign keys referencing the primary keys in Unit and Student.

Register Table:

CREATE TABLE IF NOT EXISTS register 
(
fk_unit_id INT(4) NOT NULL,
fk_student_id INT(4) NOT NULL,
register_date DATE NOT NULL,
attendance CHAR(1) NOT NULL,
PRIMARY KEY (fk_unit_id, fk_student_id, register_date),
FOREIGN KEY (fk_unit_id) REFERENCES unit(unit_id),
FOREIGN KEY (fk_student_id) REFERENCES student(student_id)
);

Student table:

CREATE TABLE IF NOT EXISTS student
(
student_id INT(4) ZEROFILL NOT NULL AUTO_INCREMENT,
student_first_name VARCHAR(20) NOT NULL,
student_surname VARCHAR(20) NOT NULL,
student_dob DATE NOT NULL,
student_contact_no VARCHAR(11) NOT NULL,
student_email VARCHAR(30) NOT NULL,
student_address VARCHAR(50) NOT NULL,
student_image_name VARCHAR(30) NOT NULL,
PRIMARY KEY (student_id)
);

Unit Table:

CREATE TABLE IF NOT EXISTS unit
(
unit_id INT(4) ZEROFILL NOT NULL AUTO_INCREMENT,
unit_name VARCHAR(50) NOT NULL,
unit_day VARCHAR(10) NOT NULL,
unit_time VARCHAR (10) NOT NULL,
fk_course_code VARCHAR(4) NOT NULL,
fk_lecturer_id INT(4) NOT NULL,
PRIMARY KEY (unit_id),
FOREIGN KEY (fk_course_code) REFERENCES course(course_code),
FOREIGN KEY (fk_lecturer_id) REFERENCES lecturer(lecturer_id)
);

For the record, the fk_course_code does work with Course(course_code) which is a VARCHAR(4), so I'm wondering if perhaps it doesn't like using an auto_incremented Primary Key as a Foreign Key??

Edit

I am getting error code #1215 - Cannot add foreign key restraint

Any help will be much appreciated!

mysql generally doesn't care at all what type a field is. you can use pretty much any field as a foreign key, but the field definition in both tables has to be pretty much identical. e.g. you can link `unsigned int` to `unsigned int`, but you can't link `int` to `bigint` or `varchar` to `text`. about the only exception is that the field in a child table can be nullable, even if the parent field is `not null`. — Marc B, Apr 23 '15 at 20:54
Can a student register for same unit multiple time? If no then I don't think this is correct: `PRIMARY KEY (fk_unit_id, fk_student_id, register_date),` you just need `PRIMARY KEY (fk_unit_id, fk_student_id),` in that case. — Maximus2012, Apr 23 '15 at 20:54
How do you know if the foreign key is not working? Is there any error message that you're getting ? — Maximus2012, Apr 23 '15 at 20:56
You also probably don't need course information in student table since unit table takes care of that and then the registration table matches a student with the course via unit. — Maximus2012, Apr 23 '15 at 20:59
The MySQL documentation on Foreign Keys uses indexing of columns before using them as Foreign Keys, like this: `INDEX (fk_course_code), FOREIGN KEY (fk_course_code) REFERENCES course(course_code)` I don't know if that's the source of your problem though. — The 42nd Doctor, Apr 23 '15 at 21:04
@Maximus2012 I get error #1215 when I try to add the foreign key constraints and you're right about the link between course and student - I've taken it out of my code. I should say the register is an attendance register to mark students in for lessons so there will be multiple records for students in the attendance register table depending on the date and which unit they are attending... Hope this clarifies. I will edit post. — Kittyinsocks, Apr 23 '15 at 21:05
Try specifying encoding when creating table, e.g. `CREATE TABLE t1 ( ... ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;` it might help — Misa Lazovic, Apr 23 '15 at 21:06
http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint — Maximus2012, Apr 23 '15 at 21:07
Could `course_code` or `lecturer_id` fields in Unit table be causing `FOREIGN KEY VIOLATION` error ? — Maximus2012, Apr 23 '15 at 21:09
How are you trying to add the foreign Keys? If you are pasting the create tables in your question, that won't work because the unit and student table don't exist before you try to FK to them. — Dan, Apr 23 '15 at 21:14
The unit and student table have already been added to my MySQL database so that shouldn't be a problem... All are INT (4) with the exception of course_code, but that one has worked fine. I'm still thinking it's something to do with the ZEROFILL AUTO_INCREMENT. — Kittyinsocks, Apr 23 '15 at 21:21
I think I got the solution!! I put fk_lecturer_id and fk_student_id and fk_unit_id as INT (4) ZEROFILL and this seemed to work. So it needed me to match it up with the ZEROFILL on the Primary Keys.. — Kittyinsocks, Apr 23 '15 at 21:26
Just like Mark said in the first comment - field you reference and field that references it **must be the same in their definition** - therefore yes, you either need zerofill on both, or no zerofill on both. — N.B., Apr 23 '15 at 22:41

Jeff Cashion PhD · Answer 1 · 2015-04-24T13:50:17.623

Both of the primary keys in the Student and Unit table are configured with ZEROFILL, but the columns in the Register table that refer to them are not. The attributes for foreign key columns must match exactly the columns they are referring to in the foreign tables.

I recommend you change the Register creation to as follows:

CREATE TABLE IF NOT EXISTS register 
(
    fk_unit_id INT(4) ZEROFILL NOT NULL,
    fk_student_id INT(4) ZEROFILL NOT NULL,
    register_date DATE NOT NULL,
    attendance CHAR(1) NOT NULL,
    PRIMARY KEY (fk_unit_id, fk_student_id, register_date),
    CONSTRAINT `c_fk_unit_id` FOREIGN KEY (fk_unit_id) REFERENCES unit(unit_id),
    CONSTRAINT `c_fk_student_id` FOREIGN KEY (fk_student_id) REFERENCES student(student_id)
);

There are other optimizations and changes I would suggest, but they are beyond the scope of the question posted.

MySQL Database Can't Add Foreign Key

1 Answers1