12

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
Community
  • 1
  • 1
user3375943
  • 171
  • 1
  • 1
  • 7
  • Please check what kind of constraints are really put on entities table. Run `show create table your_table_name` – Antoniossss Mar 03 '14 at 19:17
  • A unique Constraint will Allow you ONE null value. If it allows you multiple null values then it wouldnt be Unique would it :) – M.Ali Mar 03 '14 at 19:17
  • @M.Ali I thought that NULL will not violate unique constraints. Lets have A that relates to B via FK and the relation is ONE - ONE but not mandatory. Your assumption would prevent creating such constraints – Antoniossss Mar 03 '14 at 19:21
  • @M.Ali the old argument. `null` means no value, so there is no sense in applying the restriction of `unique` to it. In general I like SQL Server, but this is a major mistake. – SJuan76 Mar 03 '14 at 19:21
  • @SJuan76 you are very wrong there my friend, in SQL Server NULL is not considered as no value but it is considered as `an Unknown value`. It can be no value or it can be any value. – M.Ali Mar 03 '14 at 19:26
  • 1
    @M.Ali if it is considere an unknown value then it should even more so not be considered a unique value. MySQL handles this more correct: "A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL." – Bachi Aug 08 '14 at 14:12

1 Answers1

1

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

Community
  • 1
  • 1
SJuan76
  • 24,532
  • 6
  • 47
  • 87
  • Another link: http://stackoverflow.com/questions/1796414/sql-server-unique-constraint-with-duplicate-nulls – SJuan76 Mar 03 '14 at 19:23
  • SJuan76 I see the point about NULL values in SQL Sever. I guess I will have to manage this business logic programatically. Thanks. – user3375943 Mar 04 '14 at 15:45