Designing Composite Keys for SQL Tables

Last update on September 20 2025 13:31:25 (UTC/GMT +8 hours)

Designing a Composite Key for Multi-Attribute Relationships

Write a SQL query to design a composite key for a table with multi-attribute relationships.

Solution:

-- Table requiring a composite key.
CREATE TABLE CourseEnrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

Explanation:

Purpose of the Query :

The goal is to design a composite key to uniquely identify rows based on multiple attributes.

Key Components :

PRIMARY KEY (StudentID, CourseID): Combines two columns to form a unique identifier.
Ensures that each student-course combination is unique.

Why Use Composite Keys? :

Composite keys are useful when no single attribute can uniquely identify a row.
They enforce uniqueness across multiple attributes.

Real-World Application :

In enrollment systems, composite keys ensure that a student cannot enroll in the same course twice.

Notes:

Composite keys are often used in junction tables for many-to-many relationships.
Avoid overusing composite keys if surrogate keys can simplify the design.
Important Considerations:

Ensure proper indexing on composite keys for efficient queries.

For more Practice: Solve these Related Problems:

Write a SQL query to design a composite key for a table storing student enrollments in multiple sections of a course.
Write a SQL query to create a composite key for a table tracking employee attendance by date and shift.
Write a SQL query to design a composite key for a table storing hotel bookings with room number and check-in date as unique identifiers.
Write a SQL query to create a composite key for a table managing inventory stock levels by warehouse and product ID.

Go to:

PREV : Resolving Deletion Anomalies in Database Design.
NEXT : Identifying Functional Dependencies in a Table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.