SQLite DROP TABLE / ALTER TABLEでスキーマ変更

スキーマは変わるもの。SQLite でも(ほぼ)変えられます

テーブルを作ったら、いずれは名前を変えたい、カラムを追加したい、不要なカラムを消したい、あるいは構造を全部作り直したい――そんな場面が必ず出てきます。SQLite では、よくあるケースは DROP TABLE と ALTER TABLE で直接対応でき、それ以外はドキュメント化された手順で回避する形になっています。

ただし注意点が一つ。SQLite の ALTER TABLE は Postgres や MySQL に比べるとかなり制限が多いです。何ができて何ができないのか、そしてできない場合の「テーブル再構築」パターンを知っておくこと――ここがこの章のキモになります。

DROP TABLE はテーブルごとまるっと削除する

sqlite drop table を実行すると、テーブル本体だけでなく、中のデータ、インデックス、定義されたトリガーまですべて消えます。元には戻せません。

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('一時的'), ('これも一時的');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

テーブルはこれで削除されました。この状態でクエリを投げると no such table: scratch というエラーが出ます。

セットアップスクリプトなどでよくあるのが、「そもそもテーブルがあるかどうか分からない」というケースです。そんなときは IF EXISTS を付けておけば、テーブルが無くてもエラーにならず、何もせずに通り過ぎてくれます:

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT '両方の削除に成功しました' AS result;

IF EXISTS を付けない場合、2回目の DROP はエラーになります。付けておけば、どちらも問題なく実行できます。

外部キー制約で DROP がブロックされるケース

外部キー制約が有効になっていて(PRAGMA foreign_keys = ON;)、削除しようとしているテーブルを別のテーブルが参照している場合、DROP TABLE は失敗します。

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

選択肢はいくつかあります。参照側のテーブルを先に削除する、参照している行を消す、あるいはテーブル作成時に外部キーを ON DELETE CASCADE で定義しておく、のいずれかです。SQLite は参照整合性を黙って壊してくれたりはしません。

ALTER TABLE でできる4つの操作

SQLite の ALTER TABLE でサポートされている操作は、ちょうど4つだけです。

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT
);

-- 1. テーブル名を変更する
ALTER TABLE posts RENAME TO articles;

-- 2. カラム名を変更する
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. カラムを追加する
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. カラムを削除する (SQLite 3.35以降)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

どれも 1 つのステートメントとして実行されます。最初の 2 つはほぼノーコストで、スキーマ情報を書き換えるだけです。ADD COLUMN も高速で、SQLite はテーブル本体を書き直すことなく、新しいカラム定義をスキーマに記録するだけで済みます。一方 DROP COLUMN は重めの処理で、該当カラムのデータを物理的に取り除くために全行を書き換える必要があります。

デフォルト値付きで sqlite カラム追加

既存のテーブルに新しいカラムを追加すると、デフォルト値を指定しない限り、全行のその値は NULL から始まります:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

INSERT INTO users (email) VALUES ('ada@example.com'), ('boris@example.com');

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

既存の行にはどちらも 'active' が入ります。ここでのデフォルト値は定数でなければいけません。CURRENT_TIMESTAMP のような非定数の式を ADD COLUMN のデフォルトに指定することは SQLite では許されていません。これは、行ごとに評価せずに既存のすべての行に一律で適用できる値が必要だからです。

デフォルト値なしで NOT NULL を付けたい場合は、いったん NULL 許可でカラムを追加し、UPDATE で値を埋めてから、テーブルを再構築して制約を付け直すしかありません。というわけで、ここから ALTER TABLE の制限の話に入ります。

SQLite の ALTER TABLE で「できない」こと

PostgreSQL や MySQL では使えるのに、SQLite ではできない操作はこれだけあります。

• カラムの型を変える(ALTER COLUMN ... TYPE ...)。
• カラムのデフォルト値を直接書き換える。
• 既存カラムへの NOT NULL、CHECK、UNIQUE、PRIMARY KEY の追加・削除。
• 既存カラムに外部キー制約を後付けする。
• カラムの並び順を変更する。

これらを実行しようとすると構文エラーになります。そもそも SQLite には ALTER COLUMN 句自体が存在しません。公式の答えはどれも同じで、「テーブルを作り直す」しかないというものです。

sqlite テーブル再構築のパターン

ALTER TABLE で対応できない変更が必要になったら、欲しいスキーマで新しいテーブルを作り、データをコピーし、古いテーブルを削除して、新しいテーブルを元の名前にリネームします。途中で失敗しても中途半端な状態にならないよう、トランザクションで囲んでおきましょう。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    age TEXT
);

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

これで users.age は CHECK 制約付きの整数、email は NOT NULL になりました。データもそのまま引き継がれています。

実際にこのテーブル再構築をやるときに気をつけたいポイントをいくつか挙げておきます。

• 作業中は外部キーをオフにする。 他のテーブルから参照されている場合は、トランザクションの前に PRAGMA foreign_keys = OFF; を実行し、終わったら PRAGMA foreign_keys = ON; で戻します。これをやらないと DROP TABLE が失敗します。なお、このプラグマはトランザクションの中では変更できないので、必ず外側で設定してください。
• インデックスとトリガーは作り直す。 古いテーブルを削除すると、それに紐づくインデックスやトリガーも一緒に消えます。リネーム後に新しいテーブルへ付け直しましょう。
• ビューも要チェック。 テーブルを参照しているビューは、内部に保存された SQL の中で古い名前を指したままになっています。変更したカラムに依存しているビューがあれば作り直してください。

この再構築パターンは手数こそ多いものの、確実に動きます。Alembic や Rails といったマイグレーションツールが SQLite を相手にするときも、裏側では同じことをやっています。

SQLite で複数のテーブルを削除する

複数のテーブルをまとめて削除する構文はありません。DROP TABLE を一つずつ実行していく形になります。まとめて扱いたいなら、トランザクションの中に入れてしまうのが手軽です。

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

トランザクションでまとめておけば、3つのDROPがすべて成功するか、まったく実行されないかのどちらかになります。外部キー制約が絡んでいて途中で失敗しそうな関連テーブルをまとめて削除したいときに便利です。

この章のまとめ

• DROP TABLE はテーブルとそれに紐づくインデックス・トリガーを削除します。冪等なスクリプトにしたいなら IF EXISTS を付けましょう(sqlite drop table if exists)。
• ALTER TABLE でできるのは4つだけ:テーブル名の変更、カラム名の変更、カラム追加、カラム削除です。
• それ以外の操作 ── 型の変更、新しい制約の追加、既存カラムへの外部キー付与など ── はトランザクションの中でテーブルを作り直す(再構築する)必要があります。
• テーブルを再構築するときは、外部キー・インデックス・トリガー・ビューに注意してください。これらはデータと一緒に自動でついてきません。

次回:データを入れていく

ここまでの章では、テーブルとそれを形作る制約について見てきました。次はいよいよ中身を詰めていきます。次の章は INSERT から始まり、複数行をまとめて入れる書き方、デフォルト値の使い方、そして制約に引っかかる INSERT を SQLite がどう扱うかまで取り上げます。