What is a Composite Key in SQL?

A Composite Key in SQL is a key made using two or more columns together to uniquely identify a row in a table.

In simple words:

When a single column is not enough to uniquely identify records, multiple columns are combined to form a composite key.

Why Composite Key is Needed

Sometimes:

No single column contains unique values

But:

Combination of multiple columns becomes unique

In such cases:

Composite Key is used.

Real-Time Example

Suppose a learning platform stores:

Students
Courses
Enrollments

Enrollments Table

Student ID	Course ID	Course Name
1	101	MySQL
1	102	Spring Boot
2	101	MySQL

Understanding the Problem

Here:

student_id

alone is NOT unique.

Reason:

One student can enroll in multiple courses

course_id

alone is also NOT unique.

Reason:

Multiple students can enroll in same course

Solution

Combine:

student_id + course_id

Together they become unique.

Composite Key Example

PRIMARY KEY (student_id, course_id)

Composite Key Architecture

Enrollments Table
------------------------------------------------
| Student ID | Course ID | Course Name         |
------------------------------------------------
| 1          | 101       | MySQL               |
| 1          | 102       | Spring Boot         |
| 2          | 101       | MySQL               |
------------------------------------------------

Composite Key:
(student_id, course_id)

How to Create Composite Key

Composite key is created using:

PRIMARY KEY(column1, column2)

SQL Syntax

CREATE TABLE enrollments (

    student_id INT,

    course_id INT,

    course_name VARCHAR(100),

    PRIMARY KEY(student_id, course_id)

);

Explanation

Column	Purpose
student_id	Student Identifier
course_id	Course Identifier
Composite Key	Ensures unique enrollment

What Happens if Duplicate Composite Key is Inserted?

Database throws an error.

Example

INSERT INTO enrollments
VALUES (1, 101, 'MySQL');

INSERT INTO enrollments
VALUES (1, 101, 'MySQL');

Error

Duplicate entry for composite primary key

Why Composite Key is Important

Prevents duplicate combinations
Maintains data integrity
Supports many-to-many relationships
Improves relational database design

Composite Key vs Primary Key

Feature	Primary Key	Composite Key
Columns Used	Single or Multiple	Multiple Columns
Purpose	Unique identification	Unique combination
Example	student_id	(student_id, course_id)

Important Understanding

A composite key is also a type of:

Primary Key

but it uses:

Multiple columns together.

Composite Key Query Flow

Insert Data
      |
      v
Validate student_id + course_id
      |
      v
Check Duplicate Combination
      |
      v
Data Stored

Many-to-Many Relationship Example

Composite keys are heavily used in:

Many-to-Many relationships

Example

Students <-> Courses

Relationship Diagram

Students Table
       |
       v

Enrollments Table
(Composite Key)

       |
       v

Courses Table

Students Table

Student ID	Name
1	Naresh

Courses Table

Course ID	Course Name
101	MySQL

Enrollments Table

Student ID	Course ID
1	101

Composite Key vs Foreign Key

Feature	Composite Key	Foreign Key
Purpose	Uniquely identify rows	Create relationships
Columns Used	Multiple columns	Single or multiple
Uniqueness Required	Yes	No

Composite Foreign Key

Foreign keys can also use multiple columns.

Example

FOREIGN KEY(student_id, course_id)
REFERENCES enrollments(student_id, course_id)

Advantages of Composite Key

Supports complex relationships
Prevents duplicate combinations
Improves relational modeling
Useful for junction tables

Challenges of Composite Key

Complex queries
Larger indexes
Performance overhead
Difficult foreign key references

Best Practices

Use composite keys only when needed
Keep composite keys small
Avoid too many columns in key
Use surrogate keys when appropriate

What is Surrogate Key?

A surrogate key is:

Artificial auto-generated key

Example

enrollment_id INT AUTO_INCREMENT PRIMARY KEY

Composite Key vs Surrogate Key

Feature	Composite Key	Surrogate Key
Based On	Business Columns	Artificial ID
Complexity	Higher	Lower
Performance	Moderate	Better

Real-Time Banking Example

Banking systems may use composite keys for:

Branch ID + Account Number

Real-Time E-Commerce Example

E-commerce systems may use:

order_id + product_id

to uniquely identify ordered products.

Composite Key in Microservices

Microservices may use composite keys in:

Junction tables
Mapping tables
Relationship tables

Example

user_role_mapping

(user_id, role_id)

Professional Interview Answer

A Composite Key in SQL is a key formed by combining two or more columns together to uniquely identify a row in a table. It is used when a single column cannot uniquely identify records. Composite keys are commonly used in many-to-many relationship tables such as student-course enrollments, order-product mappings, and user-role mappings. They help maintain data integrity and prevent duplicate combinations in relational databases.

Why Interviewers Like This Answer

Clearly explains composite uniqueness
Includes practical SQL examples
Shows relationship modeling knowledge
Explains many-to-many relationships
Includes real-world scenarios

Frequently Asked Questions