Using Primary Keys, Indices, and Composite Keys in MySQL

Introduction

In MySQL, a primary key is a field that uniquely identifies each record in a table. For instance, student_id, employee_id, client_id, country_id, and more. When you join two or more columns to make the primary identifier, it is referred to as a composite key. On the other hand, an index is a data structure that increases the performance of the database when searching records. Indices adopt rapid intelligent lookup and processing algorithms to efficiently scan records. When you define a column with the primary key attribute, MySQL automatically creates an index for speed purposes.

In a mission-critical application, your database might contain many related tables. For the database to operate effectively, the only way to clearly define the relationships between the tables is by using primary keys, indices, and composite keys. MySQL only allows one primary key per table but you can have as many indices as you want. Also, a primary key field can not accept duplicates or null values.

In this guide, you'll learn how to create primary keys, indices, and composite keys on your MySQL server.

Prerequisites

To follow along with this guide, you need a MySQL database server and user account.

1. Create a Sample Database

In this step, you'll set up a database.

1. Log in to your MySQL server and run the command below to create a sample_db database.

    mysql> CREATE DATABASE sample_db;

   Output.

    Query OK, 1 row affected (0.01 sec)
   

2. Switch to the new sample_db database.

    mysql> USE sample_db;

   Output.

    Database changed 
   

3. With the sample database created, you can now start creating the different MySQL keys in the following steps.

2. Create Single-column-based Keys

Your database should have the correct keys from the ground up to avoid issues as your data grows. Therefore, you should plan ahead and identify the primary key column and any other columns that you want to index. In this step, you'll create a single-column-based primary key and index on a table.

1. Create an employees table. This table will contain four columns. Define the employee_id as the unique column using the PRIMARY KEY statement. Then, use the AUTO_INCREMENT keyword to allow MySQL to generate the next employee_id in the series every time you INSERT a new record. Next, index the phone column using the statement INDEX(phone).

    mysql> CREATE TABLE employees (
               employee_id BIGINT PRIMARY KEY AUTO_INCREMENT,
               first_name VARCHAR(50),
               last_name VARCHAR(50),
               phone VARCHAR(15),
               INDEX(phone)
           ) ENGINE = InnoDB;

   Output.

    Query OK, 0 rows affected (0.02 sec)
   

2. To ensure your table has the right indices, run the following command.

    mysql> SELECT DISTINCT
               TABLE_NAME,
               INDEX_NAME,
               COLUMN_NAME 
           FROM INFORMATION_SCHEMA.STATISTICS
           WHERE TABLE_SCHEMA = 'sample_db';
   

3. You should get the following output.

    +------------+------------+-------------+
    | TABLE_NAME | INDEX_NAME | COLUMN_NAME |
    +------------+------------+-------------+
    | employees  | PRIMARY    | employee_id |
    | employees  | phone      | phone       |
    +------------+------------+-------------+
    2 rows in set (0.00 sec)
   

4. Next, INSERT a record into the employees table to test if everything is working as expected.

    mysql> INSERT INTO employees(first_name, last_name, phone) VALUES ('JOHN', 'DOE', '11111');

   Output.

    Query OK, 1 row affected (0.01 sec)
   

5. Query the employees table to make sure the data is in place.

    mysql> SELECT 
               employee_id,
               first_name,
               last_name
           FROM employees;

   Output.

    +-------------+------------+-----------+
    | employee_id | first_name | last_name |
    +-------------+------------+-----------+
    |           1 | JOHN       | DOE       |
    +-------------+------------+-----------+
    1 row in set (0.00 sec)
   

6. Attempt to violate the PRIMARY KEY unique constraint by adding an employee with an existing employee_id of 1.

    mysql> INSERT INTO employees(employee_id, first_name, last_name, phone) VALUES (1, 'MARY', 'ROE', '22222');
   

7. You should get the following error since MySQL does not accept duplicate values on the PRIMARY KEY columns.

    ERROR 1062 (23000): Duplicate entry '1' for key 'employees.PRIMARY'

3. Create Multi-column/Composite Keys

In the previous step, your primary key and indices were based on a single column. MySQL also supports composite keys. These are unique keys that use a combination of two or more columns to uniquely identify each record. For instance, consider a case where you want to store vendors' names and the products they supply on a single table. In this scenario, every distinct vendor can only have a single record for each unique product they supply.

1. To understand the above use-case better, create the products_to_vendors table using the statement below. In the table, use the statement PRIMARY KEY(vendor_name, product_name) to set up the vendor_name and product_name as a multi-column PRIMARY KEY.

    mysql> CREATE TABLE products_to_vendors (
               vendor_name VARCHAR(50),
               product_name VARCHAR(50),
               PRIMARY KEY (vendor_name, product_name)
           ) ENGINE = InnoDB;

   Output.

    Query OK, 0 rows affected (0.01 sec)
   

2. Make sure you have got the right indices.

    mysql> SELECT DISTINCT
               TABLE_NAME,
               INDEX_NAME,
               COLUMN_NAME 
           FROM INFORMATION_SCHEMA.STATISTICS
           WHERE TABLE_SCHEMA = 'sample_db'
           AND TABLE_NAME = 'products_to_vendors';

   Output.

    +---------------------+------------+--------------+
    | TABLE_NAME          | INDEX_NAME | COLUMN_NAME  |
    +---------------------+------------+--------------+
    | products_to_vendors | PRIMARY    | vendor_name  |
    | products_to_vendors | PRIMARY    | product_name |
    +---------------------+------------+--------------+
    2 rows in set (0.00 sec)
   

3. Next, INSERT the following sample records into the products_to_vendors table.

    mysql> INSERT INTO products_to_vendors(vendor_name, product_name) VALUES ('ABC Suppliers', 'WIRELESS MOUSE');
           INSERT INTO products_to_vendors(vendor_name, product_name) VALUES ('XYZ Limited', 'TOUCH SCREEN');
           INSERT INTO products_to_vendors(vendor_name, product_name) VALUES ('SPEED SOLUTION', 'POWER CABLES');

   Output.

    Query OK, 1 row affected (0.01 sec)
   

4. List the records from the products_to_vendors table.

    mysql> SELECT
               vendor_name,
               product_name                  
           FROM products_to_vendors;

   Output.

    +----------------+----------------+
    | vendor_name    | product_name   |
    +----------------+----------------+
    | ABC Suppliers  | WIRELESS MOUSE |
    | SPEED SOLUTION | POWER CABLES   |
    | XYZ Limited    | TOUCH SCREEN   |
    +----------------+----------------+
    3 rows in set (0.00 sec)
   

5. Attempt inserting a record that violates the composite key columns.

    mysql> INSERT INTO products_to_vendors(vendor_name, product_name) VALUES ('ABC Suppliers', 'WIRELESS MOUSE');
   

6. MySQL server should now throw the following error.

    ERROR 1062 (23000): Duplicate entry 'ABC Suppliers-WIRELESS MOUSE' for key 'products_to_vendors.PRIMARY'
   

7. However, since the same product(WIRELESS MOUSE) can be supplied by a different vendor, the following statement should succeed.

    mysql> INSERT INTO products_to_vendors(vendor_name, product_name) VALUES ('EXAMPLE COMPANY', 'WIRELESS MOUSE');

   Output.

    Query OK, 1 row affected (0.00 sec)

4. Create Keys on Existing MySQL Tables

Sometimes, you might forget to define keys when creating tables or find yourself in a situation where you want to index a column during the development process to speed up queries on your rapidly growing database. Luckily, MySQL provides you with the functionalities of creating keys on existing tables using the ALTER TABLE statement.

1. Create a products table without specifying any keys.

    mysql> CREATE TABLE products (
               product_id BIGINT,
               product_name VARCHAR(100),
               category_name VARCHAR(100),
               price DOUBLE                   
           ) ENGINE = InnoDB;

   Output.

    Query OK, 0 rows affected (0.01 sec)
   

2. Ensure you've not accidentally defined any indices by running the following statement.

    mysql> SELECT DISTINCT
               TABLE_NAME,
               INDEX_NAME,
               COLUMN_NAME 
           FROM INFORMATION_SCHEMA.STATISTICS
           WHERE TABLE_SCHEMA = 'sample_db'
           AND TABLE_NAME = 'products';

   Output.

    Empty set (0.00 sec)
   

3. Now, to define the product_id as the PRIMARY KEY in the products table, run the following statement.

    mysql> ALTER TABLE products ADD PRIMARY KEY(product_id);

   Output.

    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
   

4. Next, use the following command to index the product_name column.

    ALTER TABLE products ADD INDEX product_name_index (`product_name`);

   Output.

    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
   

5. Then, query the INFORMATION_SCHEMA database again to see if you've enforced the new keys.

    mysql> SELECT DISTINCT
               TABLE_NAME,
               INDEX_NAME,
               COLUMN_NAME 
           FROM INFORMATION_SCHEMA.STATISTICS
           WHERE TABLE_SCHEMA = 'sample_db'
           AND TABLE_NAME = 'products';
   

6. The output below confirms your indices are in place.

    +------------+--------------------+--------------+
    | TABLE_NAME | INDEX_NAME         | COLUMN_NAME  |
    +------------+--------------------+--------------+
    | products   | PRIMARY            | product_id   |
    | products   | product_name_index | product_name |
    +------------+--------------------+--------------+
    2 rows in set (0.00 sec)

5. Benefits of Using Primary Keys, Indices, and Composite Keys

In every application, adding indices into your database tables allows you to:

• Easily locate and identify records in large tables. For instance, if you have 1,000 employees on a table and you just want to locate a single staff member information, you can use their employee_id to look them up.

• Prevent duplicate entries. In most business transactions, duplicate records may lead to losses due to conflicting data. Since a primary key column does not allow duplicate entries, it is a good bet when it comes to validating data.

• Update and delete records. MySQL requires a unique key in a WHERE clause to look up, and UPDATE or DELETE a single record.

• Define unique constraints and link related tables together. For instance, if you want to enforce referential integrity in your database, it would be impossible without using foreign keys.

Conclusion

This guide has focused on creating primary keys, indices, and composite keys on your MySQL server. While this is not a conclusive list of using MySQL indices, it should give you a good foundation to work with databases keys as a beginner.