How to Use Unique Indexes in MySQL and Other Databases

If you’ve been using databases for a while, you’ve probably set a primary key in most of your tables. A primary key is a unique identifier for each record, e.g.

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

then issue the following 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

We can add almost 17 million records before the id goes out of range. Great — except that records 1 and 4 are identical. What if we want to ensure all phone numbers are unique?

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 as value = 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