Способы решения ошибки Duplicate key в MySQL Workbench

Быстрый ответ

Для предупреждения ошибок по причине использования уникальных ключей, применяйте условные операции SQL. При вставке данных используйте предложение ON DUPLICATE KEY UPDATE:

-- В случае обычных вставок возникнет ошибка, если будет обнаружен дубликат ключа.
-- С использованием "ON DUPLICATE KEY UPDATE" такой проблемы не будет.
INSERT INTO table_name (unique_column, other_column) VALUES ('unique_value', 'value')
ON DUPLICATE KEY UPDATE other_column = VALUES(other_column);

А при обновлении данных осуществляйте подзапросы, чтобы гарантировать, что новое значение не нарушает уникальность:

-- Обновление записи с проверкой на уникальность значения.
UPDATE table_name SET unique_column = 'new_unique_value'
WHERE id = some_id AND 'new_unique_value' NOT IN (SELECT unique_column FROM table_name WHERE id != some_id);

При операциях вставки конфликты уникальности решаются с помощью INSERT ... ON DUPLICATE KEY UPDATE, а при операциях обновления требуется дополнительная проверка на отсутствие значения в уникальных столбцах.

Разъяснение ошибки 121, или "дублирование имен ограничений"

Сталкивались ли вы с ошибкой 121 в MySQL? Это значит, что имеются дубликаты в названиях CONSTRAINT. Ограничение является ключевым элементом в области SQL и требует уникальности на уровне всей базы данных.

-- Присваиваем ограничениям уникальные имена.
ALTER TABLE your_table_name
ADD CONSTRAINT constraint_name_unique
FOREIGN KEY (column_name) REFERENCES another_table(id);

Для устранения ошибки 121, убедитесь, что имена ваших ограничений уникальны. Иногда может потребоваться переименование существующего ограничения:

-- Ограничения требуют особого подхода и ясных имен.
ALTER TABLE your_table_name
DROP FOREIGN KEY old_constraint_name,
ADD CONSTRAINT new_constraint_name_unique
FOREIGN KEY (column_name) REFERENCES another_table(id);

Когда целостность ключей на первом месте

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

• Попытка переиспользования первичного ключа: Дублирование значения в поле первичного ключа.
• Несоответствие внешнего ключа: Ссылка на отсутствующее значение в родительской таблице.
• Дублирование уникальных ключей: Вновь использование уникального значения, которое было введено в другой записи ранее.

Визуализация

Ошибка нарушения уникальности ключа напоминает о столкновении во время футбольного матча:

🏢 Таблица базы данных: | Ключ 🔑 | Данные 📊 |
                         -------------------
                         |  01   |  A       |
                         |  02   |  B       |

При попытке добавления записи с дублирующимся ключом:

💾 Запись: | Ключ 🔑 | Данные 📊 |
               -------------------
               |  02   |  C       |
               // Ошибка: 🚫 Дублирующий ключ '02'

Каждый ключ в таблице должен быть уникальным, так, как уникальны отпечатки пальцев.

🔑 Уникальный ключ = Уникальный отпечаток пальца👆
👆 Попытка записи: 🚫🔄👆 Дублирование отпечатка.
// Доступ запрещен! Дубликаты ключей не допустимы, так же как невозможно существование двух агентов 007 с одинаковым именем.

В базах данных каждая запись должна быть уникальной, чтобы избежать конфликта юридических лиц.

Создание структуры базы данных без дубликатов ключей

Профилактика: Создавайте стабильную архитектуру базы данных с соответствующими установленными уникальными ограничениями.

1. Первичные ключи: Используйте автоинкрементацию или UUID для обеспечения уникальности.
2. Уникальные индексы: Их можно применять для обозначения глобально уникальных данных, включая электронные адреса.
3. Ограничения внешнего ключа: Обеспечивайте отказоустойчивость данных благодаря строгим ограничениям.

Превентивная проверка: Заранее проводите запросы для выявления возможных нарушений уникальности. Это похоже на процедуру контроля безопасности в аэропорту:

-- Контроль уникальности, как проверка безопасности в аэропорту.
SELECT COUNT(*)
FROM table_name
WHERE column_name = 'potential_unique_value';

Любой результат, больший нуля, указывает на потенциальный конфликт ключей.

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

Стратегии обработки конфликтов ключей

Обработка исключений: Настройте ваше приложение на корректное реагирование на исключения SQL, связанные с ограничениями ключей, и адекватное взаимодействие с пользователем.

Готовность к „Плану Б“: В случае возникновения конфликта рассмотрите возможность добавления уникализатора (например, временной метки или случайной строки) и повторите операцию.

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

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 15.2.7.2 Выражение INSERT ... ON DUPLICATE KEY UPDATE — документация по использованию конструкции для обработки дубликатов.
2. sql – Как сделать 'вставку при условии отсутствия' в MySQL? – Stack Overflow — обсуждения и решения сообщества для условных вставок в MySQL.
3. INSERT ON DUPLICATE KEY UPDATE – База знаний MariaDB — руководство по работе с дубликатами в MariaDB, системе, близкой к MySQL.
4. PostgreSQL: Документация: 16: INSERT — документация об 'ON CONFLICT', предназначенном для предотвращения конфликтов.
5. Осторожно при использовании оператора MERGE в SQL Server — рекомендации по применению MERGE в SQL Server.
6. Уникальное ограничение SQL — учебник по использованию UNIQUE для обеспечения уникальности ключей в базах данных.
7. MERGE (Transact-SQL) – SQL Server | Microsoft Learn — руководство по использованию оператора MERGE для борьбы с дубликатами в SQL Server.