SQL UNIQUE

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

The UNIQUE constraint ensures all values in a column (or group of columns) are distinct — no duplicates allowed.

🔹 Basic Syntax

-- Single column UNIQUE constraint
column_name data_type UNIQUE;

-- Multiple columns (composite unique key)
UNIQUE (column1, column2);

🔹 Example

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE,       -- no two users can have the same email
  username VARCHAR(50),
  UNIQUE (username)                -- usernames must also be unique
);

-- Inserting unique values
INSERT INTO users (user_id, email, username) VALUES (1, 'alice@example.com', 'alice');

-- Trying duplicate email will fail
-- INSERT INTO users (user_id, email, username) VALUES (2, 'alice@example.com', 'alice2'); -- ERROR

-- Trying duplicate username will fail
-- INSERT INTO users (user_id, email, username) VALUES (3, 'bob@example.com', 'alice'); -- ERROR

🔹 Important Notes

• UNIQUE allows NULL values but behavior can vary by DBMS (some allow multiple NULLs, others don’t).
• A table can have multiple UNIQUE constraints on different columns.
• UNIQUE constraints create an index automatically for performance.
• Use UNIQUE to enforce business rules like unique emails, usernames, or product codes.

🧠 Quick Recap

💡 Use UNIQUE to guarantee no duplicate values in critical columns and improve query speed.