Если вы использовали базы данных некоторое время, вы, вероятно, установили первичный ключ в большинстве ваших таблиц. Первичный ключ — это уникальный идентификатор для каждой записи, например
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
AUTO_INCREMENT
Предположим, вы добавили следующие данные:
Я бы | страна | площадь | число | расширение |
---|---|---|---|---|
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);
База данных откажется добавлять новую запись, потому что она уже существует с идентификатором 1. К счастью, мы можем опустить идентификатор из нашей INSERT
INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);
Теперь у нас есть четыре записи:
Я бы | страна | площадь | число | расширение |
---|---|---|---|---|
1 | 1 | 234 | 567890 | ЗНАЧЕНИЕ NULL |
2 | 44 | 9876 | 54321 | 42 |
3 | 61 | 3 | 90908200 | ЗНАЧЕНИЕ NULL |
4 | 1 | 234 | 567890 | ЗНАЧЕНИЕ NULL |
Мы можем добавить почти 17 миллионов записей, прежде чем идентификатор выйдет за пределы диапазона.
Отлично — за исключением того, что записи 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 может отличаться.
Давайте попробуем вставить повторяющуюся запись, даже если мы не указываем идентификатор:
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'
Если вы используете практически любую базу данных, вы можете гарантировать, что ваши телефонные записи будут уникальными независимо от того, как вставлены данные.
MySQL NULL
Я говорю почти о любой базе данных, потому что MySQL имеет странную причуду NULL рассматривается как уникальное значение, поэтому вы не можете использовать сравнения, такие как value = NULL
value IS NULL
К сожалению, это также влияет на уникальные индексы, и никакая логика не была реализована, чтобы это исправить.
Мы можем выполнить наш оригинальный INSERT
INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);
Да, это безумие. Я не знаю о проблеме в других базах данных, и даже MySQL работает должным образом, если вы используете механизм хранения BDB. Об этом сообщают как об ошибке MySQL, но нет никаких известных планов исправить это.
Решение: убедитесь, что все поля, определенные в уникальном индексе, не могут быть установлены в NULL . В этом примере мы могли бы указать, что нет добавочного номера, установив значение, такое как 0 или 99999. Или, возможно, мы могли бы сделать поле числом со знаком и установить -1. Это ужасно, но это сработает.
Несмотря на эту проблему, уникальные индексы полезны во многих ситуациях и помогают вам сохранить целостность данных, когда другие программисты и пользователи не так добросовестны!