LK Phuc Nguyen LK Phuc Nguyen - 6 months ago 20
MySQL Question

MySQL - errno 150: "Foreign key constraint is incorrectly formed"

I am using MySQL on my practice task in tutorial, yet I have a problem on creating a table with error "incorrect constraint".

Please have a look and suggest me how I can fix it. Thank you!

*A course-offering is a course offered in a specific semester
*Students will enrol in course-offerings, not courses
*Lecturer is assigned to course-offerings, not courses

CREATE TABLE `students` (
`student_id` char(8) NOT NULL,
`max_load` tinyint(4) NOT NULL,
PRIMARY KEY (`student_id`)
)
CREATE TABLE `courses` (
`course_code` char(8) NOT NULL,
`course_title` varchar(100) NOT NULL,
PRIMARY KEY (`course_code`)
)
CREATE TABLE `lecturers` (
`lecturer_id` char(8) NOT NULL,
`lecturer_name` varchar(50) NOT NULL,
PRIMARY KEY (`lecturer_id`)
)

CREATE TABLE `course_offerings` (
`course_code` char(8) NOT NULL,
`semester` tinyint(4) NOT NULL,
`year` year(4) NOT NULL,
`lecturer_id` char(8) NOT NULL,
PRIMARY KEY (`course_code`,`semester`,`year`),
FOREIGN KEY (`course_code`) REFERENCES `courses` (`course_code`),
FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`lecturer_id`)
)

CREATE TABLE `enrolment` (
`student_id` char(8) NOT NULL,
`course_code` char(8) NOT NULL,
`semester` tinyint(4) NOT NULL,
`year` year(4) NOT NULL,
PRIMARY KEY (`student_id`,`course_code`,`semester`,`year`),
FOREIGN KEY (`student_id`) REFERENCES students(`student_id`),
FOREIGN KEY (`course_code`) REFERENCES course_offerings(`course_code`),
FOREIGN KEY (`semester`) REFERENCES course_offerings(`semester`),
FOREIGN KEY (`year`) REFERENCES course_offerings(`year`)
)

Tin Tin
Answer

Are you trying to have composite foreign key on enrolment? If it's the case, the following may be what you want.

CREATE TABLE `enrolment` (
    `student_id` char(8) NOT NULL,
    `course_code` char(8) NOT NULL,
    `semester` tinyint(4) NOT NULL,
    `year` year(4) NOT NULL,
PRIMARY KEY (`student_id`,`course_code`,`semester`,`year`),
FOREIGN KEY (`student_id`) 
     REFERENCES students(`student_id`),
FOREIGN KEY (`course_code`, `semester`, `year`)  
     REFERENCES course_offerings(`course_code`, `semester`, `year`))

I'd suggest you to create an id column in course_offerings and make it primary key, keeping course_code, semester and year combined UNIQUE key. Use this id as a foreign key so you will need 1 column instead of 3 columns.

Comments