Applying and approval of leaves

[cyber-sparky]

SQL File

create database leavemanagement;
use leavemanagement;

create table employee(
    id int not null auto_increment,
    name varchar(100) not null,
    email varchar(100) not null unique,
    password varchar(250) not null,
    date_of_joining timestamp default current_timestamp on update current_timestamp,
    is_active boolean not null default '1',
    date_of_relieving date null,
    primary key(id)
);

create table role(
    id int not null auto_increment primary key,
    name varchar(200) not null unique
);

create table employee_role_details(
    id int not null auto_increment primary key,
    employee_id int not null,
    role_id int not null,
    reporting_manager_id int,
    foreign key(employee_id) references employee(id),
    foreign key(role_id) references role(id),
    foreign key(reporting_manager_id) references role(id)
);

CREATE TABLE leave_types(
    id int not null auto_increment primary key,
    name varchar(2) not null,
    description varchar(20) not null
);

create table employee_leave_balance(
    id int not null auto_increment primary key,
    employee_id int not null,
    leave_type varchar(2) not null,
    no_of_days int not null,
    foreign key(employee_id) references employee(id),
    foreign key(leave_type) references leave_types(name)
);

I'm facing an issue while creating a table. When I try to create the employee_leave_balance table, I receive an error message: Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'employee_leave_balance_ibfk_2' in the referenced table 'leave_types' 0.000 sec

Could you please provide some guidance to resolve this issue?

[barathcharm]

I think, foreign key is added only if the referenced column in another table is a primary key or Unique. So make the name colum in the leave_types as UNIQUE and check whether that works.

[cyber-sparky]

Thanks @barathcharm, its works fine. Thank you for the help to resolve the problem. So far this is issue is resolved I'm closing this issue.