What is a Composite Key in SQL?

Posted on by Ian

In SQL, a composite key is a key that consists of two or more columns used together to uniquely identify a record in a table. The combination of these columns creates a unique identifier, even though the individual columns might not be unique by themselves.

Example of a Composite Key

Consider a table VehicleRegistrations that tracks vehicle registrations by state and license plate number:

CREATE TABLE VehicleRegistrations (
    StateCode CHAR(2),
    LicensePlate VARCHAR(10),
    OwnerName VARCHAR(100),
    RegistrationDate DATE,
    PRIMARY KEY (StateCode, LicensePlate)
);

In this example:

  • StateCode represents the state in which the vehicle is registered (e.g., “CA” for California, “TX” for Texas).
  • LicensePlate represents the vehicle’s license plate number within that state.

Here, StateCode and LicensePlate together form a composite primary key. Each component of this key is a natural key, as both the state code and license plate number have intrinsic meaning and are used to uniquely identify a vehicle registration within a given state.

Composite Keys vs Compound Keys

The terms composite key and compound key are often used interchangeably, and they generally refer to the same concept: a key made up of more than one column. But there is a subtle difference (depending on who you ask):

  • Compound key typically refers to a key that is composed of more than one column, where all columns in the key are required to uniquely identify a row in a table. In practice, compound keys are a subset of composite keys but specifically in cases where the combination is a primary key.
  • Composite key is a broader term that includes any key formed by combining multiple columns, regardless of whether it is a primary key, unique key, or even a foreign key.

That said, there doesn’t appear to be clear consensus amongst the SQL community around the precise definition of compound keys. Some define compound keys as being those where the columns that make up the composite key are foreign keys from other tables.

To this extent, it would follow that all compound keys are composite keys, but not all composite keys are compound keys.

Consider a table OrderItems that stores details about individual items in a customer’s order:

CREATE TABLE OrderItems (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

In this scenario, the primary key is a compound key (and therefore also a composite key).

And if we were to define a compound key as one that consists of foreign keys from other tables, then it also satisfies that criteria:

  • OrderID is a foreign key that references the Orders table.
  • ProductID is a foreign key that references the Products table.

Here, the combination of OrderID and ProductID forms a composite primary key. This makes sense because each OrderID can have multiple ProductIDs (i.e., multiple items in the order), but each combination of OrderID and ProductID must be unique, ensuring that the same product isn’t listed more than once in the same order.

Having said all that, 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.

Composite Keys That Aren’t Composite Primary Keys

As mentioned, we can have composite keys that aren’t composite primary keys. For example, we can have composite unique keys and composite foreign keys.

  1. Composite Unique Key: A composite unique key ensures that the combination of the columns involved is unique across the table, but it doesn’t serve as the primary key. This is useful when you want to ensure the uniqueness of a combination of columns without designating them as the primary means of identifying a record.
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       DepartmentID INT,
       Email VARCHAR(255),
       UNIQUE (DepartmentID, Email)
   );

Here, the combination of DepartmentID and Email must be unique across the table, ensuring that no two employees within the same department have the same email address. We applied a UNIQUE constraint against these columns to enforce their uniqueness.

  1. Composite Foreign Key: A composite foreign key refers to a combination of columns in one table that references a composite primary or unique key in another table. This helps maintain referential integrity between related tables.
   CREATE TABLE Orders (
       OrderID INT,
       CustomerID INT,
       OrderDate DATE,
       PRIMARY KEY (OrderID, CustomerID)
   );

   CREATE TABLE OrderItems (
       OrderItemID INT PRIMARY KEY,
       OrderID INT,
       CustomerID INT,
       ProductID INT,
       Quantity INT,
       UnitPrice DECIMAL(10, 2),
       FOREIGN KEY (OrderID, CustomerID) REFERENCES Orders(OrderID, CustomerID)
   );

The above example works like this:

  • Orders Table: The Orders table has a composite primary key consisting of OrderID and CustomerID. This means that a combination of OrderID and CustomerID uniquely identifies each order.
  • OrderItems Table: In the OrderItems table, the composite foreign key (OrderID, CustomerID) references the composite primary key in the Orders table. The primary key in OrderItems is OrderItemID. This is a surrogate key, which is a column specifically designed to be a unique key (rather than using a natural key – one that already exists naturally as part of the data).

This example illustrates the concept of a composite foreign key, where two columns together in OrderItems (OrderID and CustomerID) must match a corresponding composite key in the Orders table, enforcing referential integrity across the two tables

When to Use a Composite Key

You should consider using a composite key in the following scenarios:

  • Natural Uniqueness: When the natural structure of the data involves multiple attributes that together uniquely identify a record.
  • Normalization: When designing normalized databases, composite keys can help in eliminating redundancy and maintaining data integrity by uniquely identifying records in join tables or associative entities.
  • Business Requirements: When the business logic demands uniqueness across multiple columns, such as ensuring that no two employees in the same department have the same role, a composite key might be necessary.
  • Foreign Key Relationships: When establishing complex relationships between tables, composite keys can be used as foreign keys to maintain referential integrity.

Summary

Composite keys can help maintain data integrity and enforce uniqueness across multiple columns. While the terms composite and compound keys are often used interchangeably, it’s slightly more nuanced than that. Understanding when and how to use composite keys, whether as primary keys, unique keys, or foreign keys, is crucial for effective database design.