{"id":39112,"date":"2024-09-02T06:16:17","date_gmt":"2024-09-01T20:16:17","guid":{"rendered":"https:\/\/database.guide\/?p=39112"},"modified":"2026-01-21T07:17:40","modified_gmt":"2026-01-20T21:17:40","slug":"compound-keys-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/compound-keys-explained\/","title":{"rendered":"Compound Keys Explained"},"content":{"rendered":"\n

In SQL databases, a compound key is a type of primary key<\/a> that consists of two or more columns combined to uniquely identify each row in a table. The key columns are used together as a single unit to ensure uniqueness.<\/p>\n\n\n\n

Some within the SQL community define compound keys as composite primary keys<\/a> comprising of foreign keys<\/a> from other tables, so there doesn’t seem to be an agreed consensus on the precise definition.<\/p>\n\n\n\n

With that in mind, let’s explore these definitions of compound keys.<\/p>\n\n\n\n\n\n\n\n

Example of a Compound Key<\/h2>\n\n\n\n

Here’s an example that encapsulates both definitions mentioned:<\/p>\n\n\n\n

-- Create the Courses table\nCREATE TABLE Courses (\n    CourseID VARCHAR(10),\n    CourseName VARCHAR(100),\n    PRIMARY KEY (CourseID)\n);\n\n-- Create the Students table\nCREATE TABLE Students (\n    StudentID INT,\n    StudentName VARCHAR(100),\n    PRIMARY KEY (StudentID)\n);\n\n-- Create the Enrollments table with a compound key\nCREATE TABLE Enrollments (\n    StudentID INT,\n    CourseID VARCHAR(10),\n    EnrollmentDate DATE,\n    Grade CHAR(1),\n    PRIMARY KEY (StudentID, CourseID),\n    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),\n    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)\n);\n\n-- Insert some sample data\nINSERT INTO Courses (CourseID, CourseName) VALUES\n('CS101', 'Introduction to Programming'),\n('MATH201', 'Calculus I');\n\nINSERT INTO Students (StudentID, StudentName) VALUES\n(1, 'Harry Heckle'),\n(2, 'Mandy Mint');\n\nINSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade) VALUES\n(1, 'CS101', '2023-09-01', 'A'),\n(1, 'MATH201', '2023-09-01', 'B'),\n(2, 'CS101', '2023-09-02', 'B');<\/code><\/pre>\n\n\n\n
In this example, we have three tables: Courses<\/code>, Students<\/code>, and Enrollments<\/code>. <\/p>\n\n\n\n
The Enrollments<\/code> table uses a compound key consisting of StudentID<\/code> and CourseID<\/code>. This compound key ensures that a student can only be enrolled once in a particular course.<\/p>\n\n\n\n
Therefore, the following INSERT<\/a><\/code> statement will fail if we run it after the previous INSERT<\/code> statement:<\/p>\n\n\n\n
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade) VALUES\n(1, 'CS101', '2024-01-15', 'A');<\/code><\/pre>\n\n\n\n
It will fail because it tries to insert duplicate data into the compound key.<\/p>\n\n\n\n
Compound Keys vs Composite Keys<\/h2>\n\n\n\n
Compound keys are a form of composite key. But they’re not necessarily the same thing – there is a difference between them (depending on who you ask). First of all, let’s look at the difference based on our initial definition:<\/p>\n\n\n\n