Cannot persist unidirectional OneToMany parent->children

[oramattkosem]

Describe the bug
I cannot persist a parent+children with a unidirectional OneToMany relationship if the GenerationType is IDENTITY.

jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation ; SYS_PK_10095 table: TESTINGCHILDENTITY
Error Code: -104
Call: INSERT INTO TESTINGCHILDENTITY (PARENT_ID, ID, SOMEFIELD) VALUES (?, ?, ?)
	bind => [1, 0, child2]
Query: InsertObjectQuery(test.TestingChildEntity@1d23ff23)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:967)
	at test.EntityTest.testConverter(EntityTest.java:30)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation ; SYS_PK_10095 table: TESTINGCHILDENTITY
Error Code: -104
Call: INSERT INTO TESTINGCHILDENTITY (PARENT_ID, ID, SOMEFIELD) VALUES (?, ?, ?)
	bind => [1, 0, child2]
Query: InsertObjectQuery(test.TestingChildEntity@1d23ff23)
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:334)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:919)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:981)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:569)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2048)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:311)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:280)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:266)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:436)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:193)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:209)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:514)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:86)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWrite(DatabaseQueryMechanism.java:271)
	at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:63)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:812)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:109)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:86)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3025)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1841)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1823)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1773)
	at org.eclipse.persistence.mappings.OneToManyMapping.postInsert(OneToManyMapping.java:1109)
	at org.eclipse.persistence.descriptors.DescriptorQueryManager.postInsert(DescriptorQueryManager.java:1016)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:523)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:86)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:97)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:326)
	at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:61)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:812)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:109)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:86)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3025)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1841)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1823)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1773)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:248)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:215)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:160)
	at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4335)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1515)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithPreBuiltChangeSet(UnitOfWorkImpl.java:1661)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.writeChanges(RepeatableWriteUnitOfWork.java:472)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:962)
	... 4 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation ; SYS_PK_10095 table: TESTINGCHILDENTITY
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:909)
	... 49 more
Caused by: org.hsqldb.HsqlException: integrity constraint violation: unique constraint or index violation ; SYS_PK_10095 table: TESTINGCHILDENTITY
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.Constraint.getException(Unknown Source)
	at org.hsqldb.index.IndexAVL.insert(Unknown Source)
	at org.hsqldb.persist.RowStoreAVL.indexRow(Unknown Source)
	at org.hsqldb.TransactionManagerMVCC.addInsertAction(Unknown Source)
	at org.hsqldb.Session.addInsertAction(Unknown Source)
	at org.hsqldb.Table.insertSingleRow(Unknown Source)
	at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)
	at org.hsqldb.StatementInsert.getResult(Unknown Source)
	at org.hsqldb.StatementDMQL.execute(Unknown Source)
	at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)
	... 52 more

To Reproduce
Define a persistence unit like this:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Persistence deployment descriptor for dev profile -->
<persistence xmlns="https://jakarta.ee/xml/ns/persistence" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd" version="3.0">
  <persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> 
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="eclipselink.logging.level" value="FINE"/>
      <property name="eclipselink.logging.level.sql" value="FINE"/>
      <property name="eclipselink.logging.parameters" value="true"/>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
      <property name="eclipselink.id-validation" value="NEGATIVE" />
      <property name="eclipselink.cache.shared.default" value="false"/>
      <property name="jakarta.persistence.jdbc.driver" value="org.hsqldb.jdbc.JDBCDriver" />
      <property name="jakarta.persistence.jdbc.url" value="jdbc:hsqldb:mem:tertiary;hsqldb.tx=mvcc" />
      <property name="jakarta.persistence.jdbc.user" value="sa" />
      <property name="jakarta.persistence.jdbc.password" value="" />
    </properties>
  </persistence-unit>
</persistence>

And a parent like this:

package test;

import java.util.List;

import jakarta.persistence.*;
import jakarta.validation.Valid;

@Entity
public class TestingEntity {
  private long id;
  private String someField;
  private List<TestingChildEntity> children;

  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  @OrderBy("id")
  @JoinColumn(foreignKey = @ForeignKey(name = "FK_CHILDREN", value = ConstraintMode.CONSTRAINT),
      name = "PARENT_ID", nullable = false, referencedColumnName = "ID", updatable = false)
  @Valid
  public List<TestingChildEntity> getChildren() {
    return children;
  }

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  public long getId() {
    return id;
  }

  @Column
  public String getSomeField() {
    return someField;
  }

  public void setChildren(List<TestingChildEntity> argChildren) {
    children = argChildren;
  }

  public void setId(long argId) {
    id = argId;
  }

  public void setSomeField(String argSomeField) {
    someField = argSomeField;
  }
}

And a child like this:

package test;

import jakarta.persistence.*;

@Entity
public class TestingChildEntity {
  private long id;
  private long parentId;
  private String someField;

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  public long getId() {
    return id;
  }

  @Column(insertable = false, name = "PARENT_ID", nullable = false, updatable = false)
  public long getParentId() {
    return parentId;
  }

  @Column
  public String getSomeField() {
    return someField;
  }

  public void setId(long argId) {
    id = argId;
  }

  public void setParentId(long argParentId) {
    parentId = argParentId;
  }

  public void setSomeField(String argSomeField) {
    someField = argSomeField;
  }
}

Then persist the parent with new children like this:

package test;

import java.util.List;

import org.junit.jupiter.api.Test;

import jakarta.persistence.*;

public class EntityTest {
  @Test
  public void testConverter() {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("test");

    EntityManager em = emf.createEntityManager();

    EntityTransaction tx = em.getTransaction();
    tx.begin();
    TestingEntity entity = new TestingEntity();
    entity.setSomeField("parent1");

    TestingChildEntity child1 = new TestingChildEntity();
    child1.setSomeField("child1");
    TestingChildEntity child2 = new TestingChildEntity();
    child2.setSomeField("child2");
    entity.setChildren(List.of(child1, child2));

    em.persist(entity);
    em.flush();
    tx.commit();
  }
}

• EclipseLink version 4.0.2
• Java/JDK version 21
• Entity source - See above

1. For problem in persistence (JPA)
   • JPA context listed above
   • Database provider/version HSQLDB 2.7.2
   • JDBC driver provider/version HSQLDB 2.7.2

Expected behavior
The parent and children should persist successfully, new IDs assigned to all and parent/child IDs should be populated.

Additional context
Instead of using the identity generator, the records are inserted with an ID of 0. A primary key violation occurs as a result, and the graph cannot be persisted.

If the GenerationType is SEQUENCE, this works as expected.

[AleksNo]

Hello, the reason might be because you use long as an id. So if there is already a child with the id 0 in the database then you cannot insert a new child with the id 0 again. Changing long to Long might help because Long is nullable.

[oramattkosem]

I can confirm that that appears to work as a workaround. This otherwise works properly with GenerationType of SEQUENCE as well. It's the combination of IDENTITY and primitive long that misbehaves.

[AleksNo]

I am glad, that it works. And i would change the id generator from GenerationType.IDENTITY to GenerationType.SEQUENCE if it is possible. GenerationType.SEQUENCE ist usually faster when you must insert many entities into the database.

Edit: I see, that it is not defined in the Jakarta Persistence spec what should happen when the primary key is 0. So the behavior can vary between Jakarta Persistence implementations.