Compound Keys Explained

Posted on by Ian

In SQL databases, a compound key is a type of primary key 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.

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

With that in mind, let’s explore these definitions of compound keys.

Example of a Compound Key

Here’s an example that encapsulates both definitions mentioned:

-- Create the Courses table
CREATE TABLE Courses (
    CourseID VARCHAR(10),
    CourseName VARCHAR(100),
    PRIMARY KEY (CourseID)
);

-- Create the Students table
CREATE TABLE Students (
    StudentID INT,
    StudentName VARCHAR(100),
    PRIMARY KEY (StudentID)
);

-- Create the Enrollments table with a compound key
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID VARCHAR(10),
    EnrollmentDate DATE,
    Grade CHAR(1),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- Insert some sample data
INSERT INTO Courses (CourseID, CourseName) VALUES
('CS101', 'Introduction to Programming'),
('MATH201', 'Calculus I');

INSERT INTO Students (StudentID, StudentName) VALUES
(1, 'Harry Heckle'),
(2, 'Mandy Mint');

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade) VALUES
(1, 'CS101', '2023-09-01', 'A'),
(1, 'MATH201', '2023-09-01', 'B'),
(2, 'CS101', '2023-09-02', 'B');

In this example, we have three tables: Courses, Students, and Enrollments.

The Enrollments table uses a compound key consisting of StudentID and CourseID. This compound key ensures that a student can only be enrolled once in a particular course.

Therefore, the following INSERT statement will fail if we run it after the previous INSERT statement:

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade) VALUES
(1, 'CS101', '2024-01-15', 'A');

It will fail because it tries to insert duplicate data into the compound key.

Compound Keys vs Composite Keys

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:

  • Composite key: A composite key is any key that consists of multiple columns. It’s a broader term that encompasses both compound keys and other multi-column keys.
  • Compound key: A compound key is a specific type of composite key used as the primary key of a table. All compound keys are composite keys, but not all composite keys are compound keys.

In essence, compound keys are a subset of composite keys. The main distinction is that compound keys are always used as primary keys, while composite keys can be used in other contexts, such as unique constraints or foreign keys.

However, as mentioned, there appears to be some disagreement within the SQL community about the precise definition of a compound key. Some professionals define a compound key as being a composite key with all its columns being foreign keys of other tables.

Let’s break it down:

  • Composite Key:
    • This term is widely accepted and consistently used across database systems and literature.
    • It simply means a key composed of two or more columns.
    • These columns can be any combination of attributes (columns), whether they’re foreign keys or not.
  • Compound Key:
    • This term is less consistently defined across the database community.
    • In many contexts, especially in relational database design, it is used to refer to a composite key where the constituent parts are foreign keys to other tables.
    • However, some folk use “compound key” interchangeably with “composite key,” without the specific foreign key connotation.
    • The “foreign key” use of the term is common in the context of modeling relationships between tables, particularly for junction tables in many-to-many relationships.

Perhaps the reason that the “foreign key” use of the term (as composite keys made of foreign keys) is common is that it often arises in the context of representing relationships between entities. When you’re linking two or more tables together, you frequently end up with a key that’s composed of foreign keys from those tables.

However, it’s important to note that:

  1. Not all database professionals or systems make this distinction.
  2. A compound key could theoretically include both foreign key columns and non-foreign key columns.
  3. Some might use “compound key” to specifically refer to a composite key that serves as the primary key of a table, regardless of whether its components are foreign keys.
  4. C.J. Date, a prominent figure in relational database theory, and known for his work on the relational model, typically does not differentiate between the terms composite key and compound key.

So to put it in a nutshell, it’s not a universally agreed-upon distinction. When discussing these concepts, it’s often helpful to clarify the specific meaning intended, as interpretations can vary depending on the context or the background of the individuals involved.

When to Use a Compound Key

OK, now that we’ve established the vagueness around the definition of a compound key, let’s take a look at when to use one.

When to use a compound key:

  • Natural relationships: When the combination of two or more existing attributes naturally identifies a unique record (like in our Enrollments example).
  • Lack of a single unique identifier: When no single column can guarantee uniqueness across all records.
  • Enforcing data integrity: To prevent duplicate combinations of certain fields.
  • Representing many-to-many relationships: As seen in the Enrollments table, which links Students and Courses.
  • Avoiding synthetic keys: When you want to use meaningful business data rather than introducing an artificial identifier.

However, it’s important to note that compound keys can sometimes make querying and indexing more complex, and they may be less flexible if the data model changes. In some cases, using a single surrogate key (like an auto-incrementing integer) as the primary key and creating a unique constraint on the compound of columns might be a more flexible approach.

Summary

So to summarize, while there isn’t a clear consensus around the precise definition of a compound key, it’s generally accepted that it’s a primary key consisting of multiple columns. Some go a step further and add that those columns should be foreign keys in other tables.

There are also some other nuanced arguments in addition to what I’ve discussed here.

In any case, it pays to clarify the specific meaning intended, so that all parties are on the same page when discussing compound keys.