Как использовать уникальные индексы в 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.
Допустим, вы добавили следующие данные:
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);
База данных не позволила бы добавить новую запись, потому что уже существует запись с id равным единице. Но к счастью, мы можем пренебречь id из нашей инструкции INSERT, сгенерировав id автоматически:
INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);
Теперь у нас есть четыре записи:
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 |
Мы можем добавить почти 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. Это ужасно, но это будет работать.
Несмотря на эту проблему, уникальные индексы во многих ситуациях полезны и помогут вам сохранить целостность данных тогда, когда другие программисты и пользователи не так ответственны!