外部キーとして参照されている列にauto_incrementをつけ忘れた場合

MariaDB

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