すべての記事

2026-08-30

· Dylan Yu
SQLiteforeign keysdatabase designschema

SQLiteの外部キーとリレーションシップ：実践ガイド（落とし穴つき）

SQLiteはデフォルトで外部キーを強制しません。そして、多くのチュートリアルは実際にハマりやすい部分を省略しています。SQLiteで外部キーがどう動くのか、PRAGMAの役割、ALTER TABLEなしでリレーションシップをモデリングする方法、そして管理パネルでそれを可視化する方法を解説します。

SQLiteは世界で最も広くデプロイされているデータベースエンジンです。すべてのスマートフォン、すべてのブラウザ、すべてのmacOSに搭載され、今あなたのデスクトップ上のアプリの半数近くでも動いています。それにもかかわらず、外部キーの仕様は本当に奇妙です。経験豊富なエンジニアが半日を費やすほど奇妙なのです。

短く言えばこうです。SQLiteに外部キーは存在しますが、デフォルトではオフになっています。毎接続ごとに有効にしないと、何もしません。しかも有効にしてみると、ALTER TABLEが制限されすぎていて、リレーションシップの間違いを修正するのは、ほとんどのチュートリアルが触れないほど面倒な複数ステップの作業になります。

ネット上の「SQLiteの外部キー」記事の多くは、ハッピーパスだけを扱います。REFERENCES句で2つのテーブルを作り、行を挿入して終わり。これは実際に知っておくべきことの10%程度です。本記事は残りの90%——PRAGMAの挙動、ORMの設定、ALTER TABLEの問題、そしてスキーマを変更できない状況でリレーションシップをモデリングする方法——を扱います。

さっそく始めましょう。

SQLiteの外部キーの仕組み（驚かれるデフォルト設定）

SQLiteは2009年にリリースされたバージョン3.6.19から外部キーをサポートしています。これは誤字ではありません。外部キーは15年以上利用可能です。ただし、後方互換性のためデフォルトでは無効になっており、有効にする方法はスキーマのプロパティでもデータベースの設定でもありません。接続のランタイムフラグなのです。

ここで多くの人がつまずきます。正しいREFERENCES句を書き、テーブルを作り、制約に違反する行を挿入しても、SQLiteは何事もなく受け入れます。エラーも警告も出ません。制約がただの飾りになってしまうのです。

具体的に見てみましょう。まずスキーマです。

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

このFOREIGN KEY (user_id) REFERENCES users(id)は正しいSQLです。ordersのすべてのuser_idがusersの実在するidを指さなければならない、という意味です。では、これに違反する行を挿入するとどうなるか見てください。

INSERT INTO users (id, email) VALUES (1, 'alice@example.com');

-- This references user_id 999, which does not exist.
INSERT INTO orders (id, user_id, total) VALUES (1, 999, 49.99);

-- Result: the insert succeeds. No error.

PostgreSQLやMySQLから移ってきた人なら、ここで画面を凝視することになります。制約はスキーマにちゃんと書いてあるのに、なぜ発動しないのでしょうか？

外部キーの強制がオフになっているからです。PRAGMAで有効にします。

PRAGMA foreign_keys = ON;

-- Now try the same insert:
INSERT INTO orders (id, user_id, total) VALUES (2, 999, 49.99);

-- Result: Error: FOREIGN KEY constraint failed

これが仕組みのすべてです。1行書くだけで制約が機能し始めます。問題は、この1行をどこに書き、常に確実に実行させるにはどうするかです。ここが面倒なところになります。

今すぐFKがオンか確認する方法

何をする前に、この1つのクエリを覚えておいてください。

PRAGMA foreign_keys;
-- Returns 0 (off) or 1 (on)

デバッグ中の接続ならどこでも実行してください。孤立行があって制約がなぜ捕捉しなかったのか不思議に思っているなら、ほぼ常にこれが答えです——挿入を行った接続でFKがオフだったのです。

PRAGMAの落とし穴

PRAGMAの方式が本当に危険な理由は、データベース単位ではなく接続単位だという点です。ある接続でPRAGMA foreign_keys = ONを設定しても、他の接続には影響せず、永続もしません。新しい接続を開くと——同じファイルに対しても——FKは再びオフになります。

つまり、この設定は接続を開くたびに、接続を開くコードが適用しなければなりません。忘れると、サイレントに制約違反が起きます。エラーログも警告も何も出ません。データがそのまま入ってしまいます。

実際には、この設定はスキーマではなくアプリケーションコードやORMのセットアップに置くことになります。そして主要なORMはそれぞれ対応が異なり、それ自体がバグの温床になります。

Prisma

PrismaはSQLiteに接続する際、デフォルトで外部キーを有効にします。何もする必要はありません。Prismaエンジンが接続セットアップの一環としてPRAGMA foreign_keys = ON;を実行します。これは正しいデフォルトであり、PrismaのSQLiteサポートの中で、そのまま動く数少ない部分の一つです。

$executeRaw / $queryRawで生クエリを使う場合も、同じ接続プールが使われるためFKは有効なままです。素晴らしい。

SQLAlchemy

SQLAlchemyはSQLiteに対してデフォルトで外部キーを有効にしません。これに多くの人が引っかかります。SQLAlchemyはモデルにForeignKeyカラムを定義させておきながら、それを黙って強制しないからです。

イベントリスナーで明示的に有効にする必要があります。

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def _enable_sqlite_fk(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

このリスナーは新しい接続ごとに発火します。まさに望む挙動です。これを忘れると、ForeignKey宣言は制約ではなくドキュメントになってしまいます。

Drizzle

DrizzleのNode SQLiteドライバ（内部ではbetter-sqlite3）も、デフォルトではFKをオフのままにします。クライアント構築時に有効にします。

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('app.db');
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');  // <-- this line

export const db = drizzle(sqlite);

better-sqlite3の.pragma()呼び出しはその接続に適用されます。プールを使っている場合（better-sqlite3は同期型で単一接続なので稀ですが）、接続ごとに適用する必要があります。

一般的なルール

どのスタックを使っていてもルールは同じです。接続が作成される場所を見つけ、そこでPRAGMAを設定してください。 ORMのドキュメントにこれが明記されていないなら、issuesタブを検索してください。リリース後にFKがオフだったと気づいた混乱したユーザーのスレッドがほぼ確実にあります。

もう一つの注意点。PRAGMAはトランザクション内では変更できません。すでにトランザクションに入っている場合、PRAGMA foreign_keys = ONはno-opになります。BEGINの前に設定する必要があります。ほとんどのORMは接続オープン時、ユーザーコードが実行される前に設定するため正しく扱いますが、接続を手動で管理している場合は留意してください。

SQLiteの外部キーがサポートするもの（としないもの）

FKが実際にオンになると、SQLiteのサポートは人々が思うより完全です。一般的な参照アクションはすべて動きます。例とともに見ていきましょう。

ON DELETE CASCADE

親行が削除されると、すべての子行が自動的に削除されます。

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO users (id, email) VALUES (1, 'alice@example.com');
INSERT INTO orders (id, user_id, total) VALUES (1, 1, 49.99);
INSERT INTO orders (id, user_id, total) VALUES (2, 1, 12.50);

DELETE FROM users WHERE id = 1;

-- Both orders rows are now gone. No manual cleanup needed.

これが最もよく使うものです。「親がなければ子も意味をなさない」場合の正しいデフォルトです。

ON UPDATE CASCADE

親の主キーが変更されると、子の外部キーカラムがそれに合わせて更新されます。

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON UPDATE CASCADE
);

INSERT INTO accounts (id, name) VALUES (1, 'Checking');
INSERT INTO transactions (id, account_id, amount) VALUES (1, 1, 100.00);

UPDATE accounts SET id = 100 WHERE id = 1;

-- transactions.account_id is now 100, automatically.

IDを振り直す場合に有用です。ほとんどのスキーマは不変の代理キーを使うため、必要になることは少ないですが、用意されています。

SET NULL

親が削除されると、子のFKカラムがNULLに設定されます（カラムはnullableである必要があります）。

CREATE TABLE projects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    project_id INTEGER,  -- nullable
    title TEXT NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

INSERT INTO projects (id, name) VALUES (1, 'Migration');
INSERT INTO tasks (id, project_id, title) VALUES (1, 1, 'Write schema');

DELETE FROM projects WHERE id = 1;

-- tasks row still exists, project_id is now NULL.

子レコードが親なしでも意味を持つ場合——プロジェクトのないタスク、親投稿のないコメント——に適切な選択です。

RESTRICTとNO ACTION

RESTRICTは子が存在する場合、親の削除を防ぎます。しかも即座に実行します。トランザクション内であっても遅延しません。

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL
);

CREATE TABLE invoice_lines (
    id INTEGER PRIMARY KEY,
    invoice_id INTEGER NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
);

NO ACTIONがデフォルトです。SQLiteでは、NO ACTIONとRESTRICTは1点だけ微妙に異なります。NO ACTIONはステートメントの終わりに制約をチェックする（複雑なステートメント内で削除の順序を変えられる）のに対し、RESTRICTは即座にチェックします。実用上ほとんど違いはありませんが、「即座に失敗、例外なし」が欲しいならRESTRICTを使いましょう。

サポートされていないもの

知っておくべき点がいくつかあります。

  • 部分外部キーはありません。 何らかの条件が真のときだけ適用されるFK（例：「status = 'active'のときだけ強制」）は作れません。制約はカラムに対してオールオアナッシングです。
  • 式ベースのFKはありません。 FKは実際のカラムを参照する必要があり、式は参照できません。
  • 遅延制約はサポートされていますが、ほとんど使われません。 FKをDEFERRABLE INITIALLY DEFERREDとして宣言でき、COMMITまでチェックが延期されます。これは循環参照（AがBを参照し、BがAを参照する）で1つのトランザクションで両方の行を挿入する必要がある場合に有用です。動きますが、実際のスキーマで必要になったことはほぼありません——通常、nullableカラムで循環を断てます。
  • 親カラムは主キーかUNIQUEインデックスを持つ必要があります。 SQLiteは一部のデータベースより厳格です。任意のカラムは参照できません。ユニークでなければなりません。

大多数のスキーマでは、サポートされているサブセットで十分です。人々が困るギャップは機能リストではなく、次に説明するALTER TABLEの問題です。

ALTER TABLEの問題

ここがSQLiteの外部キーの話が本当に苦しくなるところです。

SQLiteのALTER TABLEは制限が多いことで有名です。できることの完全なリストは以下のとおりです。

  • ALTER TABLE ... RENAME TO ... — テーブルのリネーム
  • ALTER TABLE ... RENAME COLUMN ... TO ... — カラムのリネーム
  • ALTER TABLE ... ADD COLUMN ... — カラムの追加
  • ALTER TABLE ... DROP COLUMN ... — カラムの削除（3.35.0で追加）

これだけです。このリストで顕著に欠けているものは以下のとおりです。

  • 既存のテーブルに外部キー制約を追加できない。
  • 既存のカラムの型や制約を変更できない。
  • 既存のカラムにNOT NULL制約を追加できない。
  • カラムのデフォルト値を変更できない。

つまり、FKなしでテーブルを作った後でFKが必要だと気づいても、ALTER TABLE orders ADD FOREIGN KEY ...というステートメントは存在しません。公式のSQLiteドキュメントに回避策が記載されていますが、12ステップのプロセスです。なぜ人々が避けるのか理解してもらうため、全体をお見せします。

12ステップのリビルド

パターンはこうです。欲しいスキーマで新しいテーブルを作り、データをコピーし、古いテーブルを削除し、新しいテーブルをリネームし、古いテーブルに依存していたインデックス、トリガー、ビューを再作成します。SQLで示します。

-- 1. Turn FK enforcement off during the migration (required, because
--    SQLite won't let you alter a table referenced by FKs while FKs are on).
PRAGMA foreign_keys = OFF;

-- 2. Start a transaction.
BEGIN TRANSACTION;

-- 3. Create the new table with the FK constraint you wanted.
CREATE TABLE orders_new (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. Copy the data over, filtering out any orphaned rows first.
INSERT INTO orders_new (id, user_id, total)
SELECT id, user_id, total FROM orders
WHERE user_id IN (SELECT id FROM users);

-- 5. Drop the old table.
DROP TABLE orders;

-- 6. Rename the new table to the original name.
ALTER TABLE orders_new RENAME TO orders;

-- 7. Recreate any indexes that were on the old table.
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 8. Recreate any triggers (if you had them).
-- CREATE TRIGGER ... (omitted for brevity)

-- 9. Recreate any views that referenced the table (if you had them).
-- CREATE VIEW ... (omitted)

-- 10. Run the foreign key check to confirm everything is consistent.
PRAGMA foreign_key_check;

-- 11. Commit.
COMMIT;

-- 12. Turn FK enforcement back on.
PRAGMA foreign_keys = ON;

これが公式の手順です。動きます。しかし客観的に見てかなり多く、ステップを見落としやすいです。インデックスの再作成を忘れればクエリが遅くなります。foreign_key_checkを忘れれば孤立行を出荷します。FKの再有効化を忘れればサイレント違反の問題に逆戻りです。

人がSQLiteのリレーションシップに不満を持つ本当の理由は、機能がないことではなく、後からリレーションシップを修正するのが手動でエラーの起きやすいプロセスだからです。PostgresならALTER TABLE orders ADD CONSTRAINT ... FOREIGN KEY ...を1行実行して終わりです。SQLiteではテーブルを再構築することになります。

では実際にどうするのか？ 現実的な選択肢が3つあり、それぞれトレードオフが異なります。

ALTER TABLEなしでリレーションシップをモデリングする

選択肢1：12ステップのリビルド

上で示した方法です。本物の強制されるFK制約を持つスキーマを生成するという意味で「正しい」答えです。参照整合性をデータベースレベルで強制したい場合——通常はそうすべきです——はこれが道です。

いつ使うか：

  • スキーマを所有しており、短時間の書き込みロックを取れる。
  • テーブルが巨大でない（数百万行のコピーには時間がかかりますが、SQLiteは高速です）。
  • 生SQLやデータベースに触れる他のツールからも制約を強制したい。

いつ避けるか：

  • 数秒のダウンタイムも許容できない。
  • テーブルが巨大でコピーに時間がかかりすぎる。
  • スキーマを所有していない（サードパーティアプリのデータベースである）。

実践的なティップス：全体をスクリプト化し、データベースのコピーで先にテストし、コミット前にPRAGMA foreign_key_checkを実行してください。行が返ってきたら孤立行があるので、マイグレーションを安全に完了する前にどう処理するか決める必要があります。

選択肢2：アプリケーション層でORMに任せる

ほとんどのORMは、データベースにFK制約がなくてもモデル定義でリレーションシップを宣言できます。ORMがアプリケーションコードでリレーションシップを強制します。user.ordersを実行するとSELECT * FROM orders WHERE user_id = ?が走り、orderを作成するときはuser_idが設定されていることを確認します。

Prismaの場合：

model User {
  id     Int     @id @default(autoincrement())
  email  String  @unique
  orders Order[]
}

model Order {
  id      Int  @id @default(autoincrement())
  user_id Int
  user    User @relation(fields: [user_id], references: [id])
  total   Float
}

SQLAlchemyの場合：

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)  # no ForeignKey() needed for ORM-level relations
    user = relationship("User", back_populates="orders")

トレードオフは、リレーションシップがORMを通したときにのみ存在することです。誰かが生SQLを実行したり、別のツールがデータベースに接続したり、バックグラウンドジョブが直接行を挿入したりしても、孤立したuser_id値を止めるものはありません。すべての書き込みがアプリケーションを通すことを信頼することになります。小さなプロジェクトの多くではそれで十分です。複数のライターや外部ツールがデータベースに触れる環境では、穴のある保証になります。

選択肢3：管理ツールのUI層でリレーションシップを定義する

これが私が最終的に最もよく使うようになった方法です。特定の問題を解決するため、説明する価値があります。その問題とは、基になるスキーマを変更せずにリレーションシップを扱いたい（結合データのブラウズ、親から子へのナビゲーション、使いやすい管理インターフェースの構築）ということです。

アイデアは、リレーションシップをスキーマではなく、データを見るために使うツール——データベースの上のレイヤー——で定義するというものです。アプリケーションコードは完全に影響を受けません。スキーマはそのままです。しかしデータを管理するとき、「本物の」リレーションシップから期待する結合ビューやナビゲーションが得られます。

これはまさにBaseVoltがやることです。SQLite、PostgreSQL、MySQL、Cloudflare D1の管理パネルとして機能するローカルファーストのデスクトップアプリ（macOSとWindows）です。データベースを指定すると、任意の2つのテーブルに対してUIでリレーションシップを定義できます。親テーブル、子テーブル、つなぐカラムを選ぶと、BaseVoltはブラウズ、結合、ナビゲーションの目的でそれらをリンクとして扱います。ALTER TABLEなし。スキーママイグレーションなし。アプリケーションが見るものへの変更もなし。

これはSQLiteで特に有用です。なぜなら代替が上記の12ステップリビルドだからです。スキーマを書き換えずにリレーションシップを使いたいだけなら、UI層で定義する方が圧倒的に作業が少なく、リスクもゼロです。

フリーティア（最大2データソース）があり、Proは$99/年です。インストール前にリレーションシップUIを見たいなら、demo.basevolt.app にライブデモがあります。またBaseVoltは組み込みのMCPサーバーを持っているため、ClaudeやCursorをデータベースに向けて、スキーマ作業の支援——リレーションシップがどこにあるべきか提案させるなど——を依頼できます。

管理パネルでリレーションシップを可視化する

BaseVoltでリレーションシップを定義すると実際どうなるか、ワークフローを中心に説明しましょう。これこそが便利さの源泉です。

ステップ1：データベースに接続する

BaseVoltを開き、「Add Data Source」をクリックしてSQLiteの.dbファイルを指定します。スキーマを直接読み取ります。接続文字列もマイグレーションも設定も不要です。十数個のテーブルがあるデータベースなら、数秒で終わります。

ステップ2：リンクしたい2つのテーブルを選ぶ

リレーションシップビューで親テーブル（例：users）と子テーブル（例：orders）を選びます。BaseVoltはそれぞれのカラムを表示します。つなぐフィールド——users.idとorders.user_id——を選んでリレーションシップを定義します。これだけです。これを作るためにSQLはデータベースに対して実行されません。定義はスキーマではなくBaseVoltの設定に存在します。

ステップ3：結合ビューをブラウズする

リレーションシップが定義されると、いくつかのことが自動的に起こります。

  • usersの行を見ているとき、関連するすべてのorders行を表示するリンクセクションが現れます。どれでもクリックして編集できます。
  • ordersの行を見ているとき、親userへのリンクが表示されます。
  • JOINを書かずに両テーブルにまたがるフィルタ済みビューを構築できます。

これは文章では伝えにくいですが、使った瞬間に明らかになります。「関連データを見るためにクエリを実行する」という思考から「ものをクリックする」という思考に切り替わるのです。sqlite3 CLIで何度もSELECT * FROM orders WHERE user_id = 5を打ってきた人にとって、これは生活の質を大きく向上させます。

ステップ4：AIに手伝わせる

BaseVoltはMCPサーバーを公開しているため、ClaudeやCursorを接続して自然言語で質問できます。「マッチするユーザーのない注文はどれ？」「これらのテーブル間のリレーションシップを提案して」など。AIはMCPサーバーを通じてスキーマとデータを調べ、見落としていたリレーションシップを提案できます。魔法ではなく、スキーマのイントロスペクションがすでにあり、MCPサーバーがそれをモデルに利用可能にしているだけです。

重要なのは、これらがいずれもスキーマに触れないことです。後でリレーションシップが間違っていたと分かったら、UIで削除して再定義するだけです。マイグレーションもリビルドも、本番データへのリスクもありません。

一般的なパターン

何度もモデリングすることになる3つのリレーションシップパターンを、それぞれのSQLと管理ツールでの見え方とともに具体的に見ていきましょう。

1対多

最も一般的なパターンです。1人のユーザーが複数の注文を持つ。1つのプロジェクトが複数のタスクを持つ。1人の著者が複数の投稿を持つ。

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

FKは「多」の側にあります。BaseVoltではこう表示されます。ユーザーを見ると下にその注文が表示され、注文を見るとそのユーザーへのリンクが表示されます。どちら方向も1クリックです。

多対多（ジャンクションテーブル）

複数の学生が複数のコースに登録する。3つ目のテーブル——ジャンクションテーブル——が必要で、ペアリングごとに1行を保持します。

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

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

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

いくつか気をつける点があります。

  • ジャンクションテーブルの主キーは両方のFKカラムの複合キーです。これにより重複登録を防ぎます。
  • 両方のFKがON DELETE CASCADEを使うため、学生またはコースを削除すると登録が自動的にクリーンアップされます。
  • ジャンクションテーブルは追加データ（ここではenrolled_at）を保持できます。

管理ツールでは、これは2つのリレーションシップです。students ↔ enrollmentsとcourses ↔ enrollments。学生のコースを見るには、学生 → 登録 → コースとナビゲートします。BaseVoltはこれを2ホップとして扱います。これが正直なモデリング方法です。リレーショナルデータベースに「直接的な」多対多は存在せず、ジャンクションテーブルしかありません。

自己参照

カテゴリーが親カテゴリーを持つ。従業員のマネージャーもまた従業員である。コメントが親コメントを持つ。

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

FKがテーブルから自分自身を指します。parent_idはnullableなので、ルートカテゴリーは親を持たなくて済みます。ON DELETE SET NULLは、カテゴリーを削除しても子を削除せず、トップレベルに孤立させることを意味します。削除をツリー全体にカスケードしたい場合はON DELETE CASCADEを使いますが、注意してください。1つのステートメントでサブツリー全体が削除されます。

自己参照リレーションシップは、UI層で定義するのが特に便利なケースです。「親」と「子」が同じテーブルだからです。BaseVoltではcategoriesからcategoriesへのid ↔ parent_idのリレーションシップを定義すると、カテゴリーを見たときに親も子もインラインで表示されます。

FK問題のデバッグ

FKがオンでも問題は起きます。孤立行のあるデータベースを引き継ぐ。マイグレーションがうまくいかなかった。FKがオフの間にバックグラウンドジョブが不正データを挿入した。SQLiteには何が起きたかを調べるためのPRAGMAが2つあります。

PRAGMA foreign_key_check;

データベース全体をスキャンし、外部キー制約に違反する行を返します。疑わしいときはいつでも実行してください。

PRAGMA foreign_key_check;
-- Returns rows like:
-- orders|42|users|1
-- meaning: table 'orders', rowid 42, violates FK into 'users', constraint #1

各行は子テーブル、違反行のrowid、親テーブル、どのFK制約（インデックス順）に違反したかを示します。そこからSELECT * FROM orders WHERE rowid = 42で実際の行を見て、どう処理するか決められます。user_idを修正する、行を削除する、欠けている親を挿入する、などです。

これを12ステップリビルドの終わり、コミット前に実行すべきでもあります。何も返さなければ、マイグレーションは整合しています。

PRAGMA foreign_key_list(table);

特定のテーブルに定義された外部キー制約を表示します。

PRAGMA foreign_key_list(orders);
-- Returns one row per FK, showing:
-- id | seq | table  | from     | to | on_update | on_delete | match
-- 0  | 0   | users  | user_id  | id | NO ACTION | CASCADE   | NONE

テーブルがどんな制約を持つか忘れたとき——特に自分が設計していないデータベース——に有用です。fromとtoのカラムはどのローカルカラムがどの親カラムを指すかを示し、on_delete / on_updateはアクションを示します。

孤立行を手動で見つける

データベース全体をスキャンせずに、特定のリレーションシップの孤立行を見つけたい場合：

SELECT o.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

これは古典的な「親のない子を見つける」クエリです。1つのリレーションシップだけ気になる場合はforeign_key_checkより速く、rowidだけでなく完全な子行を返します。

デバッグのワークフロー

リレーションシップに何かおかしいと感じたら、以下の順序で進めるのが良いでしょう。

  1. 書き込みを行った接続でPRAGMA foreign_keys;を実行する。0なら、それが答えです。
  2. PRAGMA foreign_key_check;を実行して被害を確認する。
  3. 各違反について、子を修正する、子を削除する、欠けている親を挿入する、のいずれかを決める。
  4. PRAGMA foreign_key_list(child_table);を実行し、制約が想定通りか確認する。
  5. 今後FKがどこでもオンになるよう、接続セットアップを修正する。

最も一般的な根本原因は圧倒的にステップ1です。「SQLiteの外部キーが動かない」問題の80%は、単にPRAGMAがオフの接続だと推定されます。

まとめ

SQLiteの外部キーサポートは問題ありません。問題を引き起こすのは機能不足ではなく、デフォルトと使い勝手です。具体的には：

  • FKはデフォルトでオフです。 接続を作成するコードで、毎接続ごとにPRAGMA foreign_keys = ONを設定してください。デバッグ時はPRAGMA foreign_keys;で確認します。
  • PRAGMAは接続単位です。 ORMのドキュメントにどう扱うか書いてあります。書いてなければ、扱っていないと仮定してイベントフックを追加してください。
  • ALTER TABLEでFKは追加できません。 リレーションシップの修正は12ステップのテーブルリビルドになるか、アプリケーション/UI層でリレーションシップを定義する代わりになります。
  • サポートされる参照アクションは実用に十分です。 CASCADE、SET NULL、RESTRICT、NO ACTIONはすべて動きます。遅延制約も必要なら使えます。
  • デバッグはPRAGMA2つです。 foreign_key_checkとforeign_key_listで必要な情報はすべて得られます。

そして、スキーマを書き換えずにSQLiteのリレーションシップを扱いたいだけなら——結合データのブラウズ、親と子の行のナビゲーション、管理インターフェースの構築——ALTER TABLEと戦う代わりにUI層で定義してください。それこそがBaseVoltの目的です。データベースを指定し、リレーションシップを視覚的に定義し、スキーマもアプリコードも触れずに使える管理パネルを得られます。

basevolt.app で試してみてください — サインアップもクレジットカードも不要です。まずは demo.basevolt.app のライブデモで触ってみるのも良いでしょう。

この記事が役に立ったら、データベース、ローカルファーストソフトウェア、開発ツール構築の地味な部分について書いています。X でフォローしてください。

BasevoltBasevolt

Basevoltをお試しください — PostgreSQL、MySQL、SQLite、Cloudflare D1用の無料ローカルファーストデータベース管理パネル。

Basevoltを無料でダウンロード