🔒 SQL UNIQUE Constraint – Ensure Unique Values in a Column

Want to make sure that no duplicate values sneak into your database? The SQL UNIQUE constraint guarantees that every value in a column (or combination of columns) is distinct across all records.

This guide explains what the UNIQUE constraint is, how it works, and where to use it for best data integrity.

---

📘 What is a UNIQUE Constraint in SQL?

The UNIQUE constraint ensures that the values in a column (or a group of columns) are not duplicated in any row of the table. It helps enforce data integrity by rejecting duplicate entries.

🔁 Difference Between UNIQUE and PRIMARY KEY

Feature	PRIMARY KEY	UNIQUE
Allows NULL values?	❌ No	✅ Yes (one or more NULLs allowed)
Number per table	Only one	Multiple allowed
Used for joins?	Frequently used in joins	Not always used

---

🧾 SQL UNIQUE Constraint Syntax

✅ Adding UNIQUE in CREATE TABLE:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

✅ Composite UNIQUE Constraint:

CREATE TABLE employees (
  emp_id INT,
  dept_id INT,
  CONSTRAINT unique_emp_dept UNIQUE (emp_id, dept_id)
);

✅ Adding UNIQUE with ALTER TABLE:

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

---

🎯 Use Cases of SQL UNIQUE

• Ensuring email addresses are unique in a user table
• Preventing duplicate phone numbers in a contacts list
• Enforcing a unique combination of columns (e.g., emp_id + dept_id)

---

🧠 Real-World Example

Let’s say you’re building a login system. You want to make sure no two users register with the same email:

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  password_hash TEXT
);

If someone tries to sign up with an email that already exists, the database will block the insertion and throw a unique constraint violation error.

---

⚠️ Things to Keep in Mind

• A column with a UNIQUE constraint can have NULL values (unless it’s also NOT NULL).
• You can define multiple unique constraints on a single table.
• UNIQUE is automatically indexed in most databases (PostgreSQL, MySQL, SQL Server).
• Adding a UNIQUE constraint to a column with duplicate values will result in an error.

---

📝 Summary

• The UNIQUE constraint ensures distinct values in one or more columns.
• It’s ideal for fields like email, username, mobile number, etc.
• You can have multiple UNIQUE constraints per table.
• Unlike PRIMARY KEY, it allows null values (though duplicates are not allowed).