Understanding MySQL Keys: MUL, PRI, and UNI Explained

1. Overview

MySQL keys are attributes or columns in a relational database that we can use to uniquely identify records in a table. In this tutorial, we’ll look into three primary types of keys in MySQL. These keys are the Primary Key (PRI), Unique Key (UNI), and Multiple Key (MUL).

2. What Are MySQL Keys?

In simple terms, MySQL keys are special fields, or sets of fields, that help organize, access, and manage data efficiently in a database table. Further, we can use these keys to extract any number of rows from a table. When it comes to ensuring the integrity, efficiency, and reliability of data management, these keys play different roles. These include indexing, data consistency, efficient data retrieval, and more.

However, to fully utilize the potential of MySQL keys, it’s essential to understand the different types of keys available and their specific functions. Each of them serves a unique purpose in database management, and understanding their differences helps us efficiently carry out database design and operation.

3. Primary Key

A primary key is like a unique identifier for each record in a table. It’s a special field or a combination of fields that ensures each row has a distinct identity. Moreover, primary key fields can’t contain duplicate or NULL values; this ensures that every record possesses a valid and unique primary key value. Think of it as a data fingerprint – no two records can share the same one.

Generally, the primary key plays a crucial role in relational database management. This is because it helps databases keep track of individual records efficiently. Imagine we have a list of users in a database. Each user needs to be uniquely identifiable to help update, delete, or reference them accurately. That’s where the primary key comes in handy. Through this attribute, users are set apart, reducing confusion and allowing the database to manage them effectively.

For example, assuming we have a table called Department in our university database, let’s use the desc command to describe the table and show the primary key:

desc Department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| code  | varchar(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Basically, from this output, we can see that the id column has a PRI key, indicating that it’s the primary key of the table. This means the id column uniquely identifies each row in the Department table and cannot have NULL values.

4. Unique Key

A unique key ensures that all values in a column or a group of columns are unique across the table. Moreover, if a unique key is applied to multiple columns together, then each combination of values in those columns must be unique across the table.

Another characteristic of unique keys is that, unlike primary keys, they can contain NULL values, which can be unique. However, duplicate non-null values aren’t allowed.

For example, let’s look into a table called Student, using the desc command to show the column with a unique key:

desc Student;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | YES  | UNI | NULL    |       |
| name            | varchar(60) | YES  |     | NULL    |       |
| national_id     | bigint(20)  | NO   |     | NULL    |       |
| birth_date      | date        | YES  |     | NULL    |       |
| enrollment_date | date        | YES  |     | NULL    |       |
| graduation_date | date        | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

Here, the column id is marked with a UNI key, indicating that it is a unique key. Further, we can also see YES under the Null column, which signifies that the id column can contain NULL values.

5. Multiple Keys

A multiple key is an index in MySQL that allows duplicate values in the column or columns it covers. In other words, unlike primary and unique keys, multiple keys permit duplicate values, which makes them suitable for columns where we expect repeated values.

We can also use MUL keys for columns that serve as foreign keys to maintain relationships between tables. Furthermore, by using multiple keys to create indexes on columns with non-unique values, we can make our queries faster. This factor helps MySQL optimize its entire query performance.

For example, let’s look at a table that has a MUL key in a column:

desc Course;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | varchar(10)  | NO   | PRI | NULL    |       |
| name          | varchar(60)  | YES  |     | NULL    |       |
| textbook      | varchar(100) | YES  |     | NULL    |       |
| credits       | int(11)      | YES  |     | NULL    |       |
| is_active     | varchar(10)  | YES  |     | NULL    |       |
| department_id | int(11)      | YES  | MUL | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

In this example, the department_id column is marked with the MUL key, meaning it has a multiple key. This key enables streamlined management and retrieval of course information based on departmental associations.

6. Conclusion

In this article, we explored the various types of MySQL keys and their distinct characteristics. PRI keys ensure the uniqueness of values within a column. Therefore, the PRI key serves as the primary identifier for each record in a table.

Conversely, UNI keys bring flexibility to data constraints by allowing uniqueness while also permitting optional NULL values. MUL keys, on the other hand, enable indexing and are ideal for columns containing non-unique values.