How can make Column that allow null to be unique from SQL Server 2005 ?

Question

I have column in a table that allow null but i want to make constraint that make him null or unique ... How can i do that ?
Note : I validate it from the frontend but i want to have a physical in sql server that allow this even developer try to enter data

Answer 1

You cannot do this in SQL Server 2005, unfortunately. A unique index only allows one row of NULL value.

With SQL Server 2008, you could use a filtered index - an index that is defined on only parts of the rows, e.g. those that are NOT NULL. This is a 2008 feature, so you won't be able to do this in 2005.

CREATE UNIQUE NONCLUSTERED INDEX IX_UNIQUE
ON dbo.YourTable(YourColumn)
WHERE YourColumn IS NOT NULL
GO

See:

• Introduction to Filtered Index in SQL Server 2008
• Filtered Indexes in SQL Server 2008

Answer 2

being an oracle developer i might be going out on a limb here so bare with me.

you can't make a nullable column unique via a normal unique constraint on that column, but you might implement the constraint via a function based index, don't know if such a contruct exists on sql server but that might solve it on an oracle database

Answer 3

An alternative to a filtered index that works on SQL Server 2000 and later is an indexed view.

Something like:

CREATE VIEW DRI_YourTable_NonNullUnique
WITH SCHEMABINDING
AS
    SELECT YourColumn FROM dbo.YourTable WHERE not YourColumn is NULL
GO
CREATE UNIQUE CLUSTERED INDEX IX_DRI_YourTable on DRI_YourTable_NonNullUnique (YourColumn)

Note that you don't have to do anything special once this view is created - you need never refer to it again. It will simply cause constraint violations to occur, if there is an attempt to insert non-unique values in the base table.

@marc_s - I beg to differ. They're certainly not considered automatically (in query compilation) in editions below Enterprise, but they're definitely creatable, and work as DRI enforcers in all editions:

select @@VERSION

create table dbo.T1 (
    T1ID int IDENTITY(1,1) not null,
    Val1 varchar(10) null,
    constraint PK_T1 PRIMARY KEY (T1ID)
)
go
create view dbo.DRI_T1_Val1Unique
with schemabinding
as
    select Val1 from dbo.T1 where Val1 is not null
go
create unique clustered index IX_DRI_T1_Val1Unique on dbo.DRI_T1_Val1Unique (Val1)
go
insert into dbo.T1 (Val1)
select 'abc' union all
select null union all
select null
go
insert into dbo.T1 (Val1)
select 'abc'
go
select * from dbo.T1

Results:

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

1   abc
2   NULL
3   NULL

And:

(1 row(s) affected)

(3 row(s) affected)
Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'DRI_T1_Val1Unique' with unique index 'IX_DRI_T1_Val1Unique'.
The statement has been terminated.

(0 row(s) affected)

(3 row(s) affected)