CODEWITHSUNDEEP
mysqlforeign-keys
TryPenta
TryPenta

Reputation: 45

How to DELETE in mysql without dropping primary and foreign constraints

I want to delete a record inside employee_id for example 

DELETE FROM EMPLOYEE WHERE employee_id = 114;


CREATE TABLE EMPLOYEE(
employee_id    DECIMAL(6)     NOT NULL,
first_name     VARCHAR(20)  NOT NULL,
last_name      VARCHAR(25)  NOT NULL,
email          VARCHAR(25)      NULL,
phone_number   VARCHAR(20)      NULL,
hire_date      DATE     NOT NULL,
job_title      VARCHAR(35)  NOT NULL,
salary         DECIMAL(8,2)     NULL,
commission_pct DECIMAL(2,2)     NULL,
supervisor_id     DECIMAL(6)        NULL,
department_name VARCHAR(30)     NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY(employee_id),
CONSTRAINT EMPLOYEE_CK1 UNIQUE(email),
CONSTRAINT EMPLOYEE_CK2 UNIQUE(phone_number),
CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY(department_name)
       REFERENCES DEPARTMENT(department_name),
CONSTRAINT EMPLOYEE_FK2 FOREIGN KEY(supervisor_id)
       REFERENCES EMPLOYEE(employee_id),
CONSTRAINT EMPLOYEE_FK3 FOREIGN KEY(job_title)
       REFERENCES JOB(job_title),
CONSTRAINT EMPLOYEE_CH1 CHECK (salary > 0) );

ALTER TABLE DEPARTMENT
ADD ( CONSTRAINT DEPARTMENT_FK2 FOREIGN KEY(manager_id)
             REFERENCES EMPLOYEE(employee_id) );

This is the error message i got

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Any idea how to delete the record without altering or dropping constraints?

Upvotes: 1

Views: 48

Answers (3)

Rick James
Rick James

Reputation: 142560

You could change to a cascading delete. But then firing the supervisor also fires all his reports.

You could abandon FOREIGN KEYs because of the hassle. But then you lose the benefits.

You should first change all the employees reporting to him to have them point somewhere else? Maybe NULL? Then delete the supervisor.

Look at it this way. The FK caught you trying to mess up the integrity of the database. Either the integrity check is wrong, or the process (deleting the supervisor) was wrong.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 563011

To delete the employee record without dropping the foreign key, you should first UPDATE any rows in your Department table that reference that employee_id.

UPDATE Department SET manager_id = ? WHERE manager_id = ?;

Also update all rows in Employee that reference the one you want to delete as their supervisor_id.

UPDATE Employee SET supervisor_id = ? WHERE supervisor_id = ?;

Once there are no rows in Department or Employee that reference that specific employee_id, then you can DELETE the row in the Employee table.

Upvotes: 2

James
James

Reputation: 1829

You can temporarily disable then enable by using the below statement.

To Disable:

set foreign_key_checks = 0;

TO Enable:

set foreign_key_checks = 1;

Upvotes: 0

Related Questions