Keys in SQL – Primary Key, Foreign Key, Unique Key & Composite Key Explained (With Examples & Practice)

In my early days of SQL interview prep, I used to mix up Primary Key, Foreign Key, and Unique Key all the time. Interviewers love asking about keys because they test your understanding of data integrity, relationships, and database design — core concepts for any role involving databases.

Today, we're clearing all the confusion. I'll explain the four main types of keys in SQL:

• Primary Key
• Foreign Key
• Unique Key
• Composite Key

We'll use simple examples from our familiar e-commerce database (users, products, orders) that you can practice on live at sqlpractice.in.

Let's get started!

Why Do We Need Keys?

Keys are special constraints that help maintain:

• Data Integrity (no duplicates, valid relationships)
• Uniqueness (each row identifiable)
• Relationships between tables
• Performance (indexes created automatically)

Without proper keys, your database becomes messy, slow, and error-prone.

1. Primary Key (PK)

Definition: A column (or set of columns) that uniquely identifies every row in a table.

Rules:

• Must be UNIQUE
• Cannot be NULL
• Only one per table

Most common choice: Auto-incrementing ID (SERIAL or BIGSERIAL)

Alternative: Natural key (e.g., email if guaranteed unique)

Why use surrogate key (auto-increment ID)?

• Stable (doesn't change)
• Fast for joins
• Easier when data changes (e.g., user changes email)

Practice Question 1 (Easy) Identify which column should be the Primary Key in a products table. Practice here →

2. Foreign Key (FK)

Definition: A column that creates a relationship with another table's Primary Key.

Purpose: Enforces referential integrity — ensures valid relationships.

Common options:

• ON DELETE CASCADE: Delete orders if user is deleted
• ON DELETE SET NULL: Set user_id to NULL if user deleted
• ON DELETE RESTRICT: Prevent deletion if orders exist (safest)

Practice Question 2 (Medium) What happens if you try to insert an order with non-existent user_id? Practice here →

3. Unique Key

Definition: Ensures all values in a column are unique, but allows NULL (unlike Primary Key).

Use cases:

• Email, phone number, username (should be unique but might be optional)
• Business rules (e.g., only one active coupon per user)

Key Difference from Primary Key:

• Primary Key: Cannot be NULL + only one per table
• Unique Key: Can be NULL + multiple per table

Practice Question 3 (Easy) Add a Unique constraint to prevent duplicate product names. Practice here →

4. Composite Key (Compound Key)

Definition: A Primary Key made of multiple columns together.

When to use:

• No single column is naturally unique
• Common in junction tables (Many-to-Many)