[OPENJPA-1489] Generated UNIQUE constraint names and HSQL

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 1.2.2
• Fix Version/s: 1.3.0, 2.0.0-beta
• Component/s: sql
• Environment: OpenJPA 1.2.2; HSQL 1.8.0.10 and HSQL 1.8.1.1 and 2.0.0-rc8; Mac OS X 10.6.2; Java 1.6.0_17 Java HotSpot(TM) 64-Bit Server VM (build 14.3-b01-101, mixed mode)
• Assignee: Milosz Tylenda
• Reporter: Hannes Schmidt
• Created: 30/Jan/10 20:18
• Updated: 09/Mar/10 18:29
• Resolved: 31/Jan/10 09:09

Description

In my unit tests, I'm using the openjpa.jdbc.SynchronizeMappings feature to have OpenJPA create the schema in an HSQL in-memory database. Two of the tables in my schema each have a unique constraint that lists the same column names (foo,bar) in the same order. The generated name for both constraints is UNQ_foobar. HSQL apparently puts the names of unique constraints into a schema-wide namespace which causes the CREATE TABLE statement for the second table to fail with "constraint already exist".

@Entity
@Table( uniqueConstraints = { @UniqueConstraint( columnNames =

{ "foo", "bar" } ) } )
public class X {
...
private String foo;
private long bar;
...
}

@Entity
@Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar" }

) } )
public class Y

{ ... private String foo; private long bar; ... } 912 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856> executing stmnt 1211647530 CREATE TABLE X ( ... , foo VARCHAR(22) NOT NULL, ... , bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo, bar)) 915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856> [3 ms] spent 915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826> executing stmnt 853942561 CREATE TABLE Y ( ... , foo VARCHAR(22) NOT NULL, ... , bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo, bar)) 920 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826> [5 ms] spent Jan 30, 2010 11:46:16 AM org.springframework.test.context.TestContextManager beforeTestMethod WARNING: Caught exception while allowing TestExecutionListener [org.springframework.test.context.transaction.TransactionalTestExecutionListener@2773a64a] to process 'before' execution of test method [public void com.eyealike.ps.core.test.CollectionDaoTest.setup()] for test instance [com.eyealike.ps.core.test.CollectionDaoTest@19aa5882] org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is <openjpa-1.2.2-r422266:898935 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: Constraint already exists: UNQ_OWNERIDKEY in statement [CREATE TABLE Y ... }

[code=-60, state=S0011]

Note that I tweaked my sample code and log trace a little to simplify the test case.

I didn't try to reproduce this with OpenJPA 2.0.0 yet. From looking at the code in the 2.0.0 trunk, I can tell that this part has been rewritten completely and this issue might not apply to 2.0.0. I also don't know whether HSQL is violating the SQL standard by requiring unique constaint names to be schema-unique, not just table-unique.

What works for me as a workaround is to reorder the columns in the constraint on the second table.

Assuming this is not a bug in HSQL and assuming it also reproduces in 2.0.0 beta, I'd naively prefix constraint names with the table name but I'm no specialist in either SQL or JPA ...

Issue Links

• duplicates: OPENJPA-1165 Two unique constraints in a table try to share the same constraint name