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

Answer 1

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'[email protected]', 1 ), 
( N'[email protected]', 2 ), 
( N'[email protected]', 3 ), 
( N'[email protected]', 3 ), 
( N'[email protected]', 4 ), 
( N'[email protected]', NULL ), 
( N'[email protected]', NULL ), 
( N'[email protected]', NULL );

Test whether you can insert which data:

-- Works
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'[email protected]', 5 ); 

-- Fails
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'[email protected]', 5 ); 

-- Works
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'[email protected]', NULL ); 

-- Works
INSERT INTO dbo.[User] ( Email, PersonId )
VALUES ( N'[email protected]', NULL ); 

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

| Email             | PersonID |
| ----------------- | -------- |
| [email protected]    | 1        |
| [email protected]    | 2        |
| [email protected]    | 3        |
| [email protected]    | 3        |
| [email protected]    | 4        |
| [email protected]    | NULL     |
| [email protected]    | NULL     |
| [email protected]    | NULL     |
| [email protected]    | 5        |
| [email protected]    | NULL     |
| [email protected]    | NULL     |