This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Not-unique unique column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Not-unique unique column" Watch "Not-unique unique column" New topic
Author

Not-unique unique column

Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
Consider the following entity:



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

Joined: Apr 14, 2004
Posts: 10336

Did you use that entity to generate your DDL?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
I'm not sure what do you mean. The database is generated basing on this entity and others.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Ismael Upright wrote:Consider the following entity:



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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

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
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Jul 30, 2008
Posts: 162

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
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
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
Ranch Hand

Joined: Jul 30, 2008
Posts: 162

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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

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
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
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:




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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
Derby here, good sir

10.5.3.0 at the moment.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Jul 30, 2008
Posts: 162

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
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Not-unique unique column
 
Similar Threads
Composite key in collection of basic type
how to persist an object of a subclass from an existing super class entity object?
ORA-02291: integrity constraint, can not find parent key
Searching row in database by a column which is not a primary key
org.hibernate.MappingException: Could not determine type for:... with @ManyToOne