Explain what a composite primary key is and in which cases it is used in databases.

A composite primary key is a primary key that consists of two or more columns of a database table. Together, these columns uniquely identify each record.

It is used when one column cannot uniquely identify a record, but a combination of several columns can. For example, in a "Student Course Enrollment" table, uniqueness can be defined by the combination of student_id and course_id.

Example in SQL:

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

Here, student_id and course_id together form a composite primary key.