Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Why a Unique Constraint Still Allows Two ‘Duplicate’ Rows When One Column Is NULL

- 27.09.26 - ErcanOPAK

🗄️ The Unique Constraint That Let Two Identical Rows Through Anyway

A unique constraint on a column is expected to guarantee no two rows ever share the same value – but SQL Server (following the ANSI SQL standard) treats NULL as never equal to anything, including another NULL, so a unique constraint on a nullable column allows an unlimited number of rows with NULL in that column, even though every one of them looks like an identical ‘duplicate’ at a glance.

🔎 The Problem

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Email NVARCHAR(255) NULL,
    CONSTRAINT UQ_Employees_Email UNIQUE (Email)
);

INSERT INTO Employees (Id, Email) VALUES (1, NULL);
INSERT INTO Employees (Id, Email) VALUES (2, NULL);
INSERT INTO Employees (Id, Email) VALUES (3, NULL);

-- All three inserts succeed without any constraint violation. The
-- unique constraint is working exactly as the SQL standard defines it -
-- NULL is never considered equal to another NULL, so it never triggers
-- a uniqueness conflict - but from a business standpoint, three
-- employees with no email address on file looks exactly like three
-- "duplicate" rows the constraint was supposed to prevent.

✅ Fix: Decide Explicitly What NULL Should Mean Here

  • If the column should genuinely never allow more than one NULL (or shouldn’t allow NULL at all), making the column `NOT NULL` is the simplest fix – it forces every row to have an actual, comparable value, which restores the uniqueness guarantee people generally expect.
  • If NULL needs to remain allowed but still capped (at most one row without an email, say), a filtered unique index (`CREATE UNIQUE INDEX … WHERE Email IS NOT NULL` handles the non-NULL case, combined with a separate check or a computed column trick for the NULL case) achieves the specific business rule the plain unique constraint alone can’t express.
  • For a case needing ‘no two rows share a value, treating NULL as one specific shared value too,’ a computed column that maps NULL to a specific sentinel value, with the unique constraint applied to THAT computed column instead of the original, sidesteps the standard NULL-comparison behavior entirely.

⚠️ Why This Behavior Is Correct, Not a Bug

  • This isn’t a SQL Server quirk – it’s the ANSI SQL standard’s definition of NULL as ‘unknown,’ and two unknowns are never confirmed to be equal to each other, which is philosophically consistent even though it produces a business result that frequently surprises people expecting ‘unique’ to mean ‘no two rows look the same.’
  • This is worth checking specifically on any column that’s both nullable AND has a uniqueness requirement in the business logic – the two properties coexisting is a common, easy-to-overlook source of ‘duplicate’ data that the database itself was never actually going to catch.

A unique constraint doesn’t promise no duplicates – it promises no two comparable values are equal, and NULL was never on the list of things SQL considers comparable to itself.

— Database Engineer

Related posts:

SQL Date Comparisons Kill Indexes

SQL Date Comparisons Kill Indexes

SQL Server: Fix a Query's Actual Row Count Coming in Far Higher Than Estimated Because a Multi-State...

SQL Server: Fix a Query's Actual Row Count Coming in Far Higher Than Estimated Because a Multi-State...

SQL: Use Stored Procedures for Database Logic

SQL: Use Stored Procedures for Database Logic

Views: 5

Post navigation

Ajax: Fix a Request That Succeeds While the UI Still Shows the Old Error Message
Windows 11: Fix Copy and Paste That Silently Stops Working Until You Restart Explorer

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com