Cannot delete or update a parent row: spring problem without cascade

Question

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

Its would be better to show us a `SHOW CREATE TABLE tablename` for each relevant, related table. Then we can see what has been done and what has been missed. — RiggsFolly, Feb 19 '22 at 17:08
I updated it. I need a way to detach the employee from department and designation . — mikebrucks, Feb 19 '22 at 17:15
https://stackoverflow.com/questions/1027656/what-is-mysqls-default-on-delete-behavior Look at the accepted answer on that question — RiggsFolly, Feb 19 '22 at 17:26
Thank you for the link, I check it, but I can't see an example for spring — mikebrucks, Feb 20 '22 at 09:51
@RiggsFolly I can quite understand what they mean in this question What I need to do to make another table witch will hold relation for department and designation as departmen_designation ? — mikebrucks, Feb 20 '22 at 10:53

score 0 · Accepted Answer · answered Feb 21 '22 at 09:34

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

Cannot delete or update a parent row: spring problem without cascade

1 Answers1