Composite Key in RDBMS

A composite key is a primary key that consists of two or more columns combined to uniquely identify each row in a table. A single column alone may not be unique, but the combination of multiple columns together forms a unique identifier.

Example 1: Order Details

In an order details table, a single OrderID can appear in multiple rows (for different products), and a single ProductID can appear in multiple orders. However, the combination of OrderID + ProductID is unique for each row ?

Example 2: Student Table

In the following Student table, the composite key is {StudentID, StudentEnrollNo} ?

The table uses two columns (StudentID and StudentEnrollNo) together as the primary key.

SQL Syntax

The following SQL shows how to create a table with a composite key ?

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

Conclusion

A composite key is a primary key consisting of two or more columns that together uniquely identify each row. It is used when no single column can serve as a unique identifier on its own, commonly seen in junction tables for many-to-many relationships.