MySQL database cannot add foreign key
I am having a problem adding foreign key constraints to my MySQL database. For example, in this Register table (an attendance register table for recording student 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)
);
Device 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, fk_course_code works with course (course_code), which is VARCHAR (4), so I'm wondering if it is possible that it doesn't like this using auto_incremented Primary Key as foreign key?
Edit
I am getting error code # 1215 - Can't add foreign key constraint
Any help would be greatly appreciated!
source to share
Both the primary keys in the Student and Unit table are set up with ZEROFILL, but the columns in the Register table that refer to them are not. The attributes for foreign key columns must exactly match the columns they refer to in the external tables.
I recommend that you modify the creation of the Register 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 that I suggest, but these are beyond the scope of the posted question.
source to share