How to allow in the field with a unique constraint to enter a lot of null values in SQL Server

2
votes
create table test3
(
id int PRIMARY KEY,
id2 int
);

create unique index ndx_id2 on test3 (id2);

For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle.

Informix and Microsoft SQL Server follow the other interpretation of the standard.

INSERT INTO test3(id, id2) VALUES (1, null);
INSERT INTO test3(id, id2) VALUES (2, null);

Second INSERT returns

A duplicate value cannot be inserted into a unique index. [ Table name = test3,Constraint name = ndx_id2 ]

Error in SQL Server but successfully adds record to another DBMS, Sqlite for example.

How to allow in the field with a unique constraint to enter a lot of null values ​​in SQL Server?

1
sql-server

1 Answers

7
votes

From SQL Server 2008 and onwards, you can use a filtered index:

create unique index UX_YourIndex
on YourTable(col1) 
where col1 is not null

Example at SQL Fiddle.

For SQL Server 2005 and older (9.0 corresponds to 2005), you can use a surrogate column. A surrogate column is a computed column. It is computed as follows:

  • When the semi-unique column is not null, the surrogate column is equal to that column
  • When the semi-unique column is null, the surrogate column is equal to a unique id. A unique id can be an identity column, a rowguid, or anything that is different for each row.

In other words, the surrogate column uses a unique string whenever the original column is null. For example:

create table YourTable 
    (
    id int identity primary key
,   col1 int
,   surrogate1 as (coalesce(convert(varchar(20), col1), '_' + convert(varchar(20), id)))
    );

create unique index UX_YourIndex on YourTable(surrogate1);

It's your job to make sure that the surrogate values do not collide with real values. In this example, I'm assuming that col1 cannot start with an underscore.

Example at SQL Fiddle.