How to overcome foreign key constraint violation in MySQL?

Question

MySQL

I will not understand in any way why the constraint violation exception is thrown. The table and query are simple:

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';

The last line with update throws a foreign key violation on the table's only foreign key. Why is this happening?

Answer 1

dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key...
Don't get in the habit of relying on constraints and their behavior when developing an application. Update all the keys yourself - both in the child and in the parent entry.

Answer 2

The FOREIGN KEY constraint is a contract that enforces data integrity. It works like this: by placing a FOREIGN KEY constraint on the parent_name column in the taxonomy_vocabulary table with a reference to the name field in the taxonomy_vocabulary table, you say that taxonomy_vocabulary.parent_name can only take values ​​that are in taxonomy_vocabulary.name.
Thus, the database saves you from writing nonsense in this field, observing the contract you specified.