SQL Unique Constraint allows violation with nulls

Question

I have a table that has a Non-clustered, Unique index on 3 columns, "MenuId", "Name", and "ParentId".

The problem is, I am able to insert multiple rows into this table that violate the unique constraint - when ParentId is set to NULL as you can see below.

If I try to add duplicate rows and ParentId is not null, the unique constraint works as expected.

My understanding of unique indexes is that it would only allow a single unique combination of the columns participating in the index, so I would expect in the image below to have been able to instert the first row but it should have thrown an exception for violating the index when inserting the second (and third) row - but it doesn't.

Where am I going wrong?

I am using SQL LocalDb.

Update: I am using EF Core to generate the database from code and it seems that in this case, the Unique Constraint is being created with a WHERE clause to ignore when ParentId or Name is NULL:

CREATE UNIQUE NONCLUSTERED INDEX [IX_MenuItem_MenuId_ParentId_Name]
    ON [dbo].[MenuItem]([MenuId] ASC, [ParentId] ASC, [Name] ASC) 
WHERE ([ParentId] IS NOT NULL AND [Name] IS NOT NULL);

Embaressingly, when I was exploring the Unique constraint in SQL Server Explorer properties window, this WHERE clause wasn't apparant - I simply checked the Unique constraint existed, and that the columns I expected where included - however that wasn't enough. Thank you to the commentor below who couldn't reproduce and made me actually inspect the script for the index where this WHERE clause became apparant. I have now moved my question to one relating to why EF produces this in my case: https://github.com/aspnet/EntityFrameworkCore/issues/17586

Answer 1

Thanks to @蕭為元 for prompting me to look at the Unique constraint in more detail, and not just in the properties window!

The UNIQUE index has a WHERE clause that was causing it to ignore records that has NULL values for Parent ID or Name:

CREATE UNIQUE NONCLUSTERED INDEX [IX_MenuItem_MenuId_ParentId_Name]
    ON [dbo].[MenuItem]([MenuId] ASC, [ParentId] ASC, [Name] ASC) 
WHERE ([ParentId] IS NOT NULL AND [Name] IS NOT NULL);

This was generated by EF - and that's the type of problem you hit when relying on ORM's to do stuff for you and assume they do what you want - slap myself on wrist