Key Takeaways
- Unique indexes in databases such as MySQL work similarly to primary keys, ensuring that no two records have the same values in specified fields. This is achieved by altering the table to add a unique index or by recreating the table with a unique key.
- MySQL treats NULL as a unique value which can affect unique indexes. This can result in duplicate records if the field defaults to NULL. To avoid this, ensure all fields defined in a unique index cannot be set to NULL.
- Unique indexes are crucial for maintaining data integrity and avoiding data redundancy in large databases. However, while they speed up data retrieval, they can slow down data insertion as the system needs to check for uniqueness each time data is inserted.
CREATE TABLE `phone` (
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`country` DECIMAL(5,0) UNSIGNED NOT NULL,
`area` DECIMAL(5,0) UNSIGNED NOT NULL,
`number` DECIMAL(8,0) UNSIGNED NOT NULL,
`extension` DECIMAL(5,0) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
In this example, the ‘id’ column is our primary key. When we INSERT
telephone numbers, if we don’t specify an id, an AUTO_INCREMENT
number will be generated by adding one to the highest existing id.
Presume you’ve added the following data:
id | country | area | number | extension |
---|---|---|---|---|
1 | 1 | 234 | 567890 | NULL |
2 | 44 | 9876 | 54321 | 42 |
3 | 61 | 3 | 90908200 | NULL |
INSERT
:
INSERT INTO `phone`
(`id`, `country`, `area`, `number`)
(1, 1, 234, 567890);
The database would refuse to add a new record because one already exists with an id of 1. Fortunately, we can omit the id from our INSERT
to automatically generate one:
INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);
We now have four records:
id | country | area | number | extension |
---|---|---|---|---|
1 | 1 | 234 | 567890 | NULL |
2 | 44 | 9876 | 54321 | 42 |
3 | 61 | 3 | 90908200 | NULL |
4 | 1 | 234 | 567890 | NULL |
Unique Indexes
Unique indexes work in much the same way as a primary key. Although you can only have one primary key, any number of unique indexes can be created with any number of fields. In our example, we want to ensure no two records have the same country, area, number and extension. We can do this by altering our table:ALTER TABLE `phone`
ADD UNIQUE INDEX `ix_phone` (`country`, `area`, `number`, `extension`);
Note that the index name ‘ix_phone’ is optional. Alternatively, we could re-create our table:
DROP TABLE IF EXISTS `phone`;
CREATE TABLE `phone` (
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`country` DECIMAL(5,0) UNSIGNED NOT NULL,
`area` DECIMAL(5,0) UNSIGNED NOT NULL,
`number` DECIMAL(8,0) UNSIGNED NOT NULL,
`extension` DECIMAL(5,0) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ix_phone` (`country`, `area`, `number`, `extension`),
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
Most databases support unique indexes but the SQL syntax may differ.
Let’s try inserting a duplicate record even though we’re not specifying an id:
INSERT INTO `phone`
(`country`, `area`, `number`, `extension`)
(44, 9876, 54321, 42);
The following error will be generated if you’re using MySQL:
Error Code: 1062
Duplicate entry '44-9876-54321-42' for key 'ix_phone'
If you’re using almost any database, you can guarantee your phone records are unique no matter how the data is inserted.
MySQL NULLs
I say almost any database because MySQL has an odd quirk. NULL is treated as a unique value — which is why you cannot use comparisons such asvalue = NULL
and need to use value IS NULL
. Unfortunately, this also affects unique indexes and no logic has been implemented to fix it.
We can execute our original INSERT
multiple times and a new record will be created each time because the extension field defaults to NULL and is considered to be unique:
INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);
Yes, it’s insane. I’m not aware of the problem in other databases and even MySQL works as expected if you’re using the BDB storage engine. It’s been reported as a MySQL bug but there are no known plans to fix it.
The solution: ensure all fields defined in a unique index cannot be set to NULL. In this example, we could indicate that there’s no extension number by setting a value such as 0 or 99999. Or perhaps we could make the field a signed number and set -1. It’s horrible, but it’ll work.
Despite this issue, unique indexes are useful in many situations and help you to retain data integrity when other programmers and users are not so conscientious!
Frequently Asked Questions (FAQs) about Unique Indexes in MySQL Databases
What is the primary purpose of a unique index in MySQL?
The primary purpose of a unique index in MySQL is to prevent duplicate entries in a column or a set of columns. It ensures data integrity by making sure that no two rows of data have identical key values in a unique index. This is particularly useful in maintaining unique records and avoiding data redundancy in large databases.
How does a unique index affect the performance of a MySQL database?
A unique index can significantly improve the performance of a MySQL database. It allows the database management system to quickly locate and retrieve data, especially when dealing with large volumes of data. However, it’s important to note that while unique indexes speed up data retrieval, they can slow down data insertion, as the system needs to check for uniqueness each time data is inserted.
Can a unique index contain NULL values in MySQL?
Yes, a unique index in MySQL can contain NULL values. However, it treats each NULL value as a unique instance. This means that if you have a unique index on a column, you can insert multiple rows with a NULL value in that column.
How do I create a unique index in MySQL?
To create a unique index in MySQL, you can use the CREATE UNIQUE INDEX statement. The syntax is as follows:CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
Replace ‘index_name’ with the name you want to assign to the index, ‘table_name’ with the name of the table, and ‘column1, column2, …’ with the columns you want to include in the index.
Can I create a unique index on multiple columns in MySQL?
Yes, you can create a unique index on multiple columns in MySQL. This is known as a composite unique index. It ensures that the combined values of the specified columns are unique across the database.
How do I drop a unique index in MySQL?
To drop a unique index in MySQL, you can use the DROP INDEX statement. The syntax is as follows:DROP INDEX index_name ON table_name;
Replace ‘index_name’ with the name of the index you want to drop, and ‘table_name’ with the name of the table.
What is the difference between a primary key and a unique index in MySQL?
Both primary key and unique index in MySQL enforce uniqueness of the column on which they are defined. However, a primary key doesn’t allow NULL values, while a unique index does. Also, a table can have only one primary key but multiple unique indexes.
How do I view the unique indexes of a table in MySQL?
To view the unique indexes of a table in MySQL, you can use the SHOW INDEX statement. The syntax is as follows:SHOW INDEX FROM table_name;
Replace ‘table_name’ with the name of the table.
What happens if I try to insert duplicate values in a unique index in MySQL?
If you try to insert duplicate values in a unique index in MySQL, the system will return an error and the insertion will fail. This is because a unique index ensures that all values in the column are unique.
Can I use a unique index to enforce the uniqueness of a combination of columns in MySQL?
Yes, you can use a unique index to enforce the uniqueness of a combination of columns in MySQL. This is done by creating a composite unique index on the desired columns. This ensures that the combined values of these columns are unique across the database.
Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.