Как побороть foreign key constraint violation в MySQL?

Question

[coderlex]

Никак не разберусь почему пробрасывает исключение constraint violation. Таблица и запрос простые:

CREATE TABLE `taxonomy_vocabulary` (
  `name` varchar(32),
  `parent_name` varchar(32),
  PRIMARY KEY (`name`),
  KEY `fk-taxonomy_vocabulary-parent_name` (`parent_name`)
);

ALTER TABLE `taxonomy_vocabulary`
  ADD CONSTRAINT `fk-taxonomy_vocabulary-parent_name` FOREIGN KEY (`parent_name`) REFERENCES `taxonomy_vocabulary` (`name`) ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO `taxonomy_vocabulary` (`name`, `parent_name`) VALUES
('test1', NULL),
('test2', 'test1');

UPDATE `taxonomy_vocabulary` SET `name`='test12345' WHERE `name`='test1';

Последняя строка с update бросает foreign key violation по единственному foreign ключу таблицы. Почему это происходит?

Answer 1

[Станислав Макаров]

dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key...

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates.

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

Answer 2

[Aleksey Ratnikov]

Ограничитель FOREIGN KEY - это контракт, обеспечивающий целостность данных. Работает он так: повесив ограничить FOREIGN KEY на столбец parent_name в таблице taxonomy_vocabulary со ссылкой на поле name в таблице taxonomy_vocabulary, вы говорите, что taxonomy_vocabulary.parent_name может принимать только такие значение, которые есть в taxonomy_vocabulary.name.
Таким образом, БД вас спасает от записи ерунды в данной поле, соблюдая указанный вами же контракт.

Comments

[coderlex]

Но ведь у constraint'a на parent_name стоит ON UPDATE CASCADE, что по идее означает обновление parent_name на новое значение при обновлении соответствующей записи в родительской таблице (в данном случае это одна и та же таблица).