One way to constrain this is with an indexed view:


use tempdb
go

create table dbo.passports (passport varchar(10))
go

--"unique" indexed view
create view un_passports
with schemabinding
as
select passport
from dbo.passports
where passport is not null
--only get passports that are not null
go

--create the unique index
create unique clustered index ix_un_passports on un_passports(passport)
go

--insert some nulls, no problem
insert passports values (null)
insert passports values (null)
go

--insert unique passports, no problem
insert passports values ('abc')
insert passports values ('def')
go

--this will fail
insert passports values ('abc')
go

--clean up
drop view un_passports
drop table passports
go




--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--


[quoted text, click to view]

I would like to create a UNIQUE index but ignoring NULLs.

i.e. the field can contain a unique passport number or else nothing if the
user does not know it.

Is it possible?