Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'projects_ibfk_1' in the referenced table 'employees'

Question

CREATE DATABASE employeeDB;
USE employeeDB;

CREATE TABLE employees(
    employeeid NUMERIC(9),
    firstname VARCHAR(10),
    lastname VARCHAR(20),
    deptCode CHAR(5),
    salary NUMERIC(9, 2),
    PRIMARY KEY (employeeid)
);

CREATE TABLE projects(
    projectid CHAR(8),
    deptcode CHAR(5),
    description VARCHAR(200),
    startdate DATE,
    stopdate DATE,
    revenue NUMERIC(12, 2),
    PRIMARY KEY (projectid),
    FOREIGN KEY (deptcode) REFERENCES employees(deptCode)
);

CREATE TABLE departments(
    code CHAR(5),
    name VARCHAR(5),
    managerid NUMERIC(9),
    subdeptof CHAR(5),
    PRIMARY KEY (code),
    FOREIGN KEY (managerid) REFERENCES employees(employeeid),
    FOREIGN KEY (subdeptof) REFERENCES projects(deptcode)
);

ALTER TABLE employees ADD FOREIGN KEY (deptCode) REFERENCES projects(deptcode);

Something wrong at the line CREATE TABLE projects(...). When I run the code in MySQL it give the Error Code 1822. What is the problem ? Any expert can help ?

Answer 1

You cannot create a foreign key with a non-primary key, and if you really want to create a foreign key to a non-primary key (column), the column must be indexed with a unique constraint on it. So either create a unique constraint on deptCode column, reference by already existing primary key, or change the primary key.