MySQL Composite Key

Introduction

In this chapter, we will learn about composite keys in MySQL. A composite key is a primary key made up of two or more columns. Composite keys are useful when a single column is not sufficient to uniquely identify a row in a table. We will cover the syntax for defining composite keys, examples, and important considerations for using composite keys in MySQL.

Syntax

The basic syntax for defining a composite key in MySQL is:

When creating a table:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column1, column2)
);

Adding a composite key to an existing table:

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

Example

Let’s go through an example where we create a table with a composite key and then add a composite key to another table.

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com'),
('Neha', 'Verma', 'neha.verma@example.com'),
('Sahil', 'Mehta', 'sahil.mehta@example.com');

5. Create the Projects Table with a Composite Primary Key:

CREATE TABLE projects (
    project_id INT,
    employee_id INT,
    project_name VARCHAR(100),
    PRIMARY KEY (project_id, employee_id)
);

6. Insert Data into the Projects Table:

INSERT INTO projects (project_id, employee_id, project_name) VALUES
(1, 1, 'Project A'),
(2, 1, 'Project B'),
(1, 2, 'Project C'),
(3, 3, 'Project D');

7. Query the Tables:

Let’s perform a simple query to verify our data.

SELECT p.project_id, p.project_name, e.first_name, e.last_name
FROM projects p
JOIN employees e ON p.employee_id = e.id;

Output

Adding a Composite Key to an Existing Table

Let’s assume we have a table tasks and we want to add a composite key to it.

1. Create the Tasks Table:

CREATE TABLE tasks (
    task_id INT,
    project_id INT,
    employee_id INT,
    task_name VARCHAR(100)
);

2. Add a Composite Primary Key to the Tasks Table:

ALTER TABLE tasks
ADD PRIMARY KEY (task_id, project_id, employee_id);

3. Insert Data into the Tasks Table:

INSERT INTO tasks (task_id, project_id, employee_id, task_name) VALUES
(1, 1, 1, 'Task 1'),
(2, 1, 2, 'Task 2'),
(3, 2, 1, 'Task 3'),
(4, 3, 3, 'Task 4');

4. Query the Tables:

Let’s perform a simple query to verify our data.

SELECT t.task_id, t.task_name, p.project_name, e.first_name, e.last_name
FROM tasks t
JOIN projects p ON t.project_id = p.project_id AND t.employee_id = p.employee_id
JOIN employees e ON t.employee_id = e.id;

Output

Important Considerations

• Uniqueness: The combination of columns in a composite key must be unique across all rows in the table.
• Indexing: Composite keys are automatically indexed in MySQL, improving the performance of queries that use the composite key for lookups.
• Foreign Keys: Composite keys can be referenced by foreign keys in other tables, establishing relationships between tables based on multiple columns.
• Data Integrity: Composite keys help maintain data integrity by ensuring that the combination of key columns is always unique and consistent.

Conclusion

Composite keys are an important feature in MySQL for uniquely identifying rows based on multiple columns. This chapter covered the syntax for defining composite keys, provided a full example with creating and adding composite keys, and discussed important considerations.