Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'employees_ibfk_1' in the referenced table 'departments' 0.000 sec

Data Science
1

Hello everyone!
I was trying to create those 2 table on my database, and this Error Message keeps showing, I don’t know how to solve it. Could someone help me?

CREATE TABLE departments (
department_id DECIMAL(4,0) NOT NULL DEFAULT 0 PRIMARY KEY,
department_name VARCHAR(30) NOT NULL,
manager_id DECIMAL(6,0) NOT NULL DEFAULT 0,
location_id DECIMAL(4,0) NOT NULL,
UNIQUE (department_id, manager_id)
);
CREATE TABLE employees (
employee_id VARCHAR(10) UNIQUE NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70),
phone_number VARCHAR(15),
hire_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
salary DECIMAL(8,2) NOT NULL,
comission DECIMAL(2,2),
manager_id DECIMAL(6,0) NOT NULL DEFAULT 0,
department_id DECIMAL(4,0) NOT NULL DEFAULT 0,
FOREIGN KEY (manager_id) REFERENCES departments(manager_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ‘employees_ibfk_1’ in the referenced table ‘departments’

Thank you very much in advance for your help.

2

Hello mate, did you find a solution to your problem ? I have the same error, I don’t know what ‘missing index’ means. Thanks in advance.

3

For foreign key relationship, the parent table column on which you are creating relation must be unique or primary and they must have the same datatype and size also

manager_id in departments table is not unique.

4

1.both table should have the same data type and size also.
2.add primary key or unique in the references table value.

Alter table xxx
change (old column name) (new column name) datatype primary key
or
Alter table xxx
change (old column name) (new column name) datatype unique