🔐 SQL UNIQUE – Enforce Distinct Column Values

🧲 Introduction – What is SQL UNIQUE?

The UNIQUE constraint in SQL is used to ensure all values in a column (or set of columns) are distinct across rows. It helps maintain data integrity and prevent duplicates.

🎯 In this guide, you’ll learn:

• How to apply the UNIQUE constraint
• Single-column vs multi-column unique indexes
• Differences from PRIMARY KEY

---

✅ 1. Basic UNIQUE Constraint Syntax

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

✅ Ensures no two users have the same email address.

---

🧱 2. Add UNIQUE with ALTER TABLE

ALTER TABLE customers
ADD CONSTRAINT unique_email UNIQUE (email);

✅ Adds a named constraint after table creation.

---

🧩 3. Composite UNIQUE Constraint

CREATE TABLE bookings (
  room_id INT,
  booking_date DATE,
  UNIQUE (room_id, booking_date)
);

✅ Prevents double-booking a room on the same date.

---

🆚 4. UNIQUE vs PRIMARY KEY

Feature	PRIMARY KEY	UNIQUE Constraint
Allows NULLs	❌ No	✅ Yes (one or more)
Max per table	1	Multiple allowed
Implies index	✅ Yes	✅ Yes

✅ Use PRIMARY KEY for entity identity, UNIQUE for alternate keys.

---

⚠️ 5. Behavior Notes

• Attempting to insert a duplicate value triggers an error:

-- Error:
INSERT INTO users (email) VALUES ('test@example.com');  -- if already exists

• Can be added inline or at the end of a CREATE TABLE block
• Index is automatically created to enforce uniqueness

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use UNIQUE for natural keys (email, username)	Overusing UNIQUE on optional data
Name your constraints explicitly	Relying on auto-named constraints
Use composite keys wisely	Assuming uniqueness applies to parts only

---

📌 Summary – Recap & Next Steps

The UNIQUE constraint is an essential tool for preserving data integrity by ensuring that certain column values remain distinct. It supports both single and composite key enforcement.

🔍 Key Takeaways:

• Prevents duplicate values in one or more columns
• Allows NULLs (unlike PRIMARY KEY)
• Ideal for alternate keys and natural identifiers

⚙️ Real-World Relevance:
Used in email validation, registration systems, order codes, and inventory tracking.

---

❓ FAQ – SQL UNIQUE

❓ What does the UNIQUE constraint do?

✅ Ensures all values in the column(s) are distinct.

❓ Can I have multiple UNIQUE constraints on one table?

✅ Yes. Each one can apply to different column(s).

❓ Can UNIQUE columns be NULL?

✅ Yes. But only one NULL is allowed in most databases.

❓ How is UNIQUE different from PRIMARY KEY?

✅ PRIMARY KEY = 1 per table, no NULLs. UNIQUE = multiple allowed, NULLs permitted.

---

Share Now :