org.postgresql.util.PSQLException: ERROR: update or delete on table "skills" violates foreign key constraint on table "employee_has"

user_12208032 0
Viewed 208

I am developing a CRUD application using Angular, SpringBoot and PostgreSQL. There I have two tables named "skills" and "employees" in the database. The table created by joining both "skills" and "employee" tables is "employee_has" table. They are mapped using Many-to-Many relationship. An employee can have many skills. A skill can have many employees.

I need the functionality as when I delete a skill in the "skill" table, it should be removed from employees' who have that skill. But When I delete a skill, it does not getting deleted form the skill table and the relationship does not get deleted in the "employee_has" table and gives the below error.

org.postgresql.util.PSQLException: ERROR: update or delete on table "skills" violates foreign key constraint "fkoq05nk3xfqd4rl68fdpt17vvc" on table "employee_has"
  Detail: Key (id)=(5) is still referenced from table "employee_has".

Here is my code part for Skill model in the backend.

public class Skill {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @NotNull
    @NaturalId
    private String skill_name;

    @JsonIgnore
    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            },
            mappedBy = "skills")
    private List<Employee> employees = new ArrayList<>();

Here is my code part for Employee model in the backend.

    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "employee_has",
            joinColumns = { @JoinColumn(name = "emp_id") },
            inverseJoinColumns = { @JoinColumn(name = "skill_id") })
    private List<Skill> skills = new ArrayList<>();

Please help me to solve this issue.

spring-boot many-to-many cascade postgresql
1 Answers

This error occurs because you are trying to delete a "skill" that is a reference to the join table ("employee_has"). This can fix by the database side.

Instead of using, PostgreSQL create script

CREATE TABLE IF NOT EXISTS public.employee_has
(
    employee_id bigint NOT NULL,
    skill_id bigint NOT NULL,
    CONSTRAINT employee_has_pkey PRIMARY KEY (employee_id, skill_id),
    CONSTRAINT fkam2psf41jwoy33ge3uvxep8tl FOREIGN KEY (skill_id)
        REFERENCES public.skill (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fkkd8xx37dlmjryoas0d91hri6c FOREIGN KEY (employee_id)
        REFERENCES public.employee (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

Use this- Replacing "ON DELETE NO ACTION" to "ON DELETE CASCADE", Fix it by replacing No Action to Cascade

CREATE TABLE IF NOT EXISTS public.employee_has
(
    employee_id bigint NOT NULL,
    skill_id bigint NOT NULL,
    CONSTRAINT employee_has_pkey PRIMARY KEY (employee_id, skill_id),
    CONSTRAINT fkam2psf41jwoy33ge3uvxep8tl FOREIGN KEY (skill_id)
        REFERENCES public.skill (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fkkd8xx37dlmjryoas0d91hri6c FOREIGN KEY (employee_id)
        REFERENCES public.employee (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)
user_76206950
Related