Re: Unique Constraint with Multiple NULLS

In SQLServer 2000 you can use an indexed view to achieve this.

CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)

GO

CREATE VIEW Sometable_Unique_Non_NULL
 WITH SCHEMABINDING
 AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL

GO

CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)

INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)

--

David Portas