Database.Give example of use of composite primary key

🎓 Example Scenario: Student Course Enrollment

You want to track which students are enrolled in which courses, and each student can enroll in many courses, while each course has many students — a many-to-many relationship.

You use a junction table to model this.

🧱 Table: StudentCourses with Composite Primary Key

CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

🔑 Composite primary key: (student_id, course_id)

Ensures a student can’t enroll in the same course twice

Guarantees uniqueness of each student–course pair

📌 Insert Example:

INSERT INTO StudentCourses (student_id, course_id, enrollment_date)
VALUES (1, 101, '2025-04-01');

🔍 Query Example:

Get all courses a student is enrolled in:

SELECT course_id, enrollment_date
FROM StudentCourses
WHERE student_id = 1;

Get all students in a specific course:

SELECT student_id, enrollment_date
FROM StudentCourses
WHERE course_id = 101;

🧠 Why Use a Composite Key Here?

Benefit	Explanation
Avoids duplicate enrollments	Each student-course pair is unique
Models many-to-many cleanly	No need for an artificial ID column
Enforces data integrity	Prevents inconsistent or duplicate data