Multiple Annotatns for Unique Constraints Break SchemaExport

keenan · **Joined:** Mon Jan 09, 2006 3:05 pm **Posts:** 24

Hibernate version:
* Hibernate Core 3.2.0.CR2
* Hibernate Annotation 3.2.0.CR1
* Hibernate Entity Manager 3.2.0.CR1

Problem
This example demonstrates two different aspects of a bug which prevents schema generation. The underlying problem seems to be that if a unique constraint is specified on the same column in two different annotations, then generated DDL tries to specify the constraint twice, which Oracle rejects.

Mapping documents:

Code:

@Entity
@Table(uniqueConstraints = { @UniqueConstraint( columnNames = { "NAME" } ) })
public class Constrained {
     private long id;
     private String name;
     
     @Id
     @Column(name="id", unique=true, nullable=false, insertable=true, updatable=true, precision=15, scale=0)
     public long getId() {
         return this.id;
     }     
     public void setId(long id) {
         this.id = id;
     }
     
     @Column(name="name", nullable=false, insertable=true, updatable=true, length=80)
     public String getName() {
         return this.name;
     }     
     public void setName(String name) {
         this.name = name;
     }
} // Constrained

Database
Oracle 10.1
Oracle JDBC Driver 10.2
Hibernate Dialect: Oracle9Dialect

Discussion
As shown above, the generated DDL is:

Quote:

create table Constrained (id number(19,0) not null unique, name varchar2(80 char) not null unique, primary key (id), unique (name))

Oracle rejects this DDL because ID and NAME each have two overlapping constraints. The problem goes away if unique=true is removed from both of the @Column annotations.

The duplicate unique specification is obvious in the NAME case, it is in the @Table and in the @Column. The duplication is only implicit in the ID case, it is specified in the @Column and implicit because it is marked @Id.

I'm not sure if this is an Annotations problem, a Tools problem or just an error in the Oracle9Dialect, and will pursue it in the appropriate forum if you can give me a hint on where to start. Thanks,
--keenan

emmanuel · **Posted:** Sun Jul 16, 2006 1:07 pm

I thought I fixed this with http://opensource.atlassian.com/projects/hibernate/browse/ANN-118

Are you sure you use the latest version?

This is related to the core, some help on this would be apreciated.

keenan · **Joined:** Mon Jan 09, 2006 3:05 pm **Posts:** 24

Yes, I'm using the latest released versions:

Code:

INFO  11:42:22,215 (ate.ejb.Version:       <clinit>:  15)  -Hibernate EntityManager 3.2.0.CR1
INFO  11:42:22,262 (tations.Version:       <clinit>:  15)  -Hibernate Annotations 3.2.0.CR1
INFO  11:42:22,293 (cfg.Environment:       <clinit>: 499)  -Hibernate 3.2 cr2
...
ERROR 11:42:27,187 (dl.SchemaExport:         create: 272)  -Unsuccessful: create table Constrained (id number(19,0) not null unique, name varchar2(80 char) not null, primary key (id), unique (name))
ERROR 11:42:27,202 (dl.SchemaExport:         create: 273)  -ORA-02261: such unique or primary key already exists in the table

ANN-118 talks about duplicate unique constraints due to a @JoinColumn. While similar, this example (generated by the reverse engineering tool) demonstrates TWO other ways of duplicating the unique constraint:

@Id in conjunction with @Column(...unique=true)

@Table(uniqueConstraints = { @UniqueConstraint( columnNames = { "NAME" } ) }) also in conjunction with @Column(...unique=true)

Perhaps Oracle is more strict in its DDL than other databases?

Let me know how I can help, I set the logging level to DEBUG but nothing more of interest was printed.
--keenan

emmanuel · **Posted:** Thu Jul 20, 2006 4:41 am

post a working unit test case to JIRA will speed up the work.
Or a patch, will speed it up even more :-)