CODEWITHSUNDEEP
mysqlsql
Devadrita Bose
Devadrita Bose

Reputation: 165

ERROR 1215 (HY000): Cannot add foreign key constraint error

This is what I have written:

create table employee (
emp_id int, 
emp_first_name varchar(20), 
emp_last_name varchar(20), 
dept_id int,
email varchar(255),
primary key(emp_id, dept_id)
);

create table department(
    dept_id int primary key,
    dept_name varchar(255),
    gm_no int,
    foreign key(dept_id) references employee(dept_id)
);

but when I try to create the department table, it gives me the error(ERROR 1215 (HY000): Cannot add foreign key constraint)

all help is appreciated. thank you.

Upvotes: 1

Views: 100

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

You have the logic backwards. The dept_id is defined in the department table as the primary key. The foreign key constraint belongs in the tables that reference the department:

create table department(
    dept_id int primary key,
    dept_name varchar(255),
    gm_no int
);

create table employee (
    emp_id int, 
    emp_first_name varchar(20), 
    emp_last_name varchar(20), 
    dept_id int,
    email varchar(255),
    primary key(emp_id, dept_id)
    foreign key (dept_id) references department (dept_id)
);

Note that this data structure seems suspicious. I would expect a company called employee to have a unique id for empid. Period.

Employees are obvious in departments, but that could change over time. You would then want another table, say employeeDepartment that captured the changing relationship over time.

Your data model, for instance, would get confusing if an employee starts in department A, switches to B, and then returns to A.

Upvotes: 2

user16421015
user16421015

Reputation:

As @Sharofiddin said:

create table employee (
emp_id int, 
emp_first_name varchar(20), 
emp_last_name varchar(20), 
dept_id int,
email varchar(255),
primary key(emp_id),
UNIQUE KEY (dept_id) /* add this line and run the query again */
);

create table department(
    dept_id int primary key,
    dept_name varchar(255),
    gm_no int,
    foreign key(dept_id) references employee(dept_id)
);

Thank you.

Upvotes: 1

Sharofiddin
Sharofiddin

Reputation: 393

The dept_id in the employee table is not key of any type. Make the dept_in column a KEY, UNIQUE KEY, or PRIMARY KEY on the employee. In your situation, just making it UNIQUE KEY is enough.

Upvotes: 1

Related Questions