Field value must be unique unless it is NULL

Question

I'm using SQL Server 2005.

I have a field that must either contain a unique value or a NULL value. I think I should be enforcing this with either a CHECK CONSTRAINT or a TRIGGER for INSERT, UPDATE.

Is there an advantage to using a constraint here over a trigger (or vice-versa)? What might such a constraint/trigger look like?

Or is there another, more appropriate option that I haven't considered?

Answer 1

A constraint is far lighter than a trigger, even though a unique constraint is effectively an index.

However, you are only allowed one NULL in a unique constraint/index. So, you'll have to use a trigger to detect duplicates.

It's been requested from MS to ignore NULLS, but SQL 2008 has filtered indexes (as mentioned while I type this)