---
title: "What is Composite Key in SQL explain with example?"  
description: "What is Composite Key in SQL explain with example?"  
author: "Anubhav Sharma"  
published: 2026-08-31  
updated: 2026-08-31  
canonical: https://www.mindstick.com/interview/34534/what-is-composite-key-in-sql-explain-with-example  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2022"]  
reading_time: 4 minutes  

---

# What is Composite Key in SQL explain with example?

> A [**composite key**](https://www.mindstick.com/forum/157563/explain-the-different-types-of-keys-used-in-the-sql-table-with-examples) 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:

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

    PRIMARY KEY (student_id, course_id)
);
```

Now:

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

is valid.

But this would fail:

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

because `(101, 10)` already exists.

However, this is valid:

```plaintext
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:

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

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

```plaintext
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:

```plaintext
PRIMARY KEY (student_id)
```

Each `student_id` must be unique.

## Composite primary key:

```plaintext
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.

## Answers

### Answer by Anubhav Sharma

> A [**composite key**](https://www.mindstick.com/forum/157563/explain-the-different-types-of-keys-used-in-the-sql-table-with-examples) 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:

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

    PRIMARY KEY (student_id, course_id)
);
```

Now:

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

is valid.

But this would fail:

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

because `(101, 10)` already exists.

However, this is valid:

```plaintext
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:

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

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

```plaintext
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:

```plaintext
PRIMARY KEY (student_id)
```

Each `student_id` must be unique.

## Composite primary key:

```plaintext
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.


---

Original Source: https://www.mindstick.com/interview/34534/what-is-composite-key-in-sql-explain-with-example

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.