Description:
I am attempting to alter an existing table, and in the same query:
- rename a field that is used in a foreign key
- change a datatype
I am running 5.7, but also tried it in 8.0. Is it possible to do the below ALTER TABLE in a single query?
How to repeat:
mysql> USE test;
Database changed
mysql> CREATE TABLE a (a_id int PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE b (b_id int PRIMARY KEY, a_id int, c enum('1', '2'), CONSTRAINT FOREIGN KEY (a_id) REFERENCES a (a_id));
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2');
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2'), ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2'), ALGORITHM=COPY;
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
Description: I am attempting to alter an existing table, and in the same query: - rename a field that is used in a foreign key - change a datatype I am running 5.7, but also tried it in 8.0. Is it possible to do the below ALTER TABLE in a single query? How to repeat: mysql> USE test; Database changed mysql> CREATE TABLE a (a_id int PRIMARY KEY); Query OK, 0 rows affected (0.04 sec) mysql> CREATE TABLE b (b_id int PRIMARY KEY, a_id int, c enum('1', '2'), CONSTRAINT FOREIGN KEY (a_id) REFERENCES a (a_id)); Query OK, 0 rows affected (0.05 sec) mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2'); ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2'), ALGORITHM=INPLACE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2'), ALGORITHM=COPY; ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.