What is UNIQUE?
UNIQUE means "no duplicates allowed". Each value in the column must be different.
Simple rule: No two rows can have the same value in a UNIQUE column.
How UNIQUE Works
Basic Syntax
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);Example 1: Create Table with UNIQUE
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);Example 2: Insert Unique Values
INSERT INTO users (username, email) VALUES ('john', 'john@email.com');
INSERT INTO users (username, email) VALUES ('mary', 'mary@email.com');
-- This fails: INSERT INTO users (username, email) VALUES ('john', 'new@email.com');Example 3: Query Users
SELECT * FROM users WHERE username = 'john';UNIQUE vs Primary Key
| Feature | Primary Key | UNIQUE | |---------|-------------|--------| | Duplicates | No | No | | NULL allowed | No | Yes | | Per table | Only 1 | Multiple |
Try It Below
Use the playground to practice:
SELECT * FROM users;SELECT * FROM users WHERE username = 'john';
What Comes Next
Next: Learn NOT NULL to ensure required fields always have values.
Try UNIQUE
View all users. Each username and email is unique!
Source Table: students
| user_id | username | |
|---|---|---|
| 1 | john | john@email.com |
| 2 | mary | mary@email.com |
| 3 | peter | peter@email.com |
3 rows
SQL Editor
Loading...
Find by Username
Find a specific user by their unique username.
Source Table: students
| user_id | username | |
|---|---|---|
| 1 | john | john@email.com |
| 2 | mary | mary@email.com |
| 3 | peter | peter@email.com |
3 rows
SQL Editor
Loading...
Find by Email
Find user by their unique email address.
Source Table: students
| user_id | username | |
|---|---|---|
| 1 | john | john@email.com |
| 2 | mary | mary@email.com |
| 3 | peter | peter@email.com |
3 rows
SQL Editor
Loading...