SQL Composite Key

A composite key is a unique identifier for each row in a table and is formed by combining two or more columns in a table.

Example

CREATE TABLE CustomerOrderShippings (
    customer_id INT,
    order_id INT,
    shipping_id INT,
    PRIMARY KEY (customer_id, order_id, shipping_id)
);

Here, customer_id, order_id, and shipping_idtogether form a composite primary key of the CustomerOrderShippings table.


Using Composite Keys in Relationships

Composite keys are often used to create relationships between tables in a database. For example,

CREATE TABLE OrderShippings (
    order_id INT,
    shipping_id INT,
    PRIMARY KEY (order_id, shipping_id)
);

In this example, the composite key consists of the order_id and shipping_id columns, uniquely identifying each relationship between an order and its shipping details.

Note: Composite keys are particularly useful when a single column does not contain enough unique data to serve as a primary key.


Composite Key With Foreign Keys

Composite keys can also be used in conjunction with foreign keys to enforce referential integrity in a database.

Let's take a look at an example.

CREATE TABLE OrderDetails (
    customer_id INT,
    order_id INT,
    item_name VARCHAR(100),
    PRIMARY KEY (customer_id, order_id),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

Here, in the OrderDetails table, the combination of customer_id and order_id serves two roles:

  • Composite Primary Key: Together, they uniquely identify each record in the OrderDetails table, ensuring no two records are the same.
  • Composite Foreign Key: customer_id links to the Customers table and order_id links to the Orders table.

This setup links order details to both specific customers and specific orders.


Inserting Records With Composite Keys

Inserting records into a table (see SQL INSERT INTO) with a composite key is similar to inserting into any other table. For example,

-- create table with composite keys
CREATE TABLE CustomerOrderShippings (
    customer_id INT,
    order_id INT,
    shipping_id INT,
    PRIMARY KEY (customer_id, order_id, shipping_id)
);

-- insert into the table
INSERT INTO CustomerOrderShippings (customer_id, order_id, shipping_id) VALUES
(1, 4, 5),
(4, 2, 2);

Here, the SQL command inserts two records into the CustomerOrderShippings table. Each record is a combination of customer_id, order_id, and shipping_id:

  • (1, 4, 5) - represents a record where customer_id is 1, order_id is 4, and shipping_id is 5.
  • (4, 2, 2) - represents another record where customer_id is 4, order_id is 2, and shipping_id is 2.

These combinations form composite keys that uniquely identify each row in the table.

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges