Mysql ошибка 1452 — Не удается добавить или обновить дочернюю строку: ограничение внешнего ключа не удается
У меня немного странная проблема. Я пытаюсь добавить внешний ключ в одну таблицу, которая ссылается на другую, но по какой-то причине он не работает. Из-за моего ограниченного знания MySQL, единственное, что может подозревать, — это наличие внешнего ключа в другой таблице, ссылающейся на ту, на которую я пытаюсь ссылаться.
Вот картина моих табличных отношений, сгенерированных через phpMyAdmin:
Отношения
Я сделал SHOW CREATE TABLE запрос по обеим таблицам, sourcecodes_tags это таблица с внешним ключом, sourcecodes является ссылочной таблицей.
CREATE TABLE `sourcecodes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`language_id` int(11) unsigned NOT NULL,
`category_id` int(11) unsigned NOT NULL,
`title` varchar(40) CHARACTER SET utf8 NOT NULL,
`description` text CHARACTER SET utf8 NOT NULL,
`views` int(11) unsigned NOT NULL,
`downloads` int(11) unsigned NOT NULL,
`time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `language_id` (`language_id`),
KEY `category_id` (`category_id`),
CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
CREATE TABLE `sourcecodes_tags` (
`sourcecode_id` int(11) unsigned NOT NULL,
`tag_id` int(11) unsigned NOT NULL,
KEY `sourcecode_id` (`sourcecode_id`),
KEY `tag_id` (`tag_id`),
CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Было бы здорово, если бы кто-нибудь мог рассказать мне, что здесь происходит, у меня не было формального обучения или чего-то еще с MySQL 🙂
Благодарю.
Редактировать: Это код, который генерирует ошибку:
ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE
Решение
Вполне вероятно ваш sourcecodes_tags таблица содержит sourcecode_id значения, которых больше нет в вашем sourcecodes Таблица. Вы должны сначала избавиться от них.
Вот запрос, который может найти эти идентификаторы:
SELECT DISTINCT sourcecode_id FROM
sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id
WHERE sc.id IS NULL;
Другие решения
У меня была та же проблема с моей базой данных MySQL, но в конце концов я получил решение, которое сработало для меня.
Так как в моей таблице все было нормально с точки зрения mysql (обе таблицы должны использовать движок Innodb, а типы данных каждого столбца должны быть одного типа, которые участвуют в ограничении внешнего ключа).
Единственное, что я сделал, — отключил проверку внешнего ключа, а затем включил ее после выполнения операции с внешним ключом.
Шаги, которые я предпринял:
mysql> SET foreign_key_checks = 0; mysql> alter table tblUsedDestination add constraint f_operatorId foreign key(iOperatorId) references tblOperators (iOperatorId); Query OK, 8 rows affected (0.23 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> SET foreign_key_checks = 1;
использование NOT IN чтобы найти, где ограничения сдерживающий:
SELECT column FROM table WHERE column NOT IN
(SELECT intended_foreign_key FROM another_table)
Итак, более конкретно:
SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN
(SELECT id FROM sourcecodes)
РЕДАКТИРОВАТЬ: IN а также NOT IN операторы, как известно, намного быстрее, чем JOIN операторы, а также гораздо проще построить и повторить.
Обрежьте таблицы и попробуйте добавить ограничение FK.
Я знаю, что это решение немного неудобно, но оно работает на 100%. Но я согласен, что это не идеальное решение для решения проблемы, но я надеюсь, что это поможет.
Для меня эта проблема была немного другой и супер легко проверить и решить.
Вы должны убедиться, что ОБА из ваших таблиц InnoDB. Если одна из таблиц, а именно справочная таблица, является MyISAM, ограничение не будет выполнено.
SHOW TABLE STATUS WHERE Name = 't1';
ALTER TABLE t1 ENGINE=InnoDB;
Это также происходит при установке внешнего ключа для parent.id для child.column, если child.column уже имеет значение 0, а значение parent.id отсутствует 0
Вы должны убедиться, что каждый child.column имеет значение NULL или имеет значение, которое существует в parent.id
И теперь, когда я прочитал заявление, которое написал nos, это то, что он проверяет.
У меня была такая же проблема сегодня. Я проверил четыре вещи, некоторые из которых уже упоминались здесь:
-
Есть ли какие-либо значения в вашем дочернем столбце, которых нет в родительском столбце (кроме NULL, если дочерний столбец имеет значение NULL)?
-
У дочерних и родительских столбцов одинаковый тип данных?
-
Есть ли индекс родительского столбца, на который вы ссылаетесь? MySQL, кажется, требует этого по соображениям производительности (http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html)
-
И этот решил для меня: есть ли в обеих таблицах одинаковое сопоставление?
У меня был один стол в utf-8, а другой в iso-что-то. Это не сработало. После изменения iso-таблицы на сопоставление utf-8 ограничения могут быть добавлены без проблем. В моем случае phpMyAdmin даже не отображал дочернюю таблицу в iso-кодировке в раскрывающемся списке для создания ограничения внешнего ключа.
Кажется, есть недопустимое значение для столбца, например 0, которое не является допустимым внешним ключом, поэтому MySQL не может установить для него ограничение внешнего ключа.
Вы можете выполнить следующие действия:
-
Удалите столбец, для которого вы пытались установить ограничение FK.
-
Добавьте его еще раз и установите его значение по умолчанию как NULL.
-
Попробуйте снова установить ограничение внешнего ключа.