Re: Unique Constraint with Multiple NULLS

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Thu, 1 Jan 2004 22:14:11 -0000
Message-ID: <mb-dnTaVw7iNBmmiRVn-vg@giganews.com>

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

---

Please reply only to the newsgroup
--
Received on Thu Jan 01 2004 - 16:14:11 CST