MySQL Primary Key

Introduction

In this chapter, we will learn about the primary key in MySQL. A primary key is a column or a set of columns that uniquely identifies each row in a table. It is a crucial component of database design, ensuring data integrity and enabling efficient data retrieval. We will cover the syntax for defining primary keys, examples, and important considerations for using primary keys in MySQL.

Syntax

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

When creating a table:

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

For a composite primary key:

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

Adding a primary key to an existing table:

ALTER TABLE table_name
ADD PRIMARY KEY (column1);

Example

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

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table with a Primary Key:

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,
    department_id INT
);

4. Insert Data into the Employees Table:

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

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. Add a Primary Key to an Existing Table:

Let’s assume we have a table departments without a primary key.

CREATE TABLE departments (
    department_name VARCHAR(100)
);

ALTER TABLE departments
ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

8. Insert Data into the Departments Table:

INSERT INTO departments (department_name) VALUES
('Mathematics'),
('Science'),
('Literature');

9. Query the Tables:

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

SELECT * FROM employees;
SELECT * FROM projects;
SELECT * FROM departments;

Output

employees table:

projects table:

departments table:

Important Considerations

• Uniqueness: A primary key must contain unique values. No two rows can have the same primary key value.
• Not Null: A primary key column cannot contain NULL values. Every row must have a valid primary key value.
• Auto Increment: For single-column primary keys, it is common to use the AUTO_INCREMENT attribute to automatically generate unique values.
• Composite Keys: A primary key can consist of multiple columns (composite key), ensuring the combination of values is unique.
• Performance: Primary keys are automatically indexed in MySQL, improving the performance of queries that use the primary key for lookups.

Conclusion

The primary key is an essential component of database design in MySQL, ensuring data integrity and efficient data retrieval. This chapter covered the syntax for defining primary keys, provided a full example with single and composite primary keys, and discussed important considerations. In the next chapter, we will explore foreign keys and their role in establishing relationships between tables.