Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

interview

Home Interviews What is Composite Key in SQL explain with example? – MindStick
Ask Question

1 Answers

Anubhav Sharma
Anubhav Sharma 31 Aug 2026
Report Abuse

A composite key in SQL is a key made up of two or more columns together that uniquely identify a row.

Simple example

Suppose you have a StudentCourses table:

student_id course_id enrollment_date
101 10 2026-01-10
101 20 2026-01-12
102 10 2026-01-11
102 20 2026-01-15

Neither student_id nor course_id is unique by itself:

  • Student 101 appears multiple times.
  • Course 10 appears multiple times.

But the combination student_id + course_id is unique.

So we can define a composite primary key:

CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,

    PRIMARY KEY (student_id, course_id)
);

Now:

INSERT INTO StudentCourses
VALUES (101, 10, '2026-01-10');

is valid.

But this would fail:

INSERT INTO StudentCourses
VALUES (101, 10, '2026-02-01');

because (101, 10) already exists.

However, this is valid:

INSERT INTO StudentCourses
VALUES (101, 20, '2026-02-01');

because (101, 20) is a different combination.

Why use a composite key?

It's particularly useful for many-to-many relationships.

For example:

Students
   │
   │
   ├──── StudentCourses ────┤
   │                         │
   ▼                         ▼
Students                  Courses

A student can take many courses, and a course can have many students. The StudentCourses table uses:

PRIMARY KEY (student_id, course_id)

to ensure that the same student isn't enrolled in the same course twice.

Composite key vs. single-column key

Single-column primary key:

PRIMARY KEY (student_id)

Each student_id must be unique.

Composite primary key:

PRIMARY KEY (student_id, course_id)

The combination must be unique.

Easy way to remember: A composite key is like a combination lock—no single number has to be unique; the combination of numbers together identifies the row.

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md