In MySQL, a composite key is a combination of two or more columns in a table that uniquely identifies each entry. It is a candidate key made up of many columns. MySQL guarantees column uniqueness only when they are concatenated. If they are extracted separately, the uniqueness cannot be maintained.

Any key, such as the primary key, super key, or candidate key, can be referred to as a composite key if it has more than one characteristic. A composite key is important when the database has to uniquely identify each record that contains more than one characteristic. A composite key column may include a variety of data types. Thus, the columns do not have to have the same data type to form a composite key in MySQL.

A composite key can be added in two ways:

• Using CREATE Statement
• Using ALTER Statement

Let us see both ways in detail.

Composite Key Using CREATE Statement

Here, we will look at how composite keys operate in MySQL. Let us first construct a table called “employees” using the following statement:

CREATE TABLE employee (  
    employee_id INT,
    department_id INT,
    employee_name VARCHAR(255),
    PRIMARY KEY (employee_id, department_id)
);  

In this example, the employee table is created with three columns: employee_id, department_id, and employee_name. The PRIMARY KEY constraint is applied to the combination of employee_id and department_id. This means that each pair of employee_id and department_id values must be unique within the table.

We can verify the same using the command as below:

DESCRIBE employee;  

After the successful execution, It means we have successfully added the composite primary key on employee_id and department_id columns.

Next, we need to insert the values into this table as given below:

INSERT INTO employee (employee_id, department_id, employee_name) VALUES
    (101, 1, 'John Doe'),
    (102, 2, 'Jane Smith'),
    (103, 1, 'Bob Johnson'),
    (104, 3, 'Alice Brown');

Next, execute the below command to show the table data:

SELECT * FROM employee;  

Output:

Output

Composite Key Using ALTER Statement

In some cases, you might need to add a composite key to an existing table. MySQL allows you to achieve this using the ALTER TABLE statement. Let’s consider a scenario where we want to add a composite key to an existing orders table based on the columns order_id and product_id.

Assuming the initial structure of the orders table is as follows:

-- Initial table structure without a composite key
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT
);

Now, let’s use the ALTER TABLE statement to add a composite key:

-- Add a composite key to the existing table
ALTER TABLE orders
ADD PRIMARY KEY (order_id, product_id);

In this example, the ALTER TABLE statement is used to modify the orders table. The ADD PRIMARY KEY clause is then used to define the composite key based on the columns order_id and product_id.

Next, we need to insert the values into this table as given below:

INSERT INTO orders (order_id, product_id, quanitity) VALUES
    (101, 1, 10),
    (102, 2, 12),
    (103, 1, 13),
    (104, 3, 14);

Next, execute the below command to show the table data:

SELECT * FROM orders;  

Output:

Output

Conclusion

In the relational databases, the proper utilization of keys is essential for maintaining data integrity and ensuring efficient data retrieval. MySQL offers a robust solution in the form of composite keys, allowing us to create unique identifiers based on combinations of multiple columns. Through this article, we explored the creation of composite keys using both the CREATE TABLE and ALTER TABLE statements.

FAQs on MySQL Composite Key

What is a composite key in MySQL?

A composite key in MySQL is a combination of two or more columns that uniquely identifies each entry in a table. It ensures column uniqueness only when the columns are concatenated.

How is a composite key different from a single column primary key?

A composite key uses multiple columns to uniquely identify a record, while a single column primary key uses just one column. Composite keys are useful when a unique identifier needs to be created from multiple columns.

Can composite keys contain NULL values?

No, composite keys cannot contain NULL values because NULL values cannot be compared for uniqueness.