Unique Index That Allows NULLs

[cfaulkner]

How would you create a unique index or constraint that allows NULLs? I need to insure that the value entered into one colum is unique, if it is entered. Bad design I know, I just have to fix an issue with a db that someone else put together.

Thanks!

Craig

 

[dodge20]

I am not positive on the syntax, but most sql versions use this

CREATE UNIQUE INDEX CustomerIndex ON Customers (Name)
WITH IGNORE NULL;

IT may be different in TSQL, I have never done this on it.

Dodge20

 

[JamesLean]

Firstly, it is recommended that you create a constraint rather than an index to enforce uniqueness:

ALTER TABLE table_name
ADD CONSTRAINT const_name UNIQUE (col_name)

Having said that, both methods will only allow one instance of NULL in the column, you cannot change this.

I would suggest using a trigger to enforce the integrity you need.

--James

 

[cfaulkner]

James -

As I thought. Thank you for the confirmation.

Craig