UNIQUE INDEX if not NULL

[anselme]

Hello !

for MS SQL 2000
how can i set an unique index on Serial column but only if Serial IS NOT NULL

CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY]

i can have 100 rows with a NULL Serial


thank you

[rmiao]

Don't really understand your question. You can only create unique index on column that doesn't allow null.

[anselme]

but i need a column allowing NULL, and if the value IS NOT NULL it must be UNIQUE

[rmiao]

Can't do it with unique index, may need trigger to enforce that.

[anselme]

thank you, i shall do it from outside (.NET)