MS SQL Server - Unique Key Constraint

Hello friends, welcome to shrash studio learning, in this article we will clearly explain the UNIQUE constraint in SQL Server. At first glance it looks exactly like a primary key — both stop duplicate values. But there are two important differences that show up in every interview: a table can have only one primary key but many unique constraints, and a unique constraint allows one NULL while a primary key allows none. In this short guide we will cover what a unique constraint is, when to use it, how NULL behaves, and how it compares with the primary key.

What Is a UNIQUE Constraint?

A UNIQUE constraint is a rule you attach to a column (or a group of columns) that says "no two rows in this table can have the same value here". If someone tries to insert or update a row that would create a duplicate, SQL Server rejects the statement with an error.

Typical examples — an email address, a phone number, a PAN card number, a username, or an SKU code. These are all fields where a duplicate would be a serious data bug, but they usually aren't the primary key of the table.

Why Primary Key Is Not Enough

Every table can have only one primary key. But real-world tables often have several columns that need to be unique. A Customers table may have a CustomerId as the primary key, but also need Email, PhoneNumber, and PanNumber to be unique. A primary key cannot protect all four — UNIQUE constraints can.

A Small Example

Let's build a Customers table where CustomerId is the primary key and Email, PhoneNumber, and PanNumber are each unique.


CREATE TABLE Customers (
    CustomerId  INT IDENTITY(1, 1) PRIMARY KEY,
    FullName    VARCHAR(100) NOT NULL,
    Email       VARCHAR(100) CONSTRAINT UQ_Customers_Email       UNIQUE,
    PhoneNumber VARCHAR(15)  CONSTRAINT UQ_Customers_Phone       UNIQUE,
    PanNumber   VARCHAR(10)  CONSTRAINT UQ_Customers_PanNumber   UNIQUE
);

Notice — one primary key, three unique constraints. Every one of them enforces its own rule.

How UNIQUE Blocks Duplicates


INSERT INTO Customers (FullName, Email, PhoneNumber, PanNumber)
VALUES ('Anita Sharma', 'anita@example.com', '9876543210', 'ABCDE1234F');
-- OK

INSERT INTO Customers (FullName, Email, PhoneNumber, PanNumber)
VALUES ('Anita Duplicate', 'anita@example.com', '9123456789', 'PQRSX5678Y');
-- Error: Violation of UNIQUE KEY constraint 'UQ_Customers_Email'.
-- Cannot insert duplicate key in object 'Customers'.

The second insert fails because the email already exists. Notice how the error message names the exact constraint that was violated — one more reason to always name your constraints.

Adding UNIQUE to an Existing Table


ALTER TABLE Customers
ADD CONSTRAINT UQ_Customers_Email UNIQUE (Email);

If your existing data already has duplicates, this will fail — SQL Server checks the existing rows before creating the constraint. Clean the duplicates first, then add the constraint.

Composite UNIQUE Constraint

You can also enforce uniqueness across a combination of columns. The combination must be unique, but each column alone is free to repeat.


CREATE TABLE Enrollments (
    StudentId INT,
    CourseId  INT,
    EnrollDate DATE,
    CONSTRAINT UQ_Enrollments UNIQUE (StudentId, CourseId)
);

A student can be in many courses, a course can have many students, but the same student cannot enroll in the same course twice. One constraint, two columns.

The NULL Rule: One and Only One

Key rule: A UNIQUE constraint in SQL Server allows exactly one NULL value. A second NULL counts as a duplicate and is rejected. This is different from most other database engines, which allow many NULLs.


INSERT INTO Customers (FullName, Email) VALUES ('Rohit Verma', NULL);  -- OK
INSERT INTO Customers (FullName, Email) VALUES ('Priya Das',  NULL);  -- Fails
-- Error: Violation of UNIQUE KEY constraint 'UQ_Customers_Email'.

If your column can have many rows where the value is "not provided yet", a standard UNIQUE constraint will not work. In SQL Server you can use a filtered unique index instead, which ignores NULLs entirely.


CREATE UNIQUE INDEX UX_Customers_Email
    ON Customers (Email)
    WHERE Email IS NOT NULL;

With this filtered unique index, many rows can have Email = NULL, but every non-null email must still be unique.

Dropping a UNIQUE Constraint


ALTER TABLE Customers DROP CONSTRAINT UQ_Customers_Email;

  Naming tip: Skip the name and SQL Server will auto-generate something like UQ__Customers__A9D105349C8AE54A — painful to drop later. Always name your constraints UQ_Table_Column.

UNIQUE vs PRIMARY KEY: The Classic Comparison

Feature	Primary Key	Unique Constraint
Enforces uniqueness	Yes	Yes
How many per table?	Only one	Many
Allows NULL?	No — never	Yes — exactly one NULL
Default index type	Clustered (usually)	Non-clustered
Meaning	Identifies each row	Enforces a uniqueness rule on a column
Can be referenced by FK?	Yes	Yes

A helpful way to remember: think of primary key as your "main identity", and unique constraints as additional promises about specific columns.

Common Mistakes

Mistake 1: Using a primary key when a unique constraint is enough. If the column isn't meant to identify the row, use UNIQUE. Save the primary key for the real identifier.

Mistake 2: Trying to add a UNIQUE constraint on a column with existing duplicates. SQL Server refuses. Clean the data first, or replace the duplicates with unique values.

Mistake 3: Assuming many NULLs are allowed. A standard UNIQUE constraint permits only one NULL. Use a filtered unique index if you need more.

Mistake 4: Forgetting to name the constraint. Auto-generated names are unreadable and hard to drop. Always use the CONSTRAINT name syntax.

Mistake 5: Expecting UNIQUE to work across rows in different tables. UNIQUE is table-scoped. If you need a value to be unique across multiple tables, consider a shared sequence, a lookup table, or a GUID.

Real-World Use Cases

Where	Why UNIQUE Helps
User email / username	Prevents accidental duplicate accounts
PAN / Aadhaar / SSN	Government IDs must be unique per person
SKU / Barcode	Same product code should never repeat
Slug / URL path	Each post or page must have a unique slug
Enrollment (StudentId + CourseId)	Composite UNIQUE — no double registration

The Interview Answer

If an interviewer asks "What is the difference between a PRIMARY KEY and a UNIQUE constraint?":

"Both enforce uniqueness, but a table can have only one primary key while it can have many unique constraints. A primary key does not allow any NULL values, while a unique constraint allows exactly one NULL. The primary key identifies each row — unique constraints simply guarantee that a specific column never has duplicates. Both automatically create an index behind the scenes."

Summary

A UNIQUE constraint is how you protect any column (or set of columns) from duplicates without turning it into the primary key. You can have as many unique constraints on a table as you need — one for Email, one for PhoneNumber, one for PanNumber, and so on.

Remember the two rules that matter in interviews — one primary key per table, but many unique constraints, and only one NULL allowed per unique constraint. If you need to allow many NULLs, switch to a filtered unique index. Name every constraint clearly (like UQ_Customers_Email) so you can manage them without pain as your schema grows.

Concept	Key Takeaway
UNIQUE constraint	Rejects duplicate values in a column or set of columns
How many per table	As many as you need
NULL rule	Exactly one NULL allowed
Add at creation	`CONSTRAINT name UNIQUE` inside `CREATE TABLE`
Add later	`ALTER TABLE ADD CONSTRAINT name UNIQUE (col)`
Composite	`UNIQUE (col1, col2)` — pair must be unique
Many NULLs allowed?	Use a filtered unique index with `WHERE col IS NOT NULL`
Drop	`ALTER TABLE DROP CONSTRAINT name`
vs Primary Key	Many per table, one NULL allowed, no identity role
Interview line	"Many per table, one NULL allowed"

License Key