SQL - Composite Key

SQL Composite Keys

What is a Composite Key?

A Composite Key is a primary key that consists of two or more columns. The combination of these columns must be unique for each row in the table, ensuring that every record can be uniquely identified. While individual columns within the composite key can contain duplicate values, their combined value cannot.

Essentially, it’s a multi-column primary key. All columns that are part of a primary key (simple or composite) must be defined as NOT NULL.

Why Use a Composite Key?

Composite keys are most common in ‘linking’ or ‘junction’ tables, which are used to model a many-to-many relationship between two other tables.

Consider an e-commerce database with Users and Products. A user can enroll in multiple courses, and a course can have multiple users. To model this, we create a Enrollments table. In this table, a single column like UserID or CourseID is not enough to uniquely identify a row, because a user can enroll in many courses, and a course can have many users. However, the combination of (UserID, CourseID) is unique, as a user can only enroll in the same course once. This combination makes a perfect candidate for a composite primary key.

Creating a Table with a Composite Key

Syntax

You can define a composite key when you create a table using a PRIMARY KEY constraint on multiple columns.

CREATE TABLE table_name (
    column1_name data_type NOT NULL,
    column2_name data_type NOT NULL,
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column1_name, column2_name)
);

Giving your constraint a name (like pk_enrollments below) is a best practice. It makes it much easier to reference or drop later.

Example

Let’s create the Enrollments table. It links Users and Courses tables (which we assume already exist). The primary key is a composite of user_id and course_id.

CREATE TABLE Enrollments (
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade CHAR(1),

    CONSTRAINT pk_enrollments PRIMARY KEY (user_id, course_id),

    -- Foreign keys to ensure data integrity
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Verification

With the composite key in place, the database will enforce uniqueness on the (user_id, course_id) pair. Let’s test this by trying to insert a duplicate enrollment.

-- Assume User 101 and Course 202 exist.
-- First insertion (succeeds)
INSERT INTO Enrollments (user_id, course_id, enrollment_date) VALUES (101, 202, '2023-10-27');
-- Query OK, 1 row affected.

-- Second insertion with the same pair (fails)
INSERT INTO Enrollments (user_id, course_id, enrollment_date) VALUES (101, 202, '2023-10-28');
-- ERROR:  duplicate key value violates unique constraint "pk_enrollments"
-- DETAIL: Key (user_id, course_id)=(101, 202) already exists.

The error message confirms that the composite primary key is working as expected, preventing duplicate enrollments.

Dropping a Composite Key

To drop a composite key, you use the ALTER TABLE statement. The exact syntax varies slightly across different database systems, which is why naming your constraints is so important.

Standard SQL / PostgreSQL / SQL Server

These systems use the constraint’s name to drop it.

ALTER TABLE Enrollments
DROP CONSTRAINT pk_enrollments;

MySQL

In MySQL, a primary key is always named PRIMARY, so you drop it using the PRIMARY KEY keyword.

ALTER TABLE Enrollments
DROP PRIMARY KEY;

Composite Keys vs. Surrogate Keys

An alternative to a composite key is a surrogate key: a single, auto-incrementing integer column (e.g., enrollment_id INT AUTO_INCREMENT PRIMARY KEY).

Aspect	Composite Key (e.g., user_id, course_id)	Surrogate Key (e.g., enrollment_id)
Meaning	The key is made of real, meaningful data (natural key).	The key has no business meaning; it just exists to be a unique ID.
Joins	Joins require matching multiple columns, which can be slightly more complex.	Joins are simpler, matching on a single integer column, which is often faster.
Uniqueness	Guarantees uniqueness of the business rule (e.g., one enrollment per user/course).	Requires an additional UNIQUE constraint on (user_id, course_id) to enforce the business rule.
Use Case	Best for linking tables where the combination of foreign keys is the natural identifier.	Often preferred for main entity tables (Users, Products) and when ORMs are used.

The choice is a major database design decision. Many developers and Object-Relational Mapping (ORM) frameworks prefer using a surrogate key for every table for simplicity and consistency, while still adding a separate unique constraint on the natural key columns to maintain data integrity.

Best Practices

• Name Your Constraints: Always provide a custom name for your keys (e.g., pk_enrollments) to make future alterations easier.
• Choose Stable Columns: The columns in a composite key should not change over time.
• Consider Index Performance: A composite primary key automatically creates a composite index. The order of columns in the key matters for query performance. Place the most frequently filtered column first.
• Keep it Small: Avoid using many columns or wide data types (like long strings) in composite keys, as this can impact storage and performance.