Как использовать уникальные индексы в MySQL и других базах данных

Если вы пользовались базами данных на протяжении некоторого времени, вы, вероятно, устанавливали первичный ключ в большинстве ваших таблиц.

Первичный ключ – это уникальный идентификатор для каждой записи, например:

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;

В этом примере, столбец id – это наш первичный ключ. Когда мы производим операцию INSERT для телефонного номера, в случае, если мы не определили id, будет сгенерировано число AUTO_INCREMENT путем добавления единицы к наибольшему из существующих id.

Допустим, вы добавили следующие данные:

Затем выполним следующую операцию INSERT:

INSERT INTO `phone`
(`id`, `country`, `area`, `number`)
(1, 1, 234, 567890);

База данных не позволила бы добавить новую запись, потому что уже существует запись с id равным единице. Но к счастью, мы можем пренебречь id из нашей инструкции INSERT, сгенерировав id автоматически:

INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);

Теперь у нас есть четыре записи:

Мы можем добавить почти 17 миллионов записей прежде, чем значение id выйдет за пределы выделенной памяти.

Все отлично – за исключением того, что записи 1 и 4 идентичны. А что если мы хотим обеспечить уникальность всех телефонных номеров?

Уникальные индексы

Уникальные индексы работают почти таким же образом, как и первичный ключ. Однако вы можете использовать только один первичный ключ, тогда как уникальных индексов можно создать любое количество и с произвольным числом полей.

В нашем примере мы хотим гарантировать, что не существует двух записей с одной и той же страной, областью, основным и добавочным номером.

Мы можем сделать это, изменив нашу таблицу:

ALTER TABLE `phone` 
ADD UNIQUE INDEX `ix_phone` (`country`, `area`, `number`, `extension`);

Обратите внимание, что имя индекса ix_phone не является обязательным. С другой стороны, мы бы могли создать нашу таблицу вновь:

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;

Большинство баз данных поддерживает уникальные индексы, однако синтаксис SQL может отличаться.

Давайте попробуем вставить дублирующую запись, не указывая id:

INSERT INTO `phone`
(`country`, `area`, `number`, `extension`)
(44, 9876, 54321, 42);

Если вы используете MySQL, будет сгенерирована следующая ошибка:

Error Code: 1062
Duplicate entry '44-9876-54321-42' for key 'ix_phone'

При использовании практически любой базы данных вы можете гарантировать уникальность ваших записей телефонных номеров, независимо от того, как происходит вставка данных.

Значение NULL в MySQL

Я говорю «практически любой базы данных», потому что MySQL имеет странную особенность. Значение NULL рассматривается как уникальное значение – вот почему вы не можете использовать такие сравнения как value = NULL, а должны использовать value IS NULL.

К сожалению, это также влияет и на уникальные индексы, но ничего не было реализовано, чтобы исправить это.

Мы можем выполнить нашу первоначальную операцию INSERT несколько раз, и каждый раз будет создана новая запись, потому что поле extension по умолчанию имеет значение NULL и считается уникальным:

INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);

Да, это безумие. Я не в курсе о наличии этой проблемы в других базах данных, и даже MySQL работает должным образом, если вы используете подсистему хранения BDB. Данная особенность была отмечена как ошибка MySQL, но планы по ее исправлению не известны.

Решение: обеспечьте, чтобы все поля, определенные как уникальные индексы, не могли быть установлены в NULL. В данном примере мы могли бы указать, что дополнительного номера нет, установив значение 0 или 99999.

Или, возможно, мы могли бы сделать поле знаковым числом и установить значение -1. Это ужасно, но это будет работать.

Несмотря на эту проблему, уникальные индексы во многих ситуациях полезны и помогут вам сохранить целостность данных тогда, когда другие программисты и пользователи не так ответственны!