SQL Ordering does not honour unique constraints on non-primary column(s)
------------------------------------------------------------------------

                 Key: OPENJPA-253
                 URL: https://issues.apache.org/jira/browse/OPENJPA-253
             Project: OpenJPA
          Issue Type: Bug
            Reporter: Pinaki Poddar


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. 



 


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.