Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums SQL Server UNIQUE constraint with duplicate NULLs – MindStick
Ask Question

1 Answers

Khushi Singh
Khushi Singh 08 Jan 2025
Report Abuse

In Data Manipulation, the UNIQUE constraint in SQL Server will restrict the records such that no two records have similar values either in this column or in one or many other columns. Nonetheless, SQL Server permitting duplication of NULL in a column that has UNIQUE constraint. This behavior is that SQL Server operates NULL as the unknown value and the two NULLs are not equal.

Behavior of UNIQUE Constraint with NULLs

  • If a column has UNIQUE constraint, multiple NULL values can be stored in this column because SQL Server does not support uniqueness constraint for NULL.
  • Nonetheless, any value in that column cannot be NULL if other similar values already exist or are present in the database.

Example:

-- Create a table with a UNIQUE constraint
CREATE TABLE Employee (
   EmployeeID INT,
   Email NVARCHAR(100) UNIQUE
);
-- Insert values
INSERT INTO Employee (EmployeeID, Email) VALUES (1, 'john@example.com'); -- Valid
INSERT INTO Employee (EmployeeID, Email) VALUES (2, 'jane@example.com'); -- Valid
INSERT INTO Employee (EmployeeID, Email) VALUES (3, NULL);               -- Valid
INSERT INTO Employee (EmployeeID, Email) VALUES (4, NULL);               -- Valid (allows duplicate NULLs)

-- Attempt to insert a duplicate non-NULL value
INSERT INTO Employee (EmployeeID, Email) VALUES (5, 'john@example.com'); -- Error: Violation of UNIQUE constraint

Hope it helps!!
 


 

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md