外部キーとして参照されている列にauto_incrementをつけ忘れた場合
MariaDB 10.1
備忘録、メモ
構築済みのテーブルの主キー列にAUTO_INCREMENTをつけ忘れていたなどの理由により、後からAUTO_INCREMENTを付与したいとする。
その列が他のテーブルから外部キー制約で参照されていると、単純にALTER TABLEしようとしても以下のようなエラーが発生する。
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'child1_fk1' of table 'mydb.child1'
【解決策】
- 関連するテーブルをすべてWRITEロックする
- 子テーブルの外部キー制約を一時的に削除する
- ALTER TABLEで親テーブルの列にAUTO_INCREMENTをつける
- 外部キー制約を戻す(子テーブルにもう一度外部キー制約をつける)
- テーブルをアンロックする
lock tables parent write, child1 write, child2 write, child3 write; alter table child1 drop foreign key child1_fk1; alter table child2 drop foreign key child2_fk1; alter table child3 drop foreign key child3_fk1; alter table parent modify person_id int auto_increment; alter table child1 add foreign key(child1_fk1) references parent(person_id); alter table child2 add foreign key(child2_fk1) references parent(person_id); alter table child3 add foreign key(child3_fk1) references parent(person_id); unlock tables;
【参考】
https://stackoverflow.com/questions/13606469/cannot-change-column-used-in-a-foreign-key-constraint