How to create a unique index on a NULL column

Question

I am using SQL Server 2005   I want to constrain the values in a column to be unique  while allowing NULLS   My current solution involves a unique index on a view like so   CREATE VIEW vw unq WITH SCHEMABINDING AS     SELECT Column1       FROM MyTable      WHERE Column1 IS NOT NULL  CREATE UNIQUE CLUSTERED INDEX unq idx ON vw unq  Column1    Any better ideas

User · Accepted Answer

Pretty sure you can t do that  as it violates the purpose of uniques   However  this person seems to have a decent work around  http   sqlservercodebook blogspot com 2008 04 multiple-null-values-in-unique-index-in html

User · Answer

It is possible to use filter predicates to specify which rows to include in the index  From the documentation   WHERE  lt filter predicate gt  Creates a filtered index by specifying which rows to include in the index  The filtered index must be a nonclustered index on a table  Creates filtered statistics for the data rows in the filtered index   Example  CREATE TABLE Table1     NullableCol int NULL    CREATE UNIQUE INDEX IX Table1 ON Table1  NullableCol  WHERE NullableCol IS NOT NULL

User · Answer

The calculated column trick is widely known as a  nullbuster   my notes credit Steve Kass   CREATE TABLE dupNulls   pk int identity 1 1  primary key  X  int NULL  nullbuster as  case when X is null then pk else 0 end   CONSTRAINT dupNulls uqX UNIQUE  X nullbuster

User · Answer

It is possible to use filter predicates to specify which rows to include in the index  From the documentation   WHERE  lt filter predicate gt  Creates a filtered index by specifying which rows to include in the index  The filtered index must be a nonclustered index on a table  Creates filtered statistics for the data rows in the filtered index   Example  CREATE TABLE Table1     NullableCol int NULL    CREATE UNIQUE INDEX IX Table1 ON Table1  NullableCol  WHERE NullableCol IS NOT NULL

User · Answer

Using SQL Server 2008  you can create a filtered index  http   msdn microsoft com en-us library cc280372 aspx    I see Simon added this as a comment  but thought it deserved its own answer as the comment is easily missed    Another option is a trigger to check uniqueness  but this could affect performance

User · Answer

Strictly speaking  a unique nullable column  or set of columns  can be NULL  or a record of NULLs  only once  since having the same value  and this includes NULL  more than once obviously violates the unique constraint   However  that doesn t mean the concept of  unique nullable columns  is valid  to actually implement it in any relational database we just have to bear in mind that this kind of databases are meant to be normalized to properly work  and normalization usually involves the addition of several  non-entity  extra tables to establish relationships between the entities   Let s work a basic example considering only one  unique nullable column   it s easy to expand it to more such columns   Suppose we the information represented by a table like this   create table the entity incorrect     id integer    uniqnull integer null     we want this to be  unique and nullable       primary key  id       We can do it by putting uniqnull apart and adding a second table to establish a relationship between uniqnull values and the entity  rather than having uniqnull  inside  the entity    create table the entity     id integer    primary key id      create table the relation     the entity id integer not null    uniqnull integer not null     unique the entity id     unique uniqnull        primary key can be both or either of the entity id or uniqnull      primary key  the entity id  uniqnull      foreign key  the entity id  references the entity id       To associate a value of uniqnull to a row in the entity we need to also add a row in the relation   For rows in the entity were no uniqnull values are associated  i e  for the ones we would put NULL in the entity incorrect  we simply do not add a row in the relation   Note that values for uniqnull will be unique for all the relation  and also notice that for each value in the entity there can be at most one value in the relation  since the primary and foreign keys on it enforce this   Then  if a value of 5 for uniqnull is to be associated with an the entity id of 3  we need to   start transaction  insert into the entity  id  values  3    insert into the relation  the entity id  uniqnull  values  3  5   commit    And  if an id value of 10 for the entity has no uniqnull counterpart  we only do   start transaction  insert into the entity  id  values  10    commit    To denormalize this information and obtain the data a table like the entity incorrect would hold  we need to   select   id  uniqnull from   the entity left outer join the relation on   the entity id   the relation the entity id     The  left outer join  operator ensures all rows from the entity will appear in the result  putting NULL in the uniqnull column when no matching columns are present in the relation   Remember  any effort spent for some days  or weeks or months  in designing a well normalized database  and the corresponding denormalizing views and procedures  will save you years  or decades  of pain and wasted resources

User · Answer

The calculated column trick is widely known as a  nullbuster   my notes credit Steve Kass   CREATE TABLE dupNulls   pk int identity 1 1  primary key  X  int NULL  nullbuster as  case when X is null then pk else 0 end   CONSTRAINT dupNulls uqX UNIQUE  X nullbuster

User · Answer

The calculated column trick is widely known as a  nullbuster   my notes credit Steve Kass   CREATE TABLE dupNulls   pk int identity 1 1  primary key  X  int NULL  nullbuster as  case when X is null then pk else 0 end   CONSTRAINT dupNulls uqX UNIQUE  X nullbuster

User · Answer

Using SQL Server 2008  you can create a filtered index  http   msdn microsoft com en-us library cc280372 aspx    I see Simon added this as a comment  but thought it deserved its own answer as the comment is easily missed    Another option is a trigger to check uniqueness  but this could affect performance

User · Answer

The calculated column trick is widely known as a  nullbuster   my notes credit Steve Kass   CREATE TABLE dupNulls   pk int identity 1 1  primary key  X  int NULL  nullbuster as  case when X is null then pk else 0 end   CONSTRAINT dupNulls uqX UNIQUE  X nullbuster

User · Answer

Strictly speaking  a unique nullable column  or set of columns  can be NULL  or a record of NULLs  only once  since having the same value  and this includes NULL  more than once obviously violates the unique constraint   However  that doesn t mean the concept of  unique nullable columns  is valid  to actually implement it in any relational database we just have to bear in mind that this kind of databases are meant to be normalized to properly work  and normalization usually involves the addition of several  non-entity  extra tables to establish relationships between the entities   Let s work a basic example considering only one  unique nullable column   it s easy to expand it to more such columns   Suppose we the information represented by a table like this   create table the entity incorrect     id integer    uniqnull integer null     we want this to be  unique and nullable       primary key  id       We can do it by putting uniqnull apart and adding a second table to establish a relationship between uniqnull values and the entity  rather than having uniqnull  inside  the entity    create table the entity     id integer    primary key id      create table the relation     the entity id integer not null    uniqnull integer not null     unique the entity id     unique uniqnull        primary key can be both or either of the entity id or uniqnull      primary key  the entity id  uniqnull      foreign key  the entity id  references the entity id       To associate a value of uniqnull to a row in the entity we need to also add a row in the relation   For rows in the entity were no uniqnull values are associated  i e  for the ones we would put NULL in the entity incorrect  we simply do not add a row in the relation   Note that values for uniqnull will be unique for all the relation  and also notice that for each value in the entity there can be at most one value in the relation  since the primary and foreign keys on it enforce this   Then  if a value of 5 for uniqnull is to be associated with an the entity id of 3  we need to   start transaction  insert into the entity  id  values  3    insert into the relation  the entity id  uniqnull  values  3  5   commit    And  if an id value of 10 for the entity has no uniqnull counterpart  we only do   start transaction  insert into the entity  id  values  10    commit    To denormalize this information and obtain the data a table like the entity incorrect would hold  we need to   select   id  uniqnull from   the entity left outer join the relation on   the entity id   the relation the entity id     The  left outer join  operator ensures all rows from the entity will appear in the result  putting NULL in the uniqnull column when no matching columns are present in the relation   Remember  any effort spent for some days  or weeks or months  in designing a well normalized database  and the corresponding denormalizing views and procedures  will save you years  or decades  of pain and wasted resources

[sql] How to create a unique index on a NULL column?

Examples related to sql

Examples related to sql-server

Examples related to indexing

Examples related to constraints

Examples related to unique