D1 では参照されているテーブルを作り直せない

Cloudflare D1 では、外部キーで参照されているテーブルの CHECK 制約や列定義を変更できない。 enum に値を 1 つ足す場合もそうなる。理由と、それでも変更するときの手順を書く。

何ができないのか

次のスキーマで customers.tier の CHECK に 'premium' を足す場合を考える。

CREATE TABLE customers (
  id   TEXT PRIMARY KEY,
  tier TEXT NOT NULL DEFAULT 'free'
       CONSTRAINT customers_tier_enum CHECK (tier IN ('free', 'standard'))
);

CREATE TABLE orders (
  id          TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers(id)
);

CREATE TABLE order_items (
  id       INTEGER PRIMARY KEY,
  order_id TEXT NOT NULL REFERENCES orders(id),
  sku      TEXT NOT NULL,
  quantity INTEGER NOT NULL
);

CHECK は `ALTER TABLE` では変えられない

SQLite はテーブルの定義を CREATE TABLE 文のテキストとして sqlite_schema に保存しており、 CHECK 制約はそのテキストの一部である。ALTER TABLE にできるのは ADD COLUMN、 RENAME COLUMN、RENAME TO、制限付きの DROP COLUMN の 4 つだけで、保存された定義の中の CHECK を書き換える構文は無い。定義ごと置き換えることになり、SQLite 公式の手順は次の 4 文である。

CREATE TABLE customers_new (...新しい CHECK を含む定義...);
INSERT INTO customers_new SELECT * FROM customers;
DROP TABLE customers;                      -- ここが問題になる
ALTER TABLE customers_new RENAME TO customers;

3 文目の DROP TABLE は、customers を参照している orders から見れば親テーブルの消滅である。 4 文目で同名のテーブルが戻るとしても、3 文目の時点で外部キーの指す先が無くなる。この一瞬の扱いが D1 と他の SQLite 環境で異なる。

外部キーを止める方法が D1 には無い

SQLite 公式の手順は、この 4 文を PRAGMA foreign_keys = OFF で囲む。外部キーの動作を止めておけば、3 文目は orders に何も起こさない。

D1 にこの文を送っても、外部キーはOFFにならない。SQLite は PRAGMA foreign_keys をトランザクションの内側では無視する仕様で、D1 は全ステートメントを暗黙のトランザクションで実行するため、その外側で実行する方法がない。結果、3 文目の DROP TABLE はordersの設定によって次のどちらかになる。

ON DELETE CASCADE であった場合、orders の行が削除される。
NO ACTION であった場合、コミットが失敗する

つまり D1 では、参照されているテーブルを安全に置き換える方法が無い。

変更するときの手順

参照している側の外部キー制約を削除し、親のテーブルを作り直し、外部キー制約を再作成する。3 テーブル（order_items → orders → customers）ならマイグレーションは 3 本になる。

#	内容
0001	`order_items` → `orders` → `customers` の順に作り直し、その過程で外部キー制約を削除する
0002	`orders → customers` の外部キー制約を再作成する
0003	`order_items → orders` の外部キー制約を再作成する

外部キー制約を削除する順序は子テーブルからになる。order_items が orders を外部キーで参照している間は orders を DROP できず、orders が customers を参照している間は customers を DROP できないためである。再作成は 1 マイグレーションに 1 段ずつになる。外部キー制約を 1 本再作成した時点で、その親は再び「参照されているテーブル」になるからである。

0001 から 0003 の間、外部キー制約が無い状態になる。これは D1 の制約ではなく、外部キー制約を削除してから再作成するこの手順の性質である。なお DROP TABLE を含むマイグレーションの生成には、ツール側の確認を外す必要がある（orm-d1 と drizzle-kit では --accept-data-loss）。

orm-d1 での扱い

D1 専用の ORM orm-d1 （開発の経緯）のマイグレーションキットは、この状況を generate の時点で検知して停止する。

Cannot generate a safe migration:
  - "customers" has to be recreated because a check constraint changes,
    but "orders"."customer_id" (on delete no action) references it.

原因のテーブルと、参照している側の列・削除時の動作を出す。上の 3 本に分ける順序も、スキーマの依存グラフから決めている。

まとめ

CHECK の変更はテーブルの作り直しになり、その途中の DROP TABLE が問題になる
D1 は PRAGMA foreign_keys=OFF を実行できないので、参照されているテーブルは作り直せない
外部キー制約を削除して再作成すれば変更できる。削除は子から、再作成は 1 段ずつ
巻き込まれるテーブル数は子孫の数で決まる