Cloudflare D1を使ってて、DrizzleORMでマイグレーションを作成してたときのこと。
ふと作成されたマイグレーションのファイルを見ると、空っぽな上にこう書かれてた。
/*
SQLite does not support "Creating foreign key on existing column" out of the box, we do not generate automatic migration for that, so it has to be done manually
Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
https://www.sqlite.org/lang_altertable.html
Due to that we don't generate migration automatically and it has to be done manually
*/なんてこった。
再現するには
そのまんま。
まずこういうテーブルを作るマイグレーションを流したとする。
モンスターテーブルがあり、トレーナーテーブルがあり、トレーナーは推しを1体連れて歩ける的な設定。
CREATE TABLE `monsters` (
`id` integer PRIMARY KEY NOT NULL,
`name` text NOT NULL,
`power` integer NOT NULL
);
CREATE TABLE `trainers` (
`id` integer PRIMARY KEY NOT NULL,
`name` text NOT NULL,
`best_monster_id` integer
);そして後になって、trainers.best_monster_idって、monsters.idに制限されてほしいな？となり、外部キー制約をつけようとする。
すると、今回のような自体に陥る。生SQLを流してた場合は、単にエラーになる。
仕様らしい
あまたのSQLはALTER TABLEであれこれできるけど、SQLiteの場合は4つのことしかできない。
RENAME TABLERENAME COLUMNADD COLUMNDROP COLUMN
SQLiteはその特性上、実装が独特なので、簡単にはできないのだと。
どうするか
どうしようもないわけではなく、やり方としては2パターンあるらしい。
sql - How do I add a foreign key to an existing SQLite table? - Stack Overflow
まずは愚直なやり方。 テーブルAと同等のテーブルBをいったん作り（その時に外部キー制約を足し）、データをまるごとコピーして、最後にテーブルAを消し、テーブルBをAにリネームする、と。
なるほど・・・力技や・・・。
次に、SQLiteの深部をいじくるトリッキーなやり方。
SQLiteはスキーマ定義とデータ本体の2つが分離された実装になってるらしく、外部キー制約を足すだけなど、データは変更しない場合にのみ選べる選択肢らしい。
sqlite_schemaというテーブルに格納されてるスキーマ定義を、編集可能にして直接いじる、と。
なるほど・・・。
まとめ
ちなみに、
- どっちの方法も面倒だなって感じてしまった
- 作りたてのプロジェクトだった
- 他に誰も触ってないしデータもたかが知れてる
という恵まれた環境だったので、マイグレーションファイルを全消しして0からやり直すという3つ目のパターンで事なきを得たのであった。
データベース設計はむずかしい。
これからも、D1を触れば触るほど、こういうのにハマっていくんであろうな・・・。