Sqlserver how to create composite unique key with one of column is nullable

Question

i am not expert with indexing. I would like to create a composite key unique constraint. How to create if one of the column is nullable?

CREATE UNIQUE CLUSTERED INDEX [IX_User_Email_PeronsId] ON [dbo].[User]
(
    [Email] ASC,
    [PersonId] ASC
)
GO

PersonId is nullable column

Does this answer your question? [duplicate null value violation on UNIQUE KEY constraint in Mssql](https://stackoverflow.com/questions/31947263/duplicate-null-value-violation-on-unique-key-constraint-in-mssql) — GuidoG, Jan 12 '23 at 15:02
A column being `NULL`able doesn't stop the creation of a `UNIQUE` index, so what is the actual problem here? — Thom A, Jan 12 '23 at 15:03

Andreas · Accepted Answer · 2023-01-17T07:35:23.877

In fact you can create a unique clustered index with nullable columns, just tried it:

USE tempdb;

CREATE TABLE dbo.[User]
(
    Email    nvarchar(50) NOT NULL,
    PersonID int NULL
);

CREATE UNIQUE CLUSTERED INDEX [IX_User_Email_PersonID] 
ON dbo.[User] 
( 
    Email, 
    PersonID 
);

Commands completed successfully.

You didn't mention what exactly you are trying to achieve, so let me have a guess. I think you want to achieve, that the combination of Email and PersonID has to be unique, except for the rows where PersonID is null.

In this case, using a clustered index is not useful, but you can use a filtered nonclustered index:

USE tempdb;

-- Create the test table
CREATE TABLE dbo.[User]
(
    Email    nvarchar(50) NOT NULL,
    PersonID int NULL
);

-- Create a filtered unique index
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_Email_PersonID] 
ON dbo.[User] 
( 
    Email, 
    PersonID 
)
WHERE PersonID IS NOT NULL;

-- Insert test data
INSERT INTO dbo.[User]
(
    Email,
    PersonId
)
VALUES
( N'a@mydomain.com', 1 ), 
( N'b@mydomain.com', 2 ), 
( N'b@mydomain.com', 3 ), 
( N'c@mydomain.com', 3 ), 
( N'c@mydomain.com', 4 ), 
( N'd@mydomain.com', NULL ), 
( N'e@mydomain.com', NULL ), 
( N'f@mydomain.com', NULL );

Test whether you can insert which data:

-- Works
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'c@mydomain.com', 5 ); 

-- Fails
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'c@mydomain.com', 5 ); 

-- Works
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'f@mydomain.com', NULL ); 

-- Works
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'f@mydomain.com', NULL );

Content of the table after step-by-step execution:

| Email             | PersonID |
| ----------------- | -------- |
| a@mydomain.com    | 1        |
| b@mydomain.com    | 2        |
| b@mydomain.com    | 3        |
| c@mydomain.com    | 3        |
| c@mydomain.com    | 4        |
| d@mydomain.com    | NULL     |
| e@mydomain.com    | NULL     |
| f@mydomain.com    | NULL     |
| c@mydomain.com    | 5        |
| f@mydomain.com    | NULL     |
| f@mydomain.com    | NULL     |

Sqlserver how to create composite unique key with one of column is nullable

1 Answers1