SQL Server UNIQUE constraint with duplicate NULLs

Question

Khushi Singh · Answer

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.
and 
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!!
and 
and 
and 
and 
and 
and 
and 
and 
and 
and 
and 
and 
and 
and 
and

forum

SQL Server UNIQUE constraint with duplicate NULLs

Revati S Misra

Can you answer this question?

1 Answers

Liked By