SQL Server Interview Question #33

What is a composite key?

Data Types, Keys & Constraints Junior Beginner

Detailed Explanation

A composite key is a key formed from two or more columns. The combination of the columns uniquely identifies a row even though each individual column may contain duplicate values.

Composite keys are common in junction tables representing many-to-many relationships. For example, an OrderItems-style or StudentCourses table may use two foreign-key columns together as its primary key.

Code Example

CREATE TABLE StudentCourses
(
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAt DATETIME2 NOT NULL DEFAULT SYSDATETIME(),

    CONSTRAINT PK_StudentCourses
        PRIMARY KEY (StudentId, CourseId)
);