SQL Composite Key

A Composite Key in SQL is a combination of two or more columns in a table that uniquely identify a row in the table. The combination of columns must be unique, meaning that no two rows can have the same values for the combination of these columns.

Composite keys are useful when a single column is not sufficient to uniquely identify a record. By combining multiple columns, we can ensure uniqueness and maintain referential integrity.

Tables Used

Example with Composite Key

In this example, we will use a composite key for the Orders table. The composite key will consist of both ProductID and CustomerID, ensuring that a customer can only place one order for each product. This will prevent multiple orders for the same product by the same customer.

-- Create the Orders table with a composite key (ProductID, CustomerID)
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    Quantity INT,
    CONSTRAINT fk_composite_key PRIMARY KEY (ProductID, CustomerID)
);

-- Insert data into the Orders table
INSERT INTO Orders (OrderID, ProductID, CustomerID, Quantity) VALUES (1, 1, 101, 2);
INSERT INTO Orders (OrderID, ProductID, CustomerID, Quantity) VALUES (2, 2, 102, 1);
INSERT INTO Orders (OrderID, ProductID, CustomerID, Quantity) VALUES (3, 1, 103, 3);
INSERT INTO Orders (OrderID, ProductID, CustomerID, Quantity) VALUES (4, 2, 101, 1);

-- View the Orders table
SELECT * FROM Orders;
        

Explanation

- The composite key in this example consists of the ProductID and CustomerID columns.
- The combination of these two columns must be unique across all rows in the Orders table, which means a customer can only place one order per product.
- This enforces uniqueness for each combination of ProductID and CustomerID and prevents duplicate orders for the same product by the same customer.
- The PRIMARY KEY constraint ensures that the OrderID remains unique for each order, and the composite key is used to maintain the integrity between ProductID and CustomerID.