1
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 ?

emperor_c
  • 99
  • 2
  • 9
  • in `employees` table you have defined an primary key column `employeeid`. There is no primary key with `deptCode` column. And you are creating FK in `projects` table, that is connected with employees by `deptcode` column, instead table `projects` should be connected with `employees` by primary key column, which is: `employeeid` – Bartosz Olchowik May 24 '22 at 08:07
  • Does this answer your question? [Can a foreign key refer to a primary key in the same table?](https://stackoverflow.com/questions/18680680/can-a-foreign-key-refer-to-a-primary-key-in-the-same-table) – philipxy May 24 '22 at 08:48

1 Answers1

6

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.

Zephaniah Irvine
  • 389
  • 2
  • 12
Bartosz Olchowik
  • 1,129
  • 8
  • 22