Schema generation with unique constraints

Hi all,

I'm using openjpa version 1.2.0 with HSQLDB and I have a verty nasty
problem using schema generator with unique constraints.

I have to classes to persist:

public class UniqueA {
...

private int uniqueValue;

}

public class UniqueB {
...

private int uniqueValue;
}


The property uniqueValue should be unique for both classes.
The first thing I used was the @Column annotation:
@Column(name="uniqueValue", unique = true)

The schema generator generates follow sql statement
....CONSTRAINT UNQ_ UNIQUE (uniqueValue)..

as soon openjpa generates the second constraint, it will fail because
the constraint name is not unique.



The second try was with the @Table annotation:
"@Table( uniqueConstraints= {@UniqueConstraint(columnNames=
{"uniqueValue"})})"

The schema generator generates following sql statement:
....CONSTRAINT UNQ_externalRef UNIQUE (uniqueValue)...

same as before, as soon openjpa generates the second constraint, it will
fail because the constraint name is not unique.


This is my openjpa.jdbc.SynchronizeMappings:
openjpa.jdbc.SynchronizeMappings=buildSchema(Forei gnKeys=true, Indexes=true)


So i finally have no solution the let openjpa generate the correct
schema. What do I do wrong?

Thank!
Claudio

Hi,
HSQLDB does not seem to allow identical unique constraint names in
different tables. On the contrary, MySQL allows. For example, MySQL will
create following unique constraints on table A and B with the same name
UNQ_uniqueValue as per the reported use case when both A.java and B.java are
annotated as
@Table(uniqueConstraints={@UniqueConstraint(column Names={"uniqueValue"})})


CREATE TABLE A (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
KEY (id), UNIQUE UNQ_uniqueValue (uniqueValue))
and
CREATE TABLE B (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
KEY (id), UNIQUE UNQ_uniqueValue (uniqueValue))


The use case when ran against HSQLDB the schema generation DDL were:

CREATE TABLE A (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
KEY (id), CONSTRAINT UNQ_uniqueValue UNIQUE (uniqueValue))
and
CREATE TABLE B (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
KEY (id), CONSTRAINT UNQ_uniqueValue UNIQUE (uniqueValue))

which failed because "Constraint already exists: UNQ_UNIQUEVALUE in
statement [CREATE TABLE B ..."

However, the unique constraint name was indeed created from the column name
'uniqueValue' which is different from what you have reported as
"...CONSTRAINT UNQ_externalRef UNIQUE (uniqueValue)...".

Not sure where and how the name "UNQ_externalRef" is being generated. But if
the actual column name is used for creating the name of the constraint then
a workaround will be to map A.uniqueValue and B.uniqueValue to differently
named columns in the database.




Claudio Romano-2 wrote:
>
> Hi all,
>
> I'm using openjpa version 1.2.0 with HSQLDB and I have a verty nasty
> problem using schema generator with unique constraints.
>
> I have to classes to persist:
>
> public class UniqueA {
> ...
>
> private int uniqueValue;
>
> }
>
> public class UniqueB {
> ...
>
> private int uniqueValue;
> }
>
>
> The property uniqueValue should be unique for both classes.
> The first thing I used was the @Column annotation:
> @Column(name="uniqueValue", unique = true)
>
> The schema generator generates follow sql statement
> ...CONSTRAINT UNQ_ UNIQUE (uniqueValue)..
>
> as soon openjpa generates the second constraint, it will fail because
> the constraint name is not unique.
>
>
>
> The second try was with the @Table annotation:
> "@Table( uniqueConstraints= {@UniqueConstraint(columnNames=
> {"uniqueValue"})})"
>
> The schema generator generates following sql statement:
> ...CONSTRAINT UNQ_externalRef UNIQUE (uniqueValue)...
>
> same as before, as soon openjpa generates the second constraint, it will
> fail because the constraint name is not unique.
>
>
> This is my openjpa.jdbc.SynchronizeMappings:
> openjpa.jdbc.SynchronizeMappings=buildSchema(Forei gnKeys=true,
> Indexes=true)
>
>
> So i finally have no solution the let openjpa generate the correct
> schema. What do I do wrong?
>
> Thank!
> Claudio
>
>

--
View this message in context: http://n2.nabble.com/Schema-generati...3p1470609.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Hi,

thx vor the quick response.

"UNQ_externalRef" was a typo, I'm sorry what openpa produces is of
course "UNQ_uniqueValue".

Mapping A.uniqueValue and B.uniqueValue to differently named columns in
the database could be a workaround.

thanks!
Claudio Romano




Pinaki Poddar wrote:
> Hi,
> HSQLDB does not seem to allow identical unique constraint names in
> different tables. On the contrary, MySQL allows. For example, MySQL will
> create following unique constraints on table A and B with the same name
> UNQ_uniqueValue as per the reported use case when both A.java and B.java are
> annotated as
> @Table(uniqueConstraints={@UniqueConstraint(column Names={"uniqueValue"})})
>
>
> CREATE TABLE A (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
> KEY (id), UNIQUE UNQ_uniqueValue (uniqueValue))
> and
> CREATE TABLE B (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
> KEY (id), UNIQUE UNQ_uniqueValue (uniqueValue))
>
>
> The use case when ran against HSQLDB the schema generation DDL were:
>
> CREATE TABLE A (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
> KEY (id), CONSTRAINT UNQ_uniqueValue UNIQUE (uniqueValue))
> and
> CREATE TABLE B (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
> KEY (id), CONSTRAINT UNQ_uniqueValue UNIQUE (uniqueValue))
>
> which failed because "Constraint already exists: UNQ_UNIQUEVALUE in
> statement [CREATE TABLE B ..."
>
> However, the unique constraint name was indeed created from the column name
> 'uniqueValue' which is different from what you have reported as
> "...CONSTRAINT UNQ_externalRef UNIQUE (uniqueValue)...".
>
> Not sure where and how the name "UNQ_externalRef" is being generated. But if
> the actual column name is used for creating the name of the constraint then
> a workaround will be to map A.uniqueValue and B.uniqueValue to differently
> named columns in the database.
>
>
>
>
> Claudio Romano-2 wrote:
>> Hi all,
>>
>> I'm using openjpa version 1.2.0 with HSQLDB and I have a verty nasty
>> problem using schema generator with unique constraints.
>>
>> I have to classes to persist:
>>
>> public class UniqueA {
>> ...
>>
>> private int uniqueValue;
>>
>> }
>>
>> public class UniqueB {
>> ...
>>
>> private int uniqueValue;
>> }
>>
>>
>> The property uniqueValue should be unique for both classes.
>> The first thing I used was the @Column annotation:
>> @Column(name="uniqueValue", unique = true)
>>
>> The schema generator generates follow sql statement
>> ...CONSTRAINT UNQ_ UNIQUE (uniqueValue)..
>>
>> as soon openjpa generates the second constraint, it will fail because
>> the constraint name is not unique.
>>
>>
>>
>> The second try was with the @Table annotation:
>> "@Table( uniqueConstraints= {@UniqueConstraint(columnNames=
>> {"uniqueValue"})})"
>>
>> The schema generator generates following sql statement:
>> ...CONSTRAINT UNQ_externalRef UNIQUE (uniqueValue)...
>>
>> same as before, as soon openjpa generates the second constraint, it will
>> fail because the constraint name is not unique.
>>
>>
>> This is my openjpa.jdbc.SynchronizeMappings:
>> openjpa.jdbc.SynchronizeMappings=buildSchema(Forei gnKeys=true,
>> Indexes=true)
>>
>>
>> So i finally have no solution the let openjpa generate the correct
>> schema. What do I do wrong?
>>
>> Thank!
>> Claudio
>>
>>
>

Hi,
OpenJPA trunk Revision: 712300 generates a unique name for unique constraint
for @Column(unique=true) annotation.
If your environment allows an upgrade you can give this new change a try.



Claudio Romano-2 wrote:
>
> Hi,
>
> thx vor the quick response.
>
> "UNQ_externalRef" was a typo, I'm sorry what openpa produces is of
> course "UNQ_uniqueValue".
>
> Mapping A.uniqueValue and B.uniqueValue to differently named columns in
> the database could be a workaround.
>
> thanks!
> Claudio Romano
>
>
>
>
> Pinaki Poddar wrote:
>> Hi,
>> HSQLDB does not seem to allow identical unique constraint names in
>> different tables. On the contrary, MySQL allows. For example, MySQL will
>> create following unique constraints on table A and B with the same name
>> UNQ_uniqueValue as per the reported use case when both A.java and B.java
>> are
>> annotated as
>> @Table(uniqueConstraints={@UniqueConstraint(column Names={"uniqueValue"})})
>>
>>
>> CREATE TABLE A (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
>> KEY (id), UNIQUE UNQ_uniqueValue (uniqueValue))
>> and
>> CREATE TABLE B (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
>> KEY (id), UNIQUE UNQ_uniqueValue (uniqueValue))
>>
>>
>> The use case when ran against HSQLDB the schema generation DDL were:
>>
>> CREATE TABLE A (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
>> KEY (id), CONSTRAINT UNQ_uniqueValue UNIQUE (uniqueValue))
>> and
>> CREATE TABLE B (id BIGINT NOT NULL, uniqueValue INTEGER NOT NULL, PRIMARY
>> KEY (id), CONSTRAINT UNQ_uniqueValue UNIQUE (uniqueValue))
>>
>> which failed because "Constraint already exists: UNQ_UNIQUEVALUE in
>> statement [CREATE TABLE B ..."
>>
>> However, the unique constraint name was indeed created from the column
>> name
>> 'uniqueValue' which is different from what you have reported as
>> "...CONSTRAINT UNQ_externalRef UNIQUE (uniqueValue)...".
>>
>> Not sure where and how the name "UNQ_externalRef" is being generated. But
>> if
>> the actual column name is used for creating the name of the constraint
>> then
>> a workaround will be to map A.uniqueValue and B.uniqueValue to
>> differently
>> named columns in the database.
>>
>>
>>
>>
>> Claudio Romano-2 wrote:
>>> Hi all,
>>>
>>> I'm using openjpa version 1.2.0 with HSQLDB and I have a verty nasty
>>> problem using schema generator with unique constraints.
>>>
>>> I have to classes to persist:
>>>
>>> public class UniqueA {
>>> ...
>>>
>>> private int uniqueValue;
>>>
>>> }
>>>
>>> public class UniqueB {
>>> ...
>>>
>>> private int uniqueValue;
>>> }
>>>
>>>
>>> The property uniqueValue should be unique for both classes.
>>> The first thing I used was the @Column annotation:
>>> @Column(name="uniqueValue", unique = true)
>>>
>>> The schema generator generates follow sql statement
>>> ...CONSTRAINT UNQ_ UNIQUE (uniqueValue)..
>>>
>>> as soon openjpa generates the second constraint, it will fail because
>>> the constraint name is not unique.
>>>
>>>
>>>
>>> The second try was with the @Table annotation:
>>> "@Table( uniqueConstraints= {@UniqueConstraint(columnNames=
>>> {"uniqueValue"})})"
>>>
>>> The schema generator generates following sql statement:
>>> ...CONSTRAINT UNQ_externalRef UNIQUE (uniqueValue)...
>>>
>>> same as before, as soon openjpa generates the second constraint, it will
>>> fail because the constraint name is not unique.
>>>
>>>
>>> This is my openjpa.jdbc.SynchronizeMappings:
>>> openjpa.jdbc.SynchronizeMappings=buildSchema(Forei gnKeys=true,
>>> Indexes=true)
>>>
>>>
>>> So i finally have no solution the let openjpa generate the correct
>>> schema. What do I do wrong?
>>>
>>> Thank!
>>> Claudio
>>>
>>>
>>
>
>

--
View this message in context: http://n2.nabble.com/Schema-generati...3p1481092.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.