Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I create a Unique Constraint that allows NULL values with Hibernate annotations?

I am using: Spring 4, Hibernate 4, SQL Server 2008

I know how to do it with SQL Server 2008 from this question response How do I create a unique constraint that also allows nulls?

But since I don't generate any manual SQL code during the creation of the table, is it possible to generate a "where clause" in my constraint through Hibernate annotations in my Entity class?

My DDL is created from scratch with the java entity definition as follows:

@Entity
@Table(name="Neighborhood", 
uniqueConstraints = {@UniqueConstraint(columnNames = {"codecnbv","zipcode"})})
@JsonSerialize(include = JsonSerialize.Inclusion.NON_EMPTY)
public class Neighborhood implements Serializable {

private String id;
private String codecnbv;
private String zipcode;

@Id 
@Column(name="id", nullable=false, unique=true, length=2)
public String getId() {
    return this.id;
}

@Column(name="codecnbv", nullable=true, length=12) //explicitly nullable
public String getCodecnbv() {
    return codecnbv;
}

@Column(name="zipcode", nullable=true, length=5) //explicitly nullable
public String getZipcode() {
    return zipcode;
}

}

However, as soon as I add data and try to enter a second record with NULL in column codecnbv and/or zipcode, I receive an exception that says I've violated the unique constraint.

The requirement I have says that I must allow multiple null values, and when the value is not null, then I should have unique values i.e.

For zipcode column

  1. 56000 --ok
  2. NULL --ok
  3. 34089 --ok
  4. NULL --ok
  5. 34089 --Not allowed
  6. 34567 --ok
like image 501
user3375943 Avatar asked Mar 03 '14 19:03

user3375943


People also ask

Can unique constraint have NULL values?

You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value. This means that it's possible to insert rows that appear to be duplicates if one of the values is NULL .

How Hibernate handle NULL values?

The best way to avoid Hibernate's attempts at setting null values to primitives is to use Wrapper classes (Integer, Long, Double...); and especially, if you need to tack on a column or 2 to an existing table.

Which constraint allows NULL values?

Use the NULL keyword to specify that a column can store the NULL value for its data type. This implies that the column need not receive any value during insert or update operations. The NULL constraint is logically equivalent to omitting the NOT NULL constraint from the column definition.

Can you have a unique constraint on a nullable column?

As you know, when you create a UNIQUE constraint on a nullable column, SQL Server allows only one NULL value, thereby maintaining the UNIQUEness. However, there are situations when we need more than one NULL value in the column but still have to maintain uniqueness, ignoring all those NULL values.


1 Answers

It is not an issue of Hibernate but of SQL Server, which considers NULL a value and does not allow a second NULL value. Wicked, I know.

Some links:

How do I create a unique constraint that also allows nulls?

http://sqlmag.com/database-development/multiple-nulls-unique-constraints

like image 127
SJuan76 Avatar answered Oct 28 '22 16:10

SJuan76