How to MUL vs PRI vs UNI in MySQL

1. Keys in MySQL
2. Conclusion

This tutorial will teach PRI, MUL, and UNI keys in MySQL.

By going through step by step in this tutorial, we will see the basic difference between PRI, MUL, and UNI keys and how to use each of them. We will also see their effect in the table using the example code.

Keys in MySQL

The key is an attribute or sometimes a set of attributes used to uniquely identify a row from a table.

Various keys are used in MySQL for various purposes, including primary key, unique key, composite key, foreign key, super key, alternate key, and candidate key.

We will focus on Primary, Unique, and Multiple Key for this article.

PRI, UNI, and MUL Keys in MySQL

The PRI means primary key, forces on the uniqueness of a record in the table. It does not allow NULL values.

A single column or multiple columns can be used as a Primary Key. The UNI key represents Unique Key, enforces the uniqueness of a row (a record) in the table (relation) like the primary key, and has NULL values.

One or multiple columns can be used to make a unique key.

The MUL key is none of them, which means, MUL key is an index that is neither the Primary Key nor the Unique Key. It allows NULL values, and the multiple occurrences of the same value as its name MUL originated from multiple.

If you have multiple keys applied on the same column, the keys will be displayed according to their priority, PRI, UNI, and MUL.

Let’s understand with the help of the following sample code.

#create an employee table
CREATE TABLE employee(
    ID INT PRIMARY KEY NOT NULL, #this is PRI Key
    FIRST_NAME VARCHAR(60) NOT NULL, 
    LAST_NAME VARCHAR(60) NOT NULL,
    GENDER VARCHAR(60) NOT NULL, INDEX(GENDER), #this is MUL Key
    COUNTRY_CODE VARCHAR(30), INDEX(COUNTRY_CODE), #this is MUL Key
    CITIZEN_ID INT UNIQUE KEY #this is UNI Key
);

#insert some data
INSERT INTO employee(ID, FIRST_NAME, LAST_NAME,GENDER, COUNTRY_CODE,CITIZEN_ID)
VALUES
(1,'Thomas', 'Christopher','Male','+61',485),
(2,'Lisa', 'Mark', 'Female','+59',408),
(3,'Anthony', 'Richard', 'Male','+61',215),
(4,'Matthew', 'Charles', 'Male',NULL, 610),
(5,'Kiren', 'Donald','Female','+31', null);

#use the following query to describe table columns and their properties
desc employee;

Output:

See the above example code and observe the output to understand. It is important to note that we can have only one PRI key within the table, but there can be more than one UNI and MUL key.

In the above output, we have one PRI key applied on the ID column, which does not allow NULL values. That means, if one ID is assigned to an employee, that same ID will not be assigned to any other within the table.

We have one UNI key applied on the CITIZEN_ID column; it enforces the uniqueness and allows the NULL values. We can see two MUL keys on two different columns named GENDER and COUNTRY_CODE.

It is necessary to think that the MUL key is working fine on both columns where the GENDER column can not have NULL values, but COUNTRY_CODE can. This is why we say MUL is neither the PRI nor the UNI key.

Conclusion

In the above discussion, we concluded that keys are the attributes applied on column(s) to uniquely identify them within the table.

Different keys are used for different requirements. PRI is for the situation where you don’t want to repeat values within the column and don’t allow NULL values.

UNI key focuses on uniqueness within the column but allows NULL values. MUL is neither PRI nor UNI and can accept NULL and NOT NULL values.

There can be only one PRI key, but you can have multiple UNI and MUL keys depending on the project requirements.