Как исправить ошибку MySQL не может добавить ограничение внешнего ключа?

Как исправить ошибку « MySQL не может добавить ограничение внешнего ключа »? Вы можете получить следующее сообщение об ошибке при попытке добавить ограничение внешнего ключа в таблицу MySQL: « ОШИБКА 1215 (HY000): невозможно добавить ограничение внешнего ключа ».

Подобная ошибка является очень общей и не предоставляет конкретных сведений о конкретной причине, по которой MySQL не может вставить ограничение внешнего ключа в таблицу. Чтобы решить эту проблему, вам необходимо определить точную причину проблемы.

В этой статье будет объяснена причина ошибки MySQL «# 1215 — Невозможно добавить ограничение внешнего ключа», как найти причину и как ее исправить четырьмя эффективными методами.

В чем причина ошибки MySQL «# 1215 — невозможно добавить ограничение внешнего ключа»?

У этой ошибки могут быть разные причины. Чтобы определить причину этой ошибки, лучше всего изучить раздел LATEST FOREIGN KEY ERROR в SHOW ENGINE INNODB STATUS.

Так вы лучше поймете проблему. Вы сможете определить свой случай из следующего списка:

Обычно при загрузке дампов таблицы или индекса, на который ссылается ограничение, не существует.

Иногда клиенты таблицы базы данных, которые хотят добавить ограничения, не существуют, что приводит к сообщению об ошибке 1215.

Однако нет признаков того, что таблица не существует, поэтому рекомендуется использовать команду, чтобы определить, какие таблицы доступны в базе данных.

Запустите SHOW TABLES или SHOW CREATE TABLE для каждой из родительских таблиц. Любая таблица, возвращающая ошибку 1146, указывает на то, что таблицы были созданы в неправильном порядке.

Решение состоит в том, чтобы запустить отсутствующий CREATE TABLE и повторить попытку или временно отключить проверки внешнего ключа. Важно выполнить этот шаг во время восстановления резервных копий, в которых могут присутствовать циклические ссылки.

Запустите следующий код, чтобы исправить ошибку:

 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; -- restore your backup within THIS session SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Таблицы или индексы в ограничениях ссылаются на неправильное использование кавычек

Если вы хотите диагностировать эту ошибку, проверьте каждое объявление FOREIGN KEY и убедитесь, что вокруг квалификаторов объектов нет кавычек или что вокруг таблицы и по одной паре имен каждого столбца есть кавычки.

Это можно решить, либо ничего не цитируя, либо цитируя таблицу и столбец отдельно.

В локальном ключе, внешней таблице или столбце в ссылках на ограничение есть опечатка.

Эту ошибку можно диагностировать, запустив команды SHOW TABLES и SHOW COLUMNS и сравнив строки с указанными в объявлении REFERENCES. Опечатку следует исправить, как только она будет обнаружена.

Столбец, на который ссылается ограничение, не имеет того же типа или ширины, что и внешний столбец.

Для диагностики используйте родительский элемент SHOW CREATE TABLE, чтобы убедиться, что локальный столбец и столбец, на который ссылаются, содержат данные одного типа и ширины.

Эту проблему можно решить, отредактировав оператор DDL, чтобы убедиться, что определение столбца в дочерней таблице совпадает с определением в родительской таблице.

Посторонний предмет не является КЛЮЧОМ любого рода

Чтобы диагностировать эту проблему, используйте родительский элемент SHOW CREATE TABLE, чтобы проверить часть REFERENCES таблицы, чтобы убедиться, что столбец, указывающий на нее, не индексируется.

Исправьте проблему, сделав столбец либо KEY, UNIQUE KEY, либо PRIMARY KEY.

Внешний ключ представляет собой многостолбцовый PK или UK, где указанный столбец не является самым левым.

Чтобы диагностировать эту ошибку, выполните родительскую команду SHOW CREATE TABLE, чтобы определить, указывает ли часть REFERENCES на столбец, который присутствует в некоторых многостолбцовых индексах, но не является крайним левым в его определении.

Вы можете решить эту проблему, добавив индекс в родительскую таблицу, в которой указанный столбец является крайним левым или единственным столбцом.

Различные кодировки/сопоставления между двумя таблицами/столбцами

Чтобы диагностировать проблему, запустите SHOW CREATE TABLE parent и убедитесь, что части CHARACTER SET и COLLATE дочернего столбца совпадают с частями родительской таблицы.

Чтобы исправить это, убедитесь, что DDL дочерней таблицы соответствует набору символов и сопоставлению ее родительской таблицы/столбца, или ИЗМЕНИТЕ родительскую таблицу, чтобы ее определение соответствовало определению дочерней.

Родительская таблица не использует InnoDB

Проблему можно диагностировать, запустив SHOW CREATE TABLE parent и проверив ENGINE=INNODB.

ИЗМЕНИТЕ родительскую таблицу, чтобы переключиться с MySQL на InnoDB, чтобы решить эту проблему.

Как найти причину ошибки «Невозможно добавить ограничение внешнего ключа»?

Эта ошибка не указывает конкретную причину неспособности MySQL реализовать ограничение внешнего ключа для таблицы, что является очень общим.

Чтобы помочь решить эту проблему, вам необходимо определить точную причину. Вы можете сделать это, выполнив следующий запрос в клиенте MySQL:

 SHOW ENGINE INNODB STATUS;

Этот запрос должен возвращать три поля: Тип, Имя и Статус. ПОСЛЕДНЮЮ ОШИБКУ ВНЕШНЕГО КЛЮЧА можно определить, развернув столбец состояния результата.

Этот раздел направлен на то, чтобы определить, почему последний запрос на изменение не удался, чтобы вы могли исправить проблему.

Обычно причиной этой проблемы является несоответствие между типами столбцов в основной таблице и сторонней таблице.

Проблема также может быть вызвана несоответствием между типами движка двух таблиц, такими как MyISAM и InnoDB.

Это также возможно из-за разных сопоставлений между таблицами: одна использует UTF-8, а другая — latin1.

Следует обратить внимание на следующие факторы:

Механизм : Каждая таблица должна быть настроена с одним и тем же механизмом базы данных, например, InnoDB.

Тип данных : в одной таблице оба столбца должны иметь одинаковый datatype. int(11) datatype. int(11) ", тогда как в другом smallint(5) вызовет проблемы.

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

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

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

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

Как исправить ошибку MySQL «# 1215 — невозможно добавить ограничение внешнего ключа» (4 метода)?

Обсудив различные причины появления этого сообщения об ошибке, мы перешли к следующему шагу. Эту ошибку необходимо исправить, поэтому давайте посмотрим, как это сделать четырьмя эффективными способами.

Способ 1: проверить наличие стола

Чтобы начать устранение ошибки, вы можете проверить, существует ли таблица, выполнив следующую команду:

 show tables;

Из этого кода видно, что ограничение могло быть создано, поскольку таблица была доступна.

Способ 2: убедитесь, что родительская таблица использует InnoDB

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

 SHOW CREATE TABLE parent

В результате вы обнаружите, что InnoDB не является механизмом хранения. Поскольку ENGINE=MYISAM был установлен, вы можете изменить механизм хранения, выполнив следующую команду:

 ALTER TABLE parent ENGINE=INNODB;

Наконец-то можно исправить ошибку с помощью этого метода.

Способ 3: проверьте, есть ли неуместные кавычки

Для каждого объявления FOREIGN KEY убедитесь, что квалификаторы объектов не заключены в кавычки или что имена таблиц и столбцов заключены в отдельные кавычки.

При этом методе необходимо либо ничего не цитировать, либо цитировать столбец и таблицу отдельно, чтобы исправить ошибку.

Способ 4: проверка того, существует ли ключ или нет

При вводе команд MySQL необходимо следить за тем, чтобы вводилась правильная команда. Перекрестная проверка гарантирует, что клиент не ссылается на несуществующий ключ таблицы в целевой таблице.

Поэтому необходимо поправлять клиентов, как пользоваться ключом на столе. Возможно, столбец, используемый для ON DELETE SET NULL, в некоторых случаях не имеет нулевого определения.

Чтобы предотвратить ошибку MySQL 1215, вы должны убедиться, что для столбца установлено нулевое значение по умолчанию.

Заключение

В этой статье объясняется причина ошибки MySQL «# 1215 — невозможно добавить ограничение внешнего ключа», как найти причину и как ее исправить четырьмя эффективными методами.

Спасибо, что нашли время, чтобы рассмотреть эту статью. Мы рады представить его вам. В разделе комментариев, пожалуйста, оставляйте любые вопросы или комментарии.

Если вы хотите быть в курсе последних новостей, ставьте лайк и следите за нашими страницами в Facebook и Twitter.