MySQL – Composite Key

In MySQL, a composite key is a combination of two or more columns in a table that allows each row of the table to be uniquely identified. A candidate key that is formed from more than one column is called a multi-column key. In MySQL, uniqueness is guaranteed only when columns are combined. If they are taken individually, they cannot maintain their uniqueness.

Any key that combines more than one attribute, such as a primary key, super key, or candidate key, can be considered a composite key. It is useful to use a composite key when it is necessary to uniquely identify each record with more than one attribute. There can be different types of data in a column used in a composite key. For a composite key to be created in MySQL, the columns do not need to have the same data type.

There are two ways to add a composite key:

1.  CREATE Statement
2. ALTER Statement

The following is an example of how to create a composite key in MySQL:

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    PRIMARY KEY (order_id, customer_id)
);

Using this example, the order table has a composite primary key consisting of the order_id and customer_id columns. Therefore, each row in the table must have a unique combination of order_id and customer_id.

Creating composite keys with multiple columns is also possible.

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    order_date DATE,
    PRIMARY KEY (order_id, customer_id, order_date)
);

Orders table has a composite primary key comprised of order_id, customer_id, and order_date columns.