Designing Composite Keys for SQL Tables

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.

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

Previous SQL Exercise: Eliminating Deletion Anomalies with Proper Database Normalization.
Next SQL Exercise: Identifying Functional Dependencies in a Table.