Уникальный ключ с NULL
Этот вопрос требует некоторого гипотетического фона. Давайте рассмотрим employee таблица со столбцами name, date_of_birth, title, salary, используя MySQL в качестве СУБД. Поскольку, если у какого-либо человека есть то же имя и дата рождения, что и у другого человека, они по определению являются одним и тем же человеком (за исключением удивительных совпадений, когда у нас есть два человека по имени Авраам Линкольн, родившиеся 12 февраля 1809 года), мы поместим уникальный ключ на name а также date_of_birth это означает "не храните одного и того же человека дважды". Теперь рассмотрим эти данные:
id name date_of_birth title salary
1 John Smith 1960-10-02 President 500,000
2 Jane Doe 1982-05-05 Accountant 80,000
3 Jim Johnson NULL Office Manager 40,000
4 Tim Smith 1899-04-11 Janitor 95,000
Если я сейчас попытаюсь выполнить следующее утверждение, оно должно и не получится:
INSERT INTO employee (name, date_of_birth, title, salary)
VALUES ('Tim Smith', '1899-04-11', 'Janitor', '95,000')
Если я попробую это, это будет успешно:
INSERT INTO employee (name, title, salary)
VALUES ('Jim Johnson', 'Office Manager', '40,000')
И теперь мои данные будут выглядеть так:
id name date_of_birth title salary
1 John Smith 1960-10-02 President 500,000
2 Jane Doe 1982-05-05 Accountant 80,000
3 Jim Johnson NULL Office Manager 40,000
4 Tim Smith 1899-04-11 Janitor 95,000
5 Jim Johnson NULL Office Manager 40,000
Это не то, чего я хочу, но не могу сказать, что полностью не согласен с тем, что произошло. Если говорить с точки зрения математических множеств,
{'Tim Smith', '1899-04-11'} = {'Tim Smith', '1899-04-11'} <-- TRUE
{'Tim Smith', '1899-04-11'} = {'Jane Doe', '1982-05-05'} <-- FALSE
{'Tim Smith', '1899-04-11'} = {'Jim Johnson', NULL} <-- UNKNOWN
{'Jim Johnson', NULL} = {'Jim Johnson', NULL} <-- UNKNOWN
Я думаю, что MySQL говорит: "Так как я не знаю, что Джим Джонсон с NULL Дата рождения еще не в этой таблице, я добавлю его ".
Мой вопрос: как я могу предотвратить дубликаты, хотя date_of_birth не всегда известно? Лучшее, что я придумала, это двигаться date_of_birth на другой стол. Проблема с этим, однако, в том, что у меня могут оказаться, скажем, два кассира с одинаковым именем, названием и зарплатой, разными датами рождения и без возможности хранить их обоих без дубликатов.
11 ответов
Основное свойство уникального ключа заключается в том, что он должен быть уникальным. Создание части этого ключа Nullable разрушает это свойство.
Есть два возможных решения вашей проблемы:
Одним из неправильных способов было бы использовать магическую дату для обозначения неизвестного. Это просто поможет вам преодолеть "проблему" СУБД, но не решит проблему в логическом смысле. Ожидайте проблемы с двумя записями "Джона Смита", имеющими неизвестные даты рождения. Эти парни одно и то же или они уникальные личности? Если вы знаете, что они разные, то вы возвращаетесь к той же старой проблеме - ваш уникальный ключ просто не уникален. Даже не думайте о назначении целого ряда магических дат для представления "неизвестных" - это действительно дорога в ад.
Лучше создать атрибут EmployeeId в качестве суррогатного ключа. Это просто произвольный идентификатор, который вы присваиваете лицам, которые, как вы знаете, являются уникальными. Этот идентификатор часто является целочисленным значением. Затем создайте таблицу Employee, чтобы связать EmployeeId (уникальный, ненулевой ключ) с тем, что, по вашему мнению, является зависимым атрибутом, в данном случае это Имя и Дата рождения (любой из которых может быть обнуляемым). Используйте суррогатный ключ EmployeeId везде, где вы ранее использовали имя / дату рождения. Это добавляет новую таблицу в вашу систему, но решает проблему неизвестных значений надежным способом.
Рекомендую создать дополнительный столбец таблицы checksum который будет содержать хэш MD5 name а также date_of_birth, Удалите уникальный ключ (name, date_of_birth) потому что это не решает проблему. Создайте один уникальный ключ на контрольной сумме.
ALTER TABLE employee
ADD COLUMN checksum CHAR(32) NOT NULL;
UPDATE employee
SET checksum = MD5(CONCAT(name, IFNULL(date_of_birth, '')));
ALTER TABLE employee
ADD UNIQUE (checksum);
Это решение создает небольшие технические издержки, потому что для каждой вставленной пары вам нужно генерировать хеш (то же самое для каждого поискового запроса). Для дальнейших улучшений вы можете добавить триггер, который будет генерировать хеш для каждой вставки:
CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employee
FOR EACH ROW
IF new.checksum IS NULL THEN
SET new.checksum = MD5(CONCAT(new.name, IFNULL(new.date_of_birth, '')));
END IF;
Я думаю, что MySQL делает это прямо здесь. Некоторые другие базы данных (например, Microsoft SQL Server) рассматривают NULL как значение, которое может быть вставлено только один раз в столбец UNIQUE, но лично я считаю это странным и неожиданным поведением.
Однако, так как это то, что вы хотите, вы можете использовать какое-то "волшебное" значение вместо NULL, например, дату в прошлом
Ваша проблема отсутствия дубликатов, основанных на имени, не решаема, потому что у вас нет естественного ключа. Установка фиктивной даты для людей, чья дата рождения неизвестна, не решит вашу проблему. Джон Смит, родившийся в 1900/01/01, все еще будет другим человеком, чем Джон Смит, родившийся в 1960/03/09.
Я работаю с данными имен из крупных и малых организаций каждый день, и я могу заверить вас, что у них всегда два разных человека с одинаковыми именами. Иногда с одинаковым названием работы. Дата рождения также не является гарантией уникальности, множество Джона Смитов родились в один день. Черт возьми, когда мы работаем с офисными данными врачей, у нас часто есть два доктора с одинаковым именем, адресом и номером телефона (комбинации отца и сына)
Лучше всего иметь идентификатор сотрудника, если вы вводите данные сотрудника, чтобы идентифицировать каждого сотрудника однозначно. Затем проверьте уникальное имя в пользовательском интерфейсе и, если есть одно или несколько совпадений, спросите пользователя, имел ли он в виду их, и если он говорит "нет", вставьте запись. Затем создайте процесс отключения, чтобы исправить проблемы, если кто-то случайно назначит два идентификатора.
Есть еще один способ сделать это. Добавление столбца (не обнуляемого) для представления значения String столбца date_of_birth. Новое значение столбца будет ""(пустая строка), если date_of_birth равен нулю.
Мы называем столбец как date_of_birth_str и создаем уникального сотрудника ограничения (name, date_of_birth_str). Таким образом, когда две записи приходят с одинаковым именем и нулевым значением date_of_birth, уникальное ограничение по-прежнему работает.
Но усилия по обслуживанию двух столбцов с одинаковым смыслом, а также ущерб производительности нового столбца, должны быть тщательно рассмотрены.
Вы можете добавить сгенерированный столбец, в котором NULLзначение заменяется неиспользованной константой, например нулем. Затем вы можете применить уникальное ограничение к этому столбцу:
CREATE TABLE employee (
name VARCHAR(50) NOT NULL,
date_of_birth DATE,
uq_date_of_birth DATE AS (IFNULL(date_of_birth, '0000-00-00')) UNIQUE
);
Я искал одно решение, и предложение Alexander Yancharuk было для меня хорошей идеей. Но в моем случае мои столбцы являются внешними ключами, а employee_id может иметь значение null.
У меня такая структура:
+----+---------+-------------+
| id | room_id | employee_id |
+----+---------+-------------+
| 1 | 1 | NULL |
| 2 | 2 | 1 |
+----+---------+-------------+
И room_id с employee_id NULL не может быть продублирован
Я решил добавить триггер перед вставкой, например:
DELIMITER $$
USE `db`$$
CREATE DEFINER=`root`@`%` TRIGGER `db`.`room_employee` BEFORE INSERT ON `room_employee` FOR EACH ROW
BEGIN
IF EXISTS (
SELECT room_id, employee_id
FROM room_employee
WHERE (NEW.room_id = room_employee.room_id AND NEW.employee_id IS NULL AND room_employee.employee_id IS NULL)
) THEN
CALL `The room Can not be duplicated on room employee table`;
END IF;
END$$
DELIMITER ;
Я также добавил ограничение, уникальное для room_id и employee_id
У меня была похожая проблема с этим, но с изюминкой. В вашем случае у каждого сотрудника есть день рождения, хотя он может быть неизвестен. В этом случае логично, чтобы система присваивала два значения сотрудникам с неизвестными днями рождения, но в остальном идентичной информацией. Принятый ответ NealB очень точный.
Однако проблема, с которой я столкнулся, заключалась в том, что поле данных не обязательно имело значение. Например, если вы добавили поле "name_of_spouse" в вашу таблицу, не обязательно будет значение для каждой строки таблицы. В этом случае первая точка пули NealB ("неправильный путь") действительно имеет смысл. В этом случае строка "Нет" должна быть вставлена в столбец name_of_spouse для каждой строки, в которой не было известного супруга.
Ситуация, когда я столкнулся с этой проблемой, заключалась в написании программы с базой данных для классификации IP-трафика. Целью было создать график IP-трафика в частной сети. Каждый пакет был помещен в таблицу базы данных с уникальным индексом соединения на основе его источника и адреса ip, источника и адреса порта, транспортного протокола и протокола приложения. Однако многие пакеты просто не имеют протокола приложения. Например, все пакеты TCP без протокола приложения должны классифицироваться вместе и должны занимать одну уникальную запись в индексе соединений. Это потому, что я хочу, чтобы эти пакеты формировали один край моего графа. В этой ситуации я воспользовался собственным советом сверху и сохранил строку "Нет" в поле протокола приложения, чтобы гарантировать, что эти пакеты образуют уникальную группу.
Идеальным решением была бы поддержка британских систем на основе функций, но это становится более сложным, поскольку mySQL также должен был бы поддерживать индексы на основе функций. Это предотвратит необходимость использования "поддельных" значений вместо NULL, а также позволит разработчикам решать, как обрабатывать значения NULL в Великобритании. К сожалению, mySQL в настоящее время не поддерживает такую функциональность, о которой я знаю, поэтому у нас остались обходные пути.
CREATE TABLE employee(
name CHAR(50) NOT NULL,
date_of_birth DATE,
title CHAR(50),
UNIQUE KEY idx_name_dob (name, IFNULL(date_of_birth,'0000-00-00 00:00:00'))
);
(Обратите внимание на использование функции IFNULL() в определении уникального ключа)
Я думаю, что фундаментальный вопрос здесь заключается в том, что вы на самом деле имеете в виду под
ВСТАВЬТЕ В ЗНАЧЕНИЯ сотрудника (имя, должность, зарплата) («Джим Джонсон», «Офис-менеджер», «40 000»)
Ваше собственное определение человека — это имя И дата рождения, так что же означает это утверждение в этом контексте? Я бы сказал, что решение вашей проблемы состоит в том, чтобы запретить вставку половинных идентификаторов, подобных приведенному выше, путем добавления NOT NULL как в столбцы вашего имени, так и в столбцы date_of_birth. Таким образом, оператор завершится ошибкой и заставит вас вводить полные идентификаторы, а уникальный ключ выполнит свою работу, чтобы вы не ввели одно и то же лицо дважды.
Проще говоря, роль уникального ограничения заключается в создании поля или столбца. Нуль уничтожает это свойство, так как база данных обрабатывает нуль как неизвестный
Порядок, чтобы избежать дубликатов и разрешить null:
Сделать уникальный ключ первичным ключом