Duplicate key value on saving child entities

Question

When i'm trying to save an entitiy (Role) of ManyToMany relation, id of child entity is generated incorrectly, and i'm receiving DataIntegrityViolationException.

Part of saving parent entity with child:

public Organization create(Organization organization) {
        Organization created = save(organization);
        Role role = new Role();
        role.setCode("test");
        created.getRoles().add(role);
        return save(created);
    }

Hibernate debug and exception:

Hibernate: insert into organization (company_code, full_legal_name, id) values (?, ?, ?)
Hibernate: insert into role (code, description, read_only, reserved) values (?, ?, ?, ?)
2021-12-23 10:34:50.459  WARN 13464 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23505
2021-12-23 10:34:50.459 ERROR 13464 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: duplicate key value violates unique constraint "role_pkey"
  Detail: Key (id)=(15) already exists.

Parent entity:

@Entity        
public class Organization {

    @Id
    Long id;

    @ManyToMany(cascade = {CascadeType.ALL}, fetch= FetchType.EAGER)
    @JoinTable(name = "organization_role",
            joinColumns = @JoinColumn(name = "organization_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id"))
    Set<Role> roles = new HashSet<>();
    }

Role entity:

@Entity
public class Role implements Serializable {

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

Liquibase tables: Role:

CREATE TABLE role
(
    id          SERIAL                                    NOT NULL,
    code        VARCHAR(32)                               NOT NULL,
    CONSTRAINT role_pkey PRIMARY KEY (id)
);

Organization:

CREATE TABLE organization (
    id bigint NOT NULL,
    CONSTRAINT organization_pkey PRIMARY KEY (id)
);

Organization-Role:

CREATE TABLE organization_role (
    role_id INTEGER  NOT NULL,
    organization_id BIGINT NOT NULL,
    CONSTRAINT fk_organization_role_role FOREIGN KEY (role_id) REFERENCES role(id),
    CONSTRAINT fk_organization_role_organization FOREIGN KEY (organization_id) REFERENCES organization(id),
    CONSTRAINT user_organization_pkey PRIMARY KEY (role_id, organization_id)
);

Answer 1

This doesn't have to do with MTM. Such situation is possible only if your PK sequence (serial) got behind some how, and now it generates keys that already exist in the table. Couple of ideas why this might have happened:

• You recreated the sequence associated with the serial, so it starts from 1 again. But there weren't records with IDs up until 15, so first 14 inserts were successful.
• You inserted records with an explicit ID specified instead of using serial functionality. Here is how you can reproduce the problem (2nd insert causes problems down the road):

drop table if exists org;
create table org (
    id serial,
    name text,
    constraint org_pk primary key (id)
);

insert into org(name) values('name1'); -- works fine
insert into org(id, name) values(2, 'name2'); -- we don't use the sequence
insert into org(name) values('name3');-- sequence generates 2 again and insert fails

You need to find who screws up the sequence/who inserts rows with the ID specified. And update that place:

1. Either don't specify the IDs at all - serial will do this for you
2. Or access sequence explicitly in the insert statement:

insert into org(id, name) values(nextval('org_id_seq'), 'name2');

3. Or update the sequence with setval() after you inserted the rows.