CODEWITHSUNDEEP
mysqlsql
Ajay Kulkarni
Ajay Kulkarni

Reputation: 3039

Getting error while altering table in MySQL

I wrote a sql file to create a Database and add tables in that database.
Here is the code: 

CREATE SCHEMA `employee_details`;
CREATE TABLE `employee_details`.`employee` (
    `emp_id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45),
    `dob` DATE,
    `joiningdate` DATE,
    `designation_id` INT,
    `gender` VARCHAR(45),
    `address` VARCHAR(90),
    `manager_id` INT,
    PRIMARY KEY (`emp_id`)
);

CREATE TABLE `employee_details`.`designation` (
    `designation_id` INT NOT NULL AUTO_INCREMENT,
    `designation` VARCHAR(45),
    `dept_id` INT,
    `salary` FLOAT,
    PRIMARY KEY (`designation_id`)
);

CREATE TABLE `employee_details`.`department` (
    `dept_id` integer NOT NULL AUTO_INCREMENT,
    `dept_name` VARCHAR(45),
    PRIMARY KEY (`dept_id`)
);

CREATE TABLE `employee_details`.`rating` (
    `emp_id` INT,
    `emp_rating` INT,
    `add_perks` FLOAT,
    `experience` INT
);

CREATE TABLE `employee_details`.`project_assign` (
    `emp_id` INT,
    `project_id` INT
);

CREATE TABLE `employee_details`.`project` (
    `project_id` INT NOT NULL AUTO_INCREMENT,
    `project_name` VARCHAR(45),
    `project_dept` INT,
    PRIMARY KEY (`project_id`)
);

ALTER TABLE employee 
ADD FOREIGN KEY (emp_id) REFERENCES rating(emp_id);

ALTER TABLE employee 
ADD FOREIGN KEY (emp_id) REFERENCES project_assign(emp_id);

ALTER TABLE employee 
ADD FOREIGN KEY (emp_id) REFERENCES employee(manager_id);

ALTER TABLE designation 
ADD FOREIGN KEY (designation_id) REFERENCES employee(designation_id);

ALTER TABLE designation 
ADD FOREIGN KEY (dept_id) REFERENCES department(dept_id);

ALTER TABLE department 
ADD FOREIGN KEY (dept_id) REFERENCES project(project_dept);

ALTER TABLE project 
ADD FOREIGN KEY (project_id) REFERENCES project_assign(project_id);

When I execute that sql file, I get below error:

15:16:50 ALTER TABLE employee ADD FOREIGN KEY (emp_id) REFERENCES rating(emp_id) Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'employee_ibfk_1' in the referenced table 'rating' 0.000 sec

I checked data types of emp_id in employee and rating. They are same. How can I fix this error?

Upvotes: 0

Views: 585

Answers (1)

Barmar
Barmar

Reputation: 781004

All your foreign key relationships are backwards. It should be:

ALTER TABLE rating
ADD FOREIGN KEY (emp_id) REFERENCES employee(emp_id);

and similar for all the other foreign keys.

Upvotes: 2

Related Questions