Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.10, 10.5, 10.6, 10.11, 11.4, 11.7
-
None
-
Ubuntu and Debian
Description
Trying to modify a column that is not part of any foreign key throws an error that the column can not be modified because it is part of a foreign key.
The prerequisites for the bug to manifest is to have a foreign key to another table for another column and a generated column before the column you are tryging to modify.
Here is a simple script to reproduce:
DROP TABLE IF EXISTS b_poses; |
DROP TABLE IF EXISTS b_users; |
|
|
CREATE TABLE `b_users` ( |
`USER_ID` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`USER_ID`) |
);
|
|
|
CREATE TABLE `b_poses` ( |
`POS_ID` int(11) NOT NULL AUTO_INCREMENT, |
`DELETE_FLAG` tinyint(1) NOT NULL DEFAULT 0, |
`DEL_UNQ` char(0) GENERATED ALWAYS AS (if(`DELETE_FLAG` = 0,'',NULL)) STORED, |
`FOR_USER_ID` int(11) DEFAULT NULL, |
`XRANDR_PRIMARY` varchar(10) NOT NULL DEFAULT '', |
PRIMARY KEY (`POS_ID`), |
KEY `b_poses_fk11` (`FOR_USER_ID`), |
CONSTRAINT `b_poses_fk11` FOREIGN KEY (`FOR_USER_ID`) REFERENCES `b_users` (`USER_ID`) ON DELETE CASCADE |
);
|
|
|
ALTER TABLE b_poses MODIFY COLUMN XRANDR_PRIMARY VARCHAR(25) NOT NULL DEFAULT ''; |
This throws an error
ERROR 1832 (HY000) at line 20: Cannot change column 'XRANDR_PRIMARY': used in a foreign key constraint 'test/b_poses_fk11'
|
If we move the generated column after the column we are trying to change the operation is successful.
The operation is also successful if instead of trying to make the column bigger we make it lower.