MySQL Composite Key

The MySQL composite key, also known as a composite primary key or compound key, is a key that consists of two or more columns in a table. A composite key is used to uniquely identify rows in a table based on the combination of values in multiple columns. Each column in a composite key contributes to the uniqueness of the key.

Here’s how you can create a composite key in MySQL:

1. Defining a Composite Primary Key: You can define a composite primary key when creating a table. To do this, you list the columns that make up the key within parentheses in the PRIMARY KEY constraint.

   CREATE TABLE example (
       column1 INT,
       column2 VARCHAR(50),
       PRIMARY KEY (column1, column2)
   );

In this example, the example table has a composite primary key consisting of two columns: column1 and column2. Together, they uniquely identify each row in the table.

2. Defining a Composite Unique Key: If you want to create a composite key that enforces uniqueness but is not a primary key, you can use the UNIQUE constraint.

   CREATE TABLE example (
       column1 INT,
       column2 VARCHAR(50),
       UNIQUE KEY (column1, column2)
   );

This creates a composite unique key, which allows the combination of values in column1 and column2 to be unique within the table.

3. Adding a Composite Key to an Existing Table: If you want to add a composite key to an existing table, you can use the ALTER TABLE statement.

   ALTER TABLE existing_table
   ADD PRIMARY KEY (column1, column2);

or

   ALTER TABLE existing_table
   ADD UNIQUE KEY (column1, column2);

Composite keys are useful when a single column cannot uniquely identify rows in a table, but the combination of two or more columns does. They are commonly used in tables where the uniqueness constraint depends on multiple attributes. When working with composite keys, it’s important to carefully consider the combination of columns to ensure that it accurately reflects the desired uniqueness and data relationships.