What is composite primary key?

A composite primary key is a primary key that consists of two or more columns whose values, when combined, uniquely identify each row in a table. Instead of using a single column, multiple columns are used together to enforce the uniqueness constraint.

Definition

In relational databases, a primary key serves to uniquely identify each record (row) in a table. While a primary key is often a single column (like an 'ID'), there are scenarios where no single column can guarantee uniqueness. In such cases, a combination of two or more columns is designated as the primary key, forming a composite primary key. The values across all columns within the composite key must be unique for each row.

Key Characteristics

• Uniqueness: The combination of values from all columns in the composite key must be unique across all rows in the table.
• Non-Nullability: Each column participating in a composite primary key cannot contain NULL values.
• Data Integrity: It ensures the integrity and consistency of data by preventing duplicate records based on the combined attributes.
• Relationship Management: Often used in junction tables (also known as linking or many-to-many tables) to represent relationships between two or more other tables, where the foreign keys from those tables collectively form the primary key of the junction table.

Example

Consider a database for a university where students can enroll in multiple courses, and courses can have multiple students. A table to track these enrollments might not have a simple single-column primary key. Instead, the combination of a student's ID and a course's ID would uniquely identify a specific enrollment.

CREATE TABLE CourseEnrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

In this CourseEnrollments table, neither student_id alone nor course_id alone can be the primary key because a student can enroll in multiple courses, and a course can have multiple students. However, the combination of (student_id, course_id) is unique for each specific enrollment. This ensures that a student cannot be enrolled in the same course more than once.