SkillsetMaster - AI, Web Development & Data Analytics Courses

What You Will Learn

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

What UNIQUE means (super simple explanation)
When to use it
How it differs from Primary Key
Easy examples

What is UNIQUE?

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

UNIQUE means "no duplicates allowed". It ensures that every value in a column is different.

Think of it like:

Email addresses (no two users can have same email)
Usernames (each person gets unique username)
Phone numbers (each number belongs to one person)

Real-Life Examples

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Email Addresses:

john@email.com is taken by John
Mary cannot use john@email.com
She must use mary@email.com

Usernames:

Username "johndoe" is taken
Next person must choose different username
No two people can be "johndoe"

License Plates:

Each car has unique plate number
Cannot have two cars with ABC123

How to Use UNIQUE

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Example 1: User Registration

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

What this means:

user_id is Primary Key (unique ID)
username must be unique (no duplicate usernames)
email must be unique (no duplicate emails)

Example 2: Inserting Data

-- First user
INSERT INTO users (username, email)
VALUES ('johndoe', 'john@email.com');  -- OK

-- Second user with different username and email
INSERT INTO users (username, email)
VALUES ('maryjane', 'mary@email.com');  -- OK

-- Third user tries to use same username
INSERT INTO users (username, email)
VALUES ('johndoe', 'peter@email.com');  -- ERROR: username already exists

-- Third user tries to use same email
INSERT INTO users (username, email)
VALUES ('petersmith', 'john@email.com');  -- ERROR: email already exists

UNIQUE vs Primary Key

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Both prevent duplicates, but they are different:

Primary Key:

Identifies each row
Cannot be NULL
Only ONE per table
Example: student_id, order_id

UNIQUE:

Prevents duplicates in a column
CAN be NULL
Can have MULTIPLE unique columns per table
Example: email, username, phone

Table Comparison

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,      -- Only one Primary Key
  username VARCHAR(50) UNIQUE,     -- Can have multiple UNIQUE
  email VARCHAR(100) UNIQUE,       -- Can have multiple UNIQUE
  phone VARCHAR(20) UNIQUE         -- Can have multiple UNIQUE
);

Real-World Table Examples

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Example 1: Product Catalog

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  sku VARCHAR(50) UNIQUE,
  product_name VARCHAR(200)
);

SKU (Stock Keeping Unit) must be unique for each product.

Example 2: Employee Records

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  employee_number VARCHAR(20) UNIQUE
);

Both email and employee number must be unique.

Example 3: Social Media

CREATE TABLE social_accounts (
  account_id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

No two accounts can have same username or email.

When to Use UNIQUE

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Use UNIQUE when:

Values must be different for each row
Like email, username, phone number
Like product codes, invoice numbers
Like license plates, serial numbers

Do not use UNIQUE when:

Values can repeat
Like names (many people named John)
Like ages (many people aged 20)
Like cities (many people from New York)

UNIQUE with NULL

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

UNIQUE allows NULL values (empty values):

CREATE TABLE contacts (
  contact_id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  phone VARCHAR(20) UNIQUE
);

-- These all work (phone can be NULL):
INSERT INTO contacts (name, phone) VALUES ('John', '555-0001');  -- OK
INSERT INTO contacts (name, phone) VALUES ('Mary', NULL);        -- OK
INSERT INTO contacts (name, phone) VALUES ('Peter', NULL);       -- OK

-- This fails (duplicate phone):
INSERT INTO contacts (name, phone) VALUES ('Sarah', '555-0001'); -- ERROR

Multiple NULL values are allowed because NULL means "no value" or "unknown".

Common Mistakes

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Mistake 1: Duplicate Values

Wrong:

INSERT INTO users (username) VALUES ('john123');
INSERT INTO users (username) VALUES ('john123');  -- ERROR: Duplicate

Correct:

INSERT INTO users (username) VALUES ('john123');
INSERT INTO users (username) VALUES ('john456');  -- Different username

Mistake 2: Forgetting UNIQUE on Important Fields

Wrong:

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  email VARCHAR(100)  -- No UNIQUE!
);
-- Now two users can have same email (bad!)

Correct:

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  email VARCHAR(100) UNIQUE  -- Cannot duplicate
);

Summary

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Think of UNIQUE like:

Your email address (unique to you)
Your phone number (unique to you)
Your username on a website (unique to you)
Barcode on a product (unique to that product)

Key points:

UNIQUE prevents duplicate values
Like ensuring no two people have same email
Can have multiple UNIQUE columns
Allows NULL values (unlike Primary Key)
Different from Primary Key
Use for emails, usernames, codes
Protects data quality
Prevents confusion from duplicates

What Comes Next

Users Table with UNIQUE Constraints

user_id	username (UNIQUE)	email (UNIQUE)
1	johndoe	john@email.com
2	maryjane	mary@email.com
3	petersmith	peter@email.com

3 rows

Primary Key vs UNIQUE

Feature	Primary Key	UNIQUE
Allows duplicates	No	No
Allows NULL	No	Yes
How many per table	Only 1	Multiple
Purpose	Identify row	Prevent duplicates
Example	student_id	email, username

5 rows

Next, you will learn about NOT NULL constraint to ensure required fields always have values.

UNIQUE Constraint

What You Will Learn

What is UNIQUE?

Real-Life Examples

How to Use UNIQUE

Example 1: User Registration

Example 2: Inserting Data

UNIQUE vs Primary Key

Table Comparison

Real-World Table Examples

Example 1: Product Catalog

Example 2: Employee Records

Example 3: Social Media

When to Use UNIQUE

UNIQUE with NULL

Common Mistakes

Mistake 1: Duplicate Values

Mistake 2: Forgetting UNIQUE on Important Fields

Summary

What Comes Next

Finished this topic?