Generated UNIQUE constraint names and HSQL
-------------------------------------------
Key: OPENJPA-1489
URL: https://issues.apache.org/jira/browse/OPENJPA-1489
Project: OpenJPA
Issue Type: Improvement
Components: sql
Affects Versions: 1.2.2
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)
Reporter: Hannes Schmidt
Priority: Minor
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 constraints that list 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.transactionaltestexecutionliste...@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.collectiondaot...@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 ...
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
