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

user_16460180 0
Viewed 64

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.

mysql sql
3 Answers

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.

user_11440350

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.

user_58624850

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.

Admin1
Related