Composite Key - (MySQL Key)

In MySQL, a composite key is a unique index that is composed of more than one column. In this tutorial, we'll discuss how to define and use composite keys in MySQL.

Syntax

The syntax for creating a composite key in MySQL is as follows:

CREATE TABLE example_table (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  PRIMARY KEY (column1, column2)
);

In this example, the composite key is defined on columns column1 and column2, which together make up the unique index.

Example

Let's say we have a table called "employee" that has columns for "employee_id", "department_id", and "salary". We want to create a composite key that ensures that each combination of (employee_id, department_id) is unique. Here's how we can implement it:

CREATE TABLE employee (
  employee_id INT,
  department_id INT,
  salary DECIMAL,
  PRIMARY KEY (employee_id, department_id)
);

Now, we can insert data into the table:

INSERT INTO employee (employee_id, department_id, salary) VALUES
(1, 101, 50000),
(2, 102, 55000),
(3, 101, 45000),
(4, 103, 60000),
(5, 102, 52000);

If we try to insert a row with the same combination of employee_id and department_id, we'll get an error:

INSERT INTO employee (employee_id, department_id, salary) VALUES
(1, 101, 46000);

Output:

Error Code: 1062. Duplicate entry '1-101' for key 'PRIMARY'

Explanation

In the example above, we created a table called "employee" with columns for "employee_id", "department_id", and "salary". We then defined a composite key on the "employee_id" and "department_id" columns to ensure that each combination of employee and department is unique.

We then inserted some data into the table and attempted to insert a row with the same combination of employee_id and department_id, which resulted in an error.

Use

Composite keys are useful when we need to ensure that the combination of multiple columns is unique. They are commonly used in tables that have a relationship between multiple columns that need to be unique together.

Important Points

• A composite key is a unique index that is composed of more than one column.
• A composite key can only include columns that have a unique index defined on them.
• A composite key is defined using the PRIMARY KEY constraint followed by the list of columns that make up the key.

Summary

In this tutorial, we discussed how to define and use composite keys in MySQL. We covered the syntax, example, output, explanation, use, and important points of composite keys in MySQL. With this knowledge, you can now use composite keys in your MySQL tables to ensure that the combination of multiple columns is unique.