I try to add a generated stored column to existing table running the following SQL command:
ALTER TABLE routes ADD routes_hash char(32)
AS (MD5(CONCAT(`traveler_id`, `track`))) stored;
MySQL returns the error message:
ERROR 1025 (HY000): Error on rename of './travelers/#sql-558_69' to './travelers/routes'
(errno: 150 - Foreign key constraint is incorrectly formed)
Mysql version is 5.7.20. Here is a database structure:
CREATE TABLE `travelers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `routes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`traveler_id` int(11) NOT NULL,
`track` multilinestring NOT NULL,
PRIMARY KEY (`id`),
KEY `traveler_id` (`traveler_id`),
CONSTRAINT `routes_ibfk_1` FOREIGN KEY (`traveler_id`)
REFERENCES `travelers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I have not found any restrictions in MySQL documentation that forbid usage of the column that participates in foreign key constraint as a part of expression used for build a generated column. However, if I exclude traveler_id from the generated column expression for routes_hash the column is created fine. Is this a bug of MySQL or I am missing something about generated column usage?
