Not-unique unique column

[Ismael Upright]

Consider the following entity:

@Entity(name = "Person")  
public class Person  {  
  
    @Id  
    @Column(name="OBJECT_ID")  
    @GeneratedValue(strategy = GenerationType.TABLE)  
    private Long objectId;  
      
    @Column(name = "SURNAME", unique = true)  
    private String surname;  
      
    @Column(name = "NAME")  
    private String name;  
  
    //getters and setters  
  
}

Question: why can I put 2 Persons with the same surname into the database?

[Paul Sturrock]

Did you use that entity to generate your DDL?

[Ismael Upright]

I'm not sure what do you mean. The database is generated basing on this entity and others.

[Paul Sturrock]

DDL == Data Definition Language, or the definition of your table.

So you generated your table from this mapping? If what you say is correct this suggests a bug in whichever JPA implementation you used. The specification is quite clear on what this attribute of the annotation means. Are you sure you can insert two Person records with exactly the same surname?

[Ismael Upright]

I'm sure, I just did it agan. I can see two persons with the same surname in my application and in the database after I connected to it with SquirrelSQL.

I use hibernate-entitymanager in version 3.5.0-CR-1.

I tried with 3.5.0-CR-2 just now since it became available and the problem still exists.

[Paul Sturrock]

Well, I'm not aware of a bug like that in Hibernate. If you are happy you've not made a mistake, you probably want to package up your test case and log a defect in Hibernate's JIRA.

[Mark Spritzler]

Ismael Upright wrote:Consider the following entity:

@Entity(name = "Person")  
public class Person  {  
  
    @Id  
    @Column(name="OBJECT_ID")  
    @GeneratedValue(strategy = GenerationType.TABLE)  
    private Long objectId;  
      
    @Column(name = "SURNAME", unique = true)  
    private String surname;  
      
    @Column(name = "NAME")  
    private String name;  
  
    //getters and setters  
  
}

Question: why can I put 2 Persons with the same surname into the database?

http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#d0e3473

If I read this correctly, Hibernate isn't using the unique="true" in your @Column to generate a unique constraint. Obviously, Hibernate is not creating a unique constraint. But in the docs, maybe just having the Validator jar in your classpath will make hbm2ddl to generate the unique constraint.

Mark

[Paul Sturrock]

Mark, doesn't the JPA specification mean the UniqueConstraint annotation (the "unique" attribute in this case being shorthand for this annotation) requires Hibernate to include a unique constraint when generating DDL?

[Mark Spritzler]

Paul Sturrock wrote:Mark, doesn't the JPA specification mean the UniqueConstraint annotation (the "unique" attribute in this case being shorthand for this annotation) requires Hibernate to include a unique constraint when generating DDL?

I am not sure, but when I read that link, it seems to say no it doesn't. But that doesn't make sense. But if what the OP says is true, then obviously a unique constraint is not being created in the database.

Mark

[Ismael Upright]

Mark Spritzler wrote:
I am not sure, but when I read that link, it seems to say no it doesn't. But that doesn't make sense. But if what the OP says is true, then obviously a unique constraint is not being created in the database.
Mark

Quite nasty thing in Hibernate I'd say...

I added hibernate-validator 4.0.2.GA to pom.xml, added two properties into persistence.xml:

<property name="hibernate.validator.apply_to_ddl" value="true" />
<property name="hibernate.validator.autoregister_listeners" value="true" />

as the link you provided says but still I can put 2 persons with identical surname into the database

[Paul Sturrock]

Are we getting confused here - Hibernate validator is a tool to validate the properties of JavaBeans, it implies nothing about the constraints created in a database when a JavaBean is used to generate DDL. The JPA however specification does state:

The UniqueConstraint annotation is used to specify that a unique constraint is to be included in
the generated DDL for a primary or secondary table.

if Hibernate is not doing this I would say this is a huge hole in Hibernate's JPA implementation, and a very surprising one at that. A quick skim of the Hibernate JIRA doesn't turn up any simmilar bug in this area though.

[Kuladip Yadav]

Hi All,

May be you have added constants unique= true after schema creation and having property hbm2ddl.auto = update

So if you use hbm2ddl.auto = create then definitely it will generate unique constraint on particular column.


Thank You

Note:- hbm2ddl.auto = create will erase your all data and create new tables. So after executing first time change it to "update"

[Ismael Upright]

I have

hibernate.hbm2ddl.auto = create-drop

Everytime I launch the application and connect to the database, all tables are dropped and created from scratch.

[Kuladip Yadav]

Can you check logs generated for your application by setting logger value for org.hibernate.tool.hbm2ddl to DEBUG ?
It will show create statement for all tables.

[Mark Spritzler]

Ismael Upright wrote:I have

hibernate.hbm2ddl.auto = create-drop

Everytime I launch the application and connect to the database, all tables are dropped and created from scratch.

You should also be able to find a ddl script file that gets created and just look in it to see if you have a unique constraint on it.

Also, are you 100% sure it is the exact same surname. I mean if you even have an extra space at the end or up front will cause them to be different in the eyes of the database.

Mark

[Mark Spritzler]

Paul Sturrock wrote:Are we getting confused here - Hibernate validator is a tool to validate the properties of JavaBeans, it implies nothing about the constraints created in a database when a JavaBean is used to generate DDL. The JPA however specification does state:

The UniqueConstraint annotation is used to specify that a unique constraint is to be included in
the generated DDL for a primary or secondary table.

if Hibernate is not doing this I would say this is a huge hole in Hibernate's JPA implementation, and a very surprising one at that. A quick skim of the Hibernate JIRA doesn't turn up any simmilar bug in this area though.

In Hibernate, it can be slightly different and maybe you are using Validator annotations and you want them to be included in your DDL script. I was thinking that underneath the cover the unique="true" was being handled by the Validator package instead.

Also, the UniqueConstraint annotation is not being used in the OP's example. But I think he could try to add it and see what happens.

I am sure at this point the error is probably a single character error somewhere. ;)

Mark

[Ismael Upright]

Some news about this problem.


I changed org.hibernate.tool.hbm2ddl logging level to DEBUG.


The following fragment appears in logs:

39656 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport -
create table Person (
NAME varchar(255),
SURNAME varchar(255),
OBJECT_ID bigint not null,
primary key (OBJECT_ID)
)


I changed the Person class code by adding "nullable = false" annotation to surname field:

@Entity(name = "Person")    
public class Person  {    
    
    @Id    
    @Column(name="OBJECT_ID")    
    @GeneratedValue(strategy = GenerationType.TABLE)    
    private Long objectId;    
        
    @Column(name = "SURNAME", unique = true, nullable = false)    
    private String surname;    
        
    @Column(name = "NAME")    
    private String name;    
    
    //getters and setters    
    
}

After this change the following fragment appears in logs:

21968 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport -
create table Person (
NAME varchar(255),
SURNAME varchar(255) not null unique,
OBJECT_ID bigint not null,
primary key (OBJECT_ID)
)


It seems that unique annotation doesn't work without setting nullable to false.

My further question: is it correct behavior?

[Paul Sturrock]

Mark Spritzler wrote:
Also, the UniqueConstraint annotation is not being used in the OP's example. But I think he could try to add it and see what happens.

My quote is from the UniqueConstraint part of the spec. because I thought it a little long winded to also quote:

Column Annotation Elements

boolean unique (Optional) Whether the property is a unique key.
This is a shortcut for the UniqueConstraint annotation
at the table level and is useful for when the
unique key constraint is only a single field. This
constraint applies in addition to any constraint
entailed by primary key mapping and to constraints
specified at the table level.

You are quite possibly right about a typo or simmilar, becauseI'm still very surprised to hear Hibernate suffers from a bug like this (after all, it would suggest little or no testing).

Ismael, which database do you use?

[Ismael Upright]

Derby here, good sir

10.5.3.0 at the moment.

[Paul Sturrock]

Ah - I'm no Derby expert, but does it allow unique constraint on nullable fields? A quick skim of the Derby docs on column constraint clauses comes up with this:

UNIQUE

Specifies that values in the column must be unique. NULL values are not allowed.

So it loooks like no bug in your code, and no bug in Hibernate, just a (documented) weakness in Derby.

If you'll allow me to get on my soapbox now , this is one of many reasons why allowing the ORM to generate your schema is not something that I'd ever recommend.

[Kuladip Yadav]

In Derby Null values are not allowed for unique constraints. See here

May be ,thats why hibernate dialect skip it.
For mysql it works fine.

[Ismael Upright]

Yes, I see it.


"[UNIQUE:] Specifies that values in the columns must be unique. The identified columns must be defined as NOT NULL."


So the problem is in the specific dialect, not Hibernate itself.


Thanks everyone.

[Paul Sturrock]

Ismael Upright wrote:Yes, I see it.


"[UNIQUE:] Specifies that values in the columns must be unique. The identified columns must be defined as NOT NULL."


So the problem is in the specific dialect, not Hibernate itself.


Thanks everyone.

No, the problem is Derby. The Dialect does what Derby defines can be done.

[Mark Spritzler]

Paul Sturrock wrote:

If you'll allow me to get on my soapbox now , this is one of many reasons why allowing the ORM to generate your schema is not something that I'd ever recommend.

My Soapbox. When you understand an ORM tool and how certain mappings map to certain table design, I prefer using an ORM to generate my schema, in greenfield (brand new development) It saves me a load of time, and 99% of the time the ORM tool generates the tables exactly as how I would have designed the database tables. (I was a former DBA too)

Mark