java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (hrms.employees, CONSTRAINT FKe4i9i8vu1j96m71g4v98kqirb FOREIGN KEY (designation_id) REFERENCES designations (id))

I just land on this problem, I try to delete an entity, but the entity have a relation with another entity and another entity have another relation to imagine the picture this is the tables

How to detach employee and designation from department when I want to delete department. I can delete designations in the code, but I don't want to delete the employee with foreign key associated to the department and designation.

CREATE TABLE `departments` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(150) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `designations` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `department_name` varchar(40) DEFAULT NULL,
    `name` varchar(140) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `employees` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `address` varchar(255) NOT NULL,
    `dob` varchar(255) DEFAULT NULL,
    `email` varchar(35) NOT NULL,
    `employee_number` varchar(255) NOT NULL,
    `first_name` varchar(40) NOT NULL,
    `full_name` varchar(100) NOT NULL,
    `gender` varchar(255) DEFAULT NULL,
    `join_date` varchar(255) NOT NULL,
    `last_name` varchar(40) NOT NULL,
    `password` varchar(40) NOT NULL,
    `phone_number` varchar(255) NOT NULL,
    `username` varchar(255) NOT NULL,
    `department_id` bigint DEFAULT NULL,
    `designation_id` bigint DEFAULT NULL,
    `avatar_image` varchar(64) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FKgy4qe3dnqrm3ktd76sxp7n4c2` (`department_id`),
    KEY `FKe4i9i8vu1j96m71g4v98kqirb` (`designation_id`),
    CONSTRAINT `FKe4i9i8vu1j96m71g4v98kqirb` 
        FOREIGN KEY (`designation_id`) 
        REFERENCES `designations` (`id`),
    CONSTRAINT `FKgy4qe3dnqrm3ktd76sxp7n4c2` 
        FOREIGN KEY (`department_id`) 
        REFERENCES `departments` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CodePudding user response：

Spring is completely irrelevant to your question. Its all about the MySQL database and the way you have defined the tables in your DDL.

You cannot delete a Parent row if a Child row exists on your database because of how you defined that constraint.

There is a ON DELETE .... syntax which tells MySQL what to do when a parent row is deleted, by default MySQL will reject the delete, you can change this in a number of ways, as specified in the MySQL manual, of all the odd places.

In your case as you want to NOT DELETE the Employee when you delete the Department, and you have the column

`department_id` bigint DEFAULT NULL,

defined as DEFAULT NULL then change your CONSTRAINT as below

CONSTRAINT `FKgy4qe3dnqrm3ktd76sxp7n4c2` 
        FOREIGN KEY (`department_id`) 
        REFERENCES `departments` (`id`)
    ON DELETE SET NULL

You could of course also do

CONSTRAINT `FKgy4qe3dnqrm3ktd76sxp7n4c2` 
        FOREIGN KEY (`department_id`) 
        REFERENCES `departments` (`id`)
    ON DELETE SET DEFAULT

both would do the same thing in this case as your default is NULL for that column