@UniqueConstraint for nullable field

Question

I have a Product entity and it's name and category combination should be unique for every product.

In my case name is required, but category may not be present.

Product is defined like this:

@Entity
@Table(
    uniqueConstraints=
        @UniqueConstraint(columnNames={"name", "category")
)
public class Product {

    // ...

    @NotNull
    @Size(min = 1, max = 64)
    private String name;

    @ManyToOne(fetch = FetchType.EAGER)
    private ProductCategory category;

    // ...

}

... but the constraint works only when category is not NULL.

In other words I cannot persist (and this is ok) entities with:

name="example", category=1
name="example", category=1

... at the same time I can persist (and this is not what I want) entities wihh:

name="example", category=null
name="example", category=null

So, my question is - how do I implement unique constraint for combination of fields, one of which can be nullable (with NULL treated as any other value)?

PS: I use Hibernate as JPA provider and MySQL database.

V G · Answer 1 · 2013-11-22T16:16:09.620

0

The @UniqueConstraints annotation should do the work. Check the documentation with an example. Also if the table is autogenerated, you might consider dropping the table, depending on how your autogeneration of the schema is set in your persistence.xml file.

UPDATE Of course, you must specify both columns (not fields as you did):

@UniqueConstraint(columnNames={"name", "category_id")//or check the name of the column in the db for category

UPDATE 2 Because the problem is actually in mysql, you could insert other two persistent fields to your entity:

@Column(columnName="category", insertable=false, updatable=false)//map to the same column used for your category
private Long categoryId;//I suppose it is a Long. 

@Column(columnName="categoryIdConstraint")//
private long categoryIdConstraint;//this is not nullable. If categoryId is null, then simply put 0 or -1

//and now synchronize the categoryIdConstraint field with entity listeners
//and add the @UniqueConstraint over "name" and "categoryIdConstraint" (which now cannot be null.)

edited Nov 22 '13 at 16:16

answered Nov 22 '13 at 12:53

V G

18,822
6
51
89

Well, I guess it should but it doesn't. Hence the question. Reading questions like this: http://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints it seems that this is MySQL specific thing. I cannot find workaround though. – Eugene Loy Nov 22 '13 at 13:07
Why not simply having a default Category? Or forcing one? – V G Nov 22 '13 at 14:26
This is my plan B. But this is rather hacky solution in my case and I'd like to avoid it. – Eugene Loy Nov 22 '13 at 14:28
Updated the answer. You could say the same about this solution, but it is just an idea. – V G Nov 22 '13 at 15:39

score 0 · Accepted Answer · answered Dec 22 '13 at 13:30

Managed to avoid solving this this issue relatively easy (by appying null object pattern).

For anyone who cannot avoid this issue easily and is wondering how to solve this with as less pain as possible - have a look on implementing mysql trigger that will do unique constraint validation taking nullable columns into account. Here is a nice starting point.

@UniqueConstraint for nullable field

2 Answers2