[OPENJPA-253] SQL Ordering does not honour unique constraints on non-primary column(s)

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Pinaki Poddar
• Created: 07/Jun/07 19:18
• Updated: 20/Mar/14 21:18

Description

In a same transaction, when an existing instance x is removed and a new instance y is created (in that order), during commit, OpenJPA SQL ordering issues INSERT SQL prior to DELETE (though the object operations invoked remove() before persist()).

If x and y had the same value for one or more non-primary but unique column(s), then INSERT fails when it preceeds DELETE.

Simple example:

// a simple onject with a unique non-primary column
@Entity
public class PObject

{ @Id @GeneratedValue private long id; @Column(unique=true) private String name; }

Simple Test:

public void testSQLOrderViolatesUniqueConstraintOnDeleteInsert ()

{ // create an entity p OpenJPAEntityManager em = getEM(); em.begin(); PObject p = new PObject(); p.setName("First"); em.persist(p); em.commit(); em.close(); // Find an existing entity p1, create another entity p2 // set p2 name same as that of p1 (name is unique in database) // remove p1 and *then* insert p2 // commit fails em = getEM(); em.begin(); PObject p1 = em.find(PObject.class, p.getId()); PObject p2 = new PObject(); p2.setName(p1.getName()); em.remove(p1); em.persist(p2); em.commit(); em.close(); }

The second transaction commit fails as INSERT SQL corresponding to em.persist(p2) is issued prior to DELETE corresponding to em.remove(p1), because p1 and p2 have the same value for 'name' field which, in a database, is represented as a non-primary but unique column.

Issue Links

• is part of: OPENJPA-235 SQL reordering to avoid non-nullable foreign key constraint violations
• relates to: OPENJPA-2211 Ordering of SQL Statements when using @UniqueConstraints