SQL Composite Key

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

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.