Beginner

132. Composite Primary Key

Learn how multiple columns can work together to uniquely identify a record.

📘 Lesson 142 of 728 ⏱ 9 min 🎯 Intermediate

A composite primary key is a primary key made up of two or more columns. The combination of those columns must uniquely identify each row.

Some business tables do not have one column that uniquely identifies a record. A combination of columns may be required.

In an OrderDetails table, OrderID and ProductID together can identify a specific product within a specific order.

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

Composite keys are commonly used in transaction-detail tables, mapping tables, enrollment tables, and many-to-many relationships.

  • A composite key contains multiple columns.
  • The combination must be unique.
  • Each individual column does not necessarily need to be unique.
  • It is useful for relationship tables.
  • It can represent a business-level unique combination.
What is a composite primary key?

It is a primary key made up of two or more columns whose combined values uniquely identify a row.

When would you use a composite key?

It is useful when no single column can uniquely identify a record and a combination of columns is required.

Have you finished this lesson?

Introduction to SQL – All Lessons