SQLite 3 alter table でテーブル名が変更できない (legacy_alter_table)

概要

以下のような手順を踏むとエラーになってテーブル名が変更できない。

1. pragma legacy_alter_table=ON になっている
2. ビュー、トリガー、外部キーなどで参照されているテーブルの名前を変更する
3. pragma legacy_alter_table=OFF にする
4. 2.で変更したテーブルの名前を変更しようとするとエラーになる

解決するには pragma legacy_alter_table=ON の状態でテーブル名と参照している側のテーブル名を揃える必要がある

環境

• Windows 10 64bit
• SQLite3 (3.33.0) Command-Line Shell

エラーの再現手順

sqlite> -- # 1.
sqlite> .tables
product     total_view

sqlite> -- # 2.
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(quantity)
from
    "product"
/* total_view("sum(quantity)") */;

sqlite> -- # 3.
sqlite> pragma legacy_alter_table=on;

sqlite> -- # 4.
sqlite> alter table product rename to item;
sqlite> .tables
item        total_view

sqlite> -- # 5. 
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(quantity)
from
    "product";

sqlite> -- # 6.
sqlite> select * from total_view limit 1;
Error: no such table: main.

sqlite> -- # 7.
sqlite> pragma legacy_alter_table=off;
sqlite> alter table item rename to product;
Error: error in view total_view: no such table: main.product
                

1. 「product」テーブルと「total_view」ビューがある。
2. 「total_view」ビューは「product」テーブルを参照している。
3. 「legacy_alter_table」をonにする。
4. 「product」テーブルの名前を「item」に変更する。
5. 「legacy_alter_table」がonの状態でテーブル名を変更したため、「total_view」の参照先は「product」のまま。
6. この状態で「total_view」をselectすると、当然エラーになる。
7. 「legacy_alter_table」をoffにして「item」テーブルの名前を変更しようとするとエラーになる。

解決方法

以下の2通り。1の方が楽。

1. 「legacy_alter_table」をonにしてテーブル名を直してからoffにする。
2. テーブルを参照しているビュー、トリガー、外部キーを再作成する。

1の実行例

sqlite> -- # 1.
sqlite> pragma legacy_alter_table=on;
sqlite> alter table item rename to product;

sqlite> -- # 2.
sqlite> pragma legacy_alter_table=off;
                

1. 「legacy_alter_table」をonにして参照されているテーブルの名前を変更する。
2. 「legacy_alter_table」をoffにする。

参考URL

• 2. ALTER TABLE RENAME
  公式のalter table ～ rename ～ to に関するドキュメント
  https://sqlite.org/lang_altertable.html#altertabrename
• Pragma legacy_alter_table
  公式の legacy_alter_table に関するドキュメント
  https://www.sqlite.org/pragma.html#pragma_legacy_alter_table

• あさはか備忘録: SQLite3 テーブル名を変更する
  https://sfnovicenotes.blogspot.com/2020/12/sqlite3.html
• あさはか備忘録: SQLite3 legacy_alter_tableとは？
  https://sfnovicenotes.blogspot.com/2020/11/sqlite3-json.html