How to add a unique constraint for 2 nullable columns?

Question

Who knows how to create unique constraint for set of 2 columns with NULLS allowed in both columns? I mean ignore uniqueness for col1=NULL AND col2=NULL, but let constraint work in case of only one of them s NULL? I found how to do it for single column (How do I create a unique constraint that also allows nulls?), but can not make it for 2 columns. Here is my script which works for existing records (multiple NULL, NULL is allowed), but does not let add any new NULL, NULL record:

CREATE UNIQUE NONCLUSTERED INDEX MyIndex ON dbo.MyTable(col1, col2)     
WHERE col1 IS NOT NULL AND col2 IS NOT NULL

UPDATE: Well, it lets you add new (NULL, NULL) values so I was wrong, however it also lets you add something like (1, NULL), (1, NULL) which I would NOT like to be, so uniqueness should work for this case, only multiple (NULL, NULL) should be allowed. How to do this?

Answer 1

Something else surely is not working then. Your code/script works as expected:

CREATE TABLE #MyTable
(
    Col1 INT NULL,
    Col2 INT NULL
)

CREATE UNIQUE NONCLUSTERED INDEX MyIndex ON #MyTable(col1, col2)     
WHERE col1 IS NOT NULL AND col2 IS NOT NULL

INSERT INTO #MyTable(Col1, Col2)
VALUES(NULL, NULL) --Works

INSERT INTO #MyTable(Col1, Col2)
VALUES(NULL, NULL) --Works

SELECT * FROM #MyTable

INSERT INTO #MyTable(Col1, Col2)
VALUES(1, 1) --Works

INSERT INTO #MyTable(Col1, Col2)
VALUES(1, 1) --Fails

SELECT * FROM #MyTable

DROP TABLE #MyTable

Answer 2

If you want the values to be unique except when both are null, that implies that you want them to be unique as long as at least one value is not null. Your filter on the index has an AND, but you need an OR.

CREATE UNIQUE NONCLUSTERED INDEX MyIndex ON dbo.MyTable(col1, col2)     
WHERE col1 IS NOT NULL OR col2 IS NOT NULL -- (note: doesn't work)

However... you can't do this. SQL Server will only let you create a where clause with AND conditions. You also can't use a persisted computed column to do it.

Instead, what you can do is create an indexed view on the original table and then put the unique index on that. It's a bit heavyweight, but it should work.

So... to borrow @Meff's script, you're looking at something like:

CREATE TABLE dbo.MyTable
(
    Col1 INT NULL,
    Col2 INT NULL
)
GO
CREATE VIEW dbo.MyTableUniqueView WITH SCHEMABINDING AS
SELECT Col1, Col2 FROM dbo.MyTable
WHERE Col1 IS NOT NULL OR Col2 IS NOT NULL 
GO
CREATE UNIQUE CLUSTERED INDEX MyTableUniqueIndex
ON dbo.MyTableUniqueView(Col1, Col2)
GO
INSERT INTO MyTable(Col1, Col2)
VALUES(NULL, NULL) --Works

INSERT INTO MyTable(Col1, Col2)
VALUES(NULL, NULL) --Works

SELECT * FROM MyTable

INSERT INTO MyTable(Col1, Col2)
VALUES(1, 1) --Works

INSERT INTO MyTable(Col1, Col2)
VALUES(1, 1) --Fails

INSERT INTO MyTable(Col1, Col2)
VALUES(1, null) --Works

INSERT INTO MyTable(Col1, Col2)
VALUES(1, null) --Fails

SELECT * FROM MyTable
GO
DROP VIEW MyTableUniqueView
DROP TABLE MyTable