How to avoid duplicate insertions of "many" entity in one-to-many relationship when using a unique constraint in eclipselink/JPA

I am working on a large codebase using Spring MVC with EclipseLink 2.5.2 on a mysql database. The database and its structure are created directly, not through any code-first approach. My problem concerns 2 tables in a one-to-many relationship.

CREATE TABLE ROLE (
  ID BIGINT(20) PRIMARY KEY,
  -- OTHER FIELDS --
);

CREATE TABLE ROLE_DOMAIN (
  ID BIGINT(20) PRIMARY KEY,
  ROLE_ID BIGINT(20) NOT NULL,
  DOMAIN VARCHAR(255) NOT NULL
  -- OTHER FIELDS --
);

ALTER TABLE ROLE_DOMAIN ADD CONSTRAINT FK_ROLE_DOMAIN_ROLE_ID FOREIGN KEY (ROLE_ID) REFERENCES ROLE_BASE (ID) ON DELETE CASCADE;
ALTER TABLE ROLE_DOMAIN ADD CONSTRAINT UQ_ROLE_DOMAIN_ROLE_ID_DOMAIN UNIQUE (ROLE_ID, DOMAIN);

And in java, this is how I've got the two entities configured.

@Entity
public class Role {
  private Long id;
  private Set<RoleDomain> roleDomains = new HashSet<>();

  @Id
  @TableGenerator(name = "ROLE.ID", allocationSize = 1, initialValue = 1)
  @GeneratedValue(strategy = GenerationType.TABLE, generator = "ROLE.ID")
  public Long getID() {
    return this.id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
  @JoinColumn(name = "ROLE_ID", referencedColumnName = "ID", insertable = false, updatable = false)
  public Set<RoleDomain> getRoleDomains() {
    return roleDomains;
  }
    
  public void setRoleDomains(Set<RoleDomain> roleDomains) {
    this.roleDomains = roleDomains;
  }
}


@Entity
@Table(name = "ROLE_DOMAIN")
public class RoleDomain {
  private Long id;
  private Long roleId;
  private String domain;

  @Id
  @TableGenerator(name = "ROLE_DOMAIN.ID", allocationSize = 1, initialValue = 1)
  @GeneratedValue(strategy = GenerationType.TABLE, generator = "ROLE_DOMAIN.ID")
  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  @Column(name = "ROLE_ID", nullable = false)
  public Long getRoleId() {
    return roleId;
  }

  public void setRoleId(Long roleId) {
    this.roleId = roleId;
  }

  @Column(name = "DOMAIN", length = 255)
  public String getDomain() {
    return domain;
  }

  public void setDomain(String domain) {
    this.domain = domain;
  }
}

Say that in this table structure, I already have a record in ROLE and a record in ROLE_DOMAIN that references it, translating to a Role object named myRole containing the RoleDomain in roleDomains.

Now, when I add a new RoleDomain and save using a spring data repository like this:

myRole.add(new RoleDomain("some string"));
roleRepository.save(myRole);

I get an exception for a duplicate insert violating my unique constraint on ROLE_ID and DOMAIN in the database.

[EL Warning]: 2020-10-22 14:53:22.405--UnitOfWork(994047815)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.8.v20190620-d6443d8be7): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '198732-some string' for key 'UQ_ROLE_DOMAIN_ROLE_ID_DOMAIN'
Error Code: 1062
Call: INSERT INTO ROLE_DOMAIN (ID, DOMAIN, ROLE_ID) VALUES (?, ?, ?)
bind => [27, some other string, 198732]

The weirdest thing about this problem is that if I remove the unique constraint from the database (Note: keeping the java annotation configuration EXACTLY the same. Literally just "DROP CONSTRAINT..." in the db) then the save call works just fine. It doesn't create duplicates in ROLE_DOMAIN. It does exactly what it's supposed to, just adds the new record to ROLE_DOMAIN.

I don't understand how a unique constraint in the db would cause eclipselink to act this inconsistently. Do I have something configured wrongly? Thanks.

EDIT:
I have just now tried replacing the @Table annotation on the RoleDomain class with this:

@Table(name = "ROLE_DOMAIN", uniqueConstraints =
  @UniqueConstraint(columnNames = {"ROLE_ID", "DOMAIN"}))

It didn't change anything.

The issue with your constraint is that EclipseLink orders statements for batching, putting deletes last - this is to give you a chance to clean up other constraints, to modify existing rows before rows get deleted. This can be changed so that deletes are issued first using the setShouldPerformDeletesFirst method on the UnitOfWork. As this is native api, you will have to unwrap the EntityManager to get at it, using

em.unwrap(org.eclipse.persistence.sessions.UnitOfWork.class)

if you are in a transaction. This will only be set for the UnitOfWork within this EntityManager, so if you need it everywhere always, you will want to have a session listener with your own session adaptor class to listen for postAcquireUnitOfWork and call setShouldPerformDeletesFirst on it.