Как я могу объединить две избыточные записи в таблице MySQL, сохранив все отношения PK/FK?
Допустим, у меня есть таблица customers со следующими полями и записями:
id first_name last_name email phone
------------------------------------------------------------------------
1 Michael Turley [email protected] 555-123-4567
2 John Dohe [email protected]
3 Jack Smith [email protected] 555-555-5555
4 Johnathan Doe 123-456-7890
Существует несколько других таблиц, таких как orders, rewards, receipts которые имеют внешние ключи customer_id, относящиеся к этой таблице customers.id.
Как вы можете видеть, в своей бесконечной мудрости мои пользователи создали дубликаты записей для Джона Доу, в комплекте с непоследовательным написанием и отсутствующими данными. Администратор замечает это, выбирает клиентов 2 и 4 и нажимает "Объединить". Затем им будет предложено выбрать какое значение является правильным для каждого поля и т. Д. и т. Д., И мой PHP определяет, что объединенная запись должна выглядеть следующим образом:
id first_name last_name email phone
------------------------------------------------------------------------
? John Doe [email protected] 123-456-7890
Давайте предположим, что мистер Доу разместил несколько заказов, заработал награды, сгенерировал квитанции.. но некоторые из них были связаны с идентификатором 2, а некоторые были связаны с идентификатором 4. Объединенная строка должна соответствовать всем внешним ключам в других таблицах, которые соответствовали исходным строкам.
Вот где я не уверен, что делать. Мой инстинкт подсказывает мне делать это:
DELETE FROM customers WHERE id = 4;
UPDATE customers
SET first_name = 'John',
last_name = 'Doe',
email = '[email protected]',
phone = '123-456-7890'
WHERE id = 2;
UPDATE orders, rewards, receipts
SET customer_id = 2
WHERE customer_id = 4;
Я думаю, что это сработало бы, но если позже я добавлю другую таблицу с внешним ключом customer_id, мне нужно не забыть вернуться и добавить эту таблицу во второй запрос на ОБНОВЛЕНИЕ в моей функции слияния, или риск потери целостности.
Должен быть лучший способ сделать это.
4 answers
В качестве дополнения к моему комментарию:
use information_schema;
select table_name from columns where column_name = 'customer_id';
Затем просмотрите полученные таблицы и обновите их соответствующим образом.
Лично я бы использовал ваше инстинктивное решение, так как это может быть опасно, если есть таблицы, содержащие столбцы customer_id, которые необходимо исключить.
Я получил здесь форму Google, это мои 2 цента:
SELECT `TABLE_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE REFERENCED_TABLE_SCHEMA='DATABASE'
AND REFERENCED_TABLE_NAME='customers'
AND REFERENCED_COLUMN_NAME='customer_id'
Добавьте базу данных для страховки (вы никогда не узнаете, когда кто-то скопирует базу данных).
Вместо того, чтобы искать имя столбца, здесь мы рассмотрим сами внешние ключи
Если вы измените ограничения на удаление на ограничение, ничто не может быть удалено до удаления/переноса дочерних элементов
Короткий ответ таков: нет лучшего способа (который я могу придумать).
Это компромисс. Если вы обнаружите, что таких случаев много, возможно, стоит потратить некоторое время на написание более надежного алгоритма для проверки существующих клиентов перед добавлением нового (т. Е. Проверка вариантов имен/фамилий, представление их тому, кто добавляет клиента, спрашивая их 2 или 3 раза, действительно ли они уверены, что хотят добавить этого нового клиента и т. Д.). Если есть какие-либо варианты, если вы не уверены, что хотите добавить этого нового клиента, и т. Д. не так много таких случаев, возможно, не стоит тратить на это время.
Короче говоря, ваш подход - единственный способ, который я могу придумать. Я бы на самом деле удалил обе записи и создал новую с объединенными данными, в результате чего был бы получен новый идентификатор клиента, а не повторное использование старого, но это просто личное предпочтение - функционально это то же самое, что и ваш подход. Вам все равно нужно не забыть вернуться и изменить свою функцию слияния, чтобы отразить новые отношения на customer.id поле.
Как минимум, чтобы предотвратить любые триггеры при удалении, вызывающие некоторый каскадный эффект, я бы сначала сделал
Обновить некоторую таблицу, задающую идентификатор клиента = правильное значение, где идентификатор клиента = неправильное значение
(сделайте это во всех таблицах)...
ЗАТЕМ Удалить из клиентов, где идентификатор клиента = неправильное значение
Что касается повторяющихся данных... Попытайтесь выяснить, какой "Уилл Смит, Билл Смит, Уильям Смит", если вам не хватает определенной информации... Некоторые могут быть совершенно законными другими люди.