Can't add foreign key constraint, the foreign key referred to is not null and has the same type

Question

Here is a small part of my SQL query, the foreign key that I'm referring to is not null and has the same type where did I go wrong?

Create table `Time` (
`Year` year not null,
`Semester` char(2) not null,
primary key (`Year`, `Semester`)
);

Create table `Allocation` (
`Student_ID` int unsigned not null,
`Time_Year` year not null,
`Time_Semester` char(2) not null,
primary key (`Timeslot_Year`, `Timeslot_Semester`, `Student_ID`),
constraint fk_year_allocation foreign key (`Time_Year`) references `Time`(`Year`),
constraint fk_semester_allocation foreign key (`Time_Semester`) references `Time`(`Semester`),
);

Answer 1

Apart from fixing the typos such as the ending comma in the second table and the

primary key (`Timeslot_Year`, `Timeslot_Semester`),

should be

primary key (`Time_Year`, `Time_Semester`),

You need to add an index on the Semester column .

Why constraint fk_year_allocation foreign key (Time_Year) references Time(Year) is working ?

From primary key (Year, Semester) only the left one is used.

See Multiple-Column Indexes

See example

See Conditions and Restrictions when dealing with foreign keys.