Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create a unique index on a NULL column?

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?

like image 351
Nuno G Avatar asked Oct 10 '08 14:10

Nuno G


People also ask

Can you create a unique index on NULLable column?

PostgreSQL treats NULL as distinct value, therefore, you can have multiple NULL values in a column with a UNIQUE index . When you define a primary key or a unique constraint for a table, PostgreSQL automatically creates a corresponding UNIQUE index.

Can a unique index be null?

Although null values represent unknown values, when it comes to indexing, a null value is treated as being equal to other null values. Therefore, if a unique index consists of a single column, only one null value is allowed-more than one null value would violate the unique constraint.

Can null be in unique key?

Key Differences Between Primary key and Unique key: Primary key will not accept NULL values whereas Unique key can accept NULL values. A table can have only one primary key whereas there can be multiple unique key on a table.


2 Answers

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.

like image 176
Phil Haselden Avatar answered Sep 23 '22 18:09

Phil Haselden


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) ) 
like image 28
onedaywhen Avatar answered Sep 20 '22 18:09

onedaywhen