Nullable Unique Constraint

I have a table and want to put a unique constraint on a nullable column.
I don't think I can do this and I think I will have to check for uniqueness
on
an Insert / Update trigger (selecting from the inserted buffer where column
is
not null).
Is there a way to do this with a constraint instead of a trigger?WJ
Thank to 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)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"WJ" <wj@.nospam.com> wrote in message
news:%23FteefWbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> I have a table and want to put a unique constraint on a nullable column.
> I don't think I can do this and I think I will have to check for
uniqueness on
> an Insert / Update trigger (selecting from the inserted buffer where
column is
> not null).
> Is there a way to do this with a constraint instead of a trigger?
>|||You can do it, but nulls are considered equal in this context, so only one
null value will be allowed which may not be what you want.
Alternative is create a view that includes the nullable column but excludes
the rows where this column is null. Then create an index on the view. Either
way is typically better than via trigger.
Mike John
"WJ" <wj@.nospam.com> wrote in message
news:%23FteefWbFHA.3196@.TK2MSFTNGP14.phx.gbl...
>I have a table and want to put a unique constraint on a nullable column.
> I don't think I can do this and I think I will have to check for
> uniqueness on
> an Insert / Update trigger (selecting from the inserted buffer where
> column is
> not null).
> Is there a way to do this with a constraint instead of a trigger?
>|||Another solution is to create view as
SELECT MyUniqueColumn FROM ... WHERE MyUniqueColumn IS NOT NULL
and uniquely index the MyUniqueColumn
HTH,
Gert-Jan
WJ wrote:
> I have a table and want to put a unique constraint on a nullable column.
> I don't think I can do this and I think I will have to check for uniquenes
s on
> an Insert / Update trigger (selecting from the inserted buffer where colum
n is
> not null).
> Is there a way to do this with a constraint instead of a trigger?