実行できるSQLiteドキュメント: 主キー

Q: SQLiteで主キーはどう定義する？

CREATE TABLEの中でカラムにPRIMARY KEYを付けるだけです。例えば id INTEGER PRIMARY KEY のように書きます。複数カラムをまとめて主キーにしたい場合は、テーブルレベルで PRIMARY KEY (cola, colb) と指定します。いずれの場合も、その値（または組み合わせ）が行ごとにユニークである必要があります。

Q: `INTEGER PRIMARY KEY` と他の主キーは何が違う？

INTEGER PRIMARY KEY は特別な存在で、テーブル内部のrowidのエイリアスになります。つまりB-tree本体に直接格納されるため、追加のインデックスは作られません。一方、それ以外の型や複合主キーは、別途ユニークインデックスが作成されます。単一カラムの数値IDなら、INTEGER PRIMARY KEY の方が高速かつコンパクトです。

Q: SQLiteの主キーに `AUTOINCREMENT` は必要？

基本的に不要です。INTEGER PRIMARY KEY は NULL を渡してINSERTするだけで、自動でユニークなrowidを割り当ててくれます。AUTOINCREMENT を付けると「削除された行のIDを再利用しない」という保証が追加されますが、引き換えにsqlite_sequenceテーブルが裏で動くオーバーヘッドが生じます。IDを単調増加させたい明確な理由がない限り、付けないのが無難です。

Q: 主キーなのに `NULL` が入ってしまうのはなぜ？

これは互換性のために残されている古いバグの仕様です。通常のテーブルでは、INTEGER 以外の型の主キーカラムは NOT NULL を明示しないと NULL を許容してしまいます。例外は INTEGER PRIMARY KEY で、これだけは絶対にNULLを受け付けません。安全のため、主キーには必ず NOT NULL を付けるか、ルールが正しく強制される STRICT テーブルを使うのがおすすめです。

SQLite の主キーとは何か

主キー（primary key）は、テーブル内の各行を一意に識別するための列、または列の組み合わせのことです。同じ主キーの値を持つ行が2つ存在することは許されません。SQLite はこの制約を自動的に強制してくれますし、主キーを使って行を高速に検索します。

最もシンプルな書き方は、列定義にインラインで指定する方法です。

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

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

SELECT * FROM users;

id を指定していないのに、SQLite が勝手に値を埋めてくれましたね。これは別に魔法でもなんでもなく、INTEGER PRIMARY KEY という特別な仕組みのおかげです。次に進む前に、この挙動を押さえておきましょう。

INTEGER PRIMARY KEY が特別扱いされる理由

一般的なデータベースでは、主キーは単なる一意インデックスにすぎません。ところが SQLite の通常のテーブルには、内部的に各行を識別するための 64 ビット整数 rowid が最初から隠れて存在しています。そして、ちょうど INTEGER PRIMARY KEY という形でカラムを宣言すると、そのカラムが rowid そのものになります。つまり、追加のインデックスもストレージも不要で、あなたが付けた id と行の物理的な位置がイコールになる、というわけです。

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

INSERT INTO posts (title) VALUES ('1番目'), ('2番目'), ('3番目');

SELECT id, rowid, title FROM posts;

id と rowid は同じ列を別名で呼んでいるだけです。id での検索は行へ直接アクセスでき、追加でたどるべきインデックスツリーは存在しません。だからこそ、SQLite の主キーに数値を使いたいなら INTEGER PRIMARY KEY と正確に書くのが定石とされています。INT でも BIGINT でもなく、INTEGER NOT NULL PRIMARY KEY でも構いませんが（これは動きます）、型は必ず INTEGER でなければいけません。

他の型でも一応動きますが、その場合は別途ユニークインデックスが作られるだけで、動作上は問題ないものの、コンパクトさでは劣ります。

AUTOINCREMENT はたいてい不要

他のデータベースに慣れていると、つい id INTEGER PRIMARY KEY AUTOINCREMENT と書きたくなります。ただ、SQLite の AUTOINCREMENT キーワードは名前から想像するより限定的な働きしかせず、実際にはほとんどの場面で必要ありません。

AUTOINCREMENT を付けなくても、INTEGER PRIMARY KEY の列には現在の最大 rowid に 1 を足した値が自動で入ります。最後の行を削除した場合、その id が次の挿入で再利用される 可能性 があります。

AUTOINCREMENT を付けると、SQLite は sqlite_sequence という内部テーブルでそれまでに使われた最大の id を記録し、削除後でもその値を二度と再利用しません。

CREATE TABLE plain (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE strict_id (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

INSERT INTO plain (label) VALUES ('a'), ('b'), ('c');
INSERT INTO strict_id (label) VALUES ('a'), ('b'), ('c');

DELETE FROM plain WHERE id = 3;
DELETE FROM strict_id WHERE id = 3;

INSERT INTO plain (label) VALUES ('new');
INSERT INTO strict_id (label) VALUES ('new');

SELECT 'plain' AS table_name, id, label FROM plain
UNION ALL
SELECT 'strict_id', id, label FROM strict_id;

plain テーブルでは id 3 が再利用されました。一方 AUTOINCREMENT のテーブルは 4 まで飛んでいます。監査用途や、削除後も残る外部参照があるなど、id の再利用を本当に禁止したい理由がない限り、AUTOINCREMENT は使わないのが無難です。INSERT のたびに余分な書き込みが発生しますし、管理用の別テーブルも必要になります。

sqlite の複合主キー

1 つのカラムだけでは足りないケースもあります。たとえばユーザーとロールを紐づける中間テーブルでは、(user_id, role_id) という 組み合わせ で初めて一意になります。こうした場合は、テーブルレベルで主キーを宣言します:

CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    granted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10), (1, 20), (2, 10);
-- これは複合キーに違反します:
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);

ペアはテーブル全体で一意になります。つまり (1, 10) は一度しか登場できません。とはいえ、それぞれのカラム単体は自由に重複してOKです。これがまさに狙いで、1人のユーザーが複数のロールを持てるし、1つのロールに複数のユーザーが紐づいてもいい。ただし「同じユーザーと同じロールの組み合わせ」は1つだけ、というわけです。

SQLite の複合主キーを定義すると、指定したカラムをカバーする独立したインデックスが作られます。これは rowid に_なりません_。rowid として扱われるのは、あくまで単一の INTEGER PRIMARY KEY だけです。

主キーに NULL が入るという罠

PostgreSQL や MySQL から来た人がびっくりする SQLite の挙動があります。通常のテーブルでは、INTEGER PRIMARY KEY 以外の主キーカラムには NULL を入れられてしまうのです。これは長年知られているバグなのですが、後方互換性のためにそのまま残されています。

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'りんご');
INSERT INTO products (sku, name) VALUES (NULL, '謎の商品 1');
INSERT INTO products (sku, name) VALUES (NULL, '謎の商品 2');

SELECT * FROM products;

NULL の行が 2 件、主キーのチェックをすり抜けて入ってしまいました。これを防ぐには、INTEGER 型ではない主キー列すべてに NOT NULL を明示的に付ける必要があります。

CREATE TABLE products (
    sku   TEXT NOT NULL PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'りんご');
INSERT INTO products (sku, name) VALUES (NULL, '謎の商品');  -- 拒否されるようになった

あるいは、PK に NULL が入ってしまうバグが修正されている STRICT テーブルを使う方法もあります。主キーのカラムには毎回 NOT NULL を書く癖をつけておくと、安いコストで安心が買えます。

主キーと UNIQUE の違い

どちらも重複を防ぐ仕組みですが、次のような違いがあります。

1 つのテーブルに主キーは 1 つだけ ですが、UNIQUE 制約はいくつでも付けられます。
主キーはそのテーブルの「メインの識別子」として扱われ、外部キーはデフォルトで主キーを参照します。
INTEGER PRIMARY KEY は rowid そのものになりますが、UNIQUE を付けただけの整数カラムは rowid にはなりません。
UNIQUE カラムは NULL を複数入れても怒られません(NULL 同士は別物として扱われるため)。

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

INSERT INTO accounts (email, username) VALUES ('a@x.com', 'ada');
INSERT INTO accounts (email, username) VALUES ('b@x.com', 'boris');
SELECT * FROM accounts;

id はその行の識別子です。email や username も一意ではありますが、これらはビジネス上の属性であり、変更される可能性があります。一方、id は変わってはいけません。

あとから主キーを追加する場合（基本的には非推奨）

SQLite の ALTER TABLE は機能が限定的です。ALTER TABLE ... ADD PRIMARY KEY のような構文は存在しないため実行できません。主キーを付け忘れたまま既にデータが入っている場合、テーブルを作り直すしかありません。

CREATE TABLE events (
    name TEXT,
    occurred_at TEXT
);
INSERT INTO events VALUES ('login', '2026-01-01'), ('logout', '2026-01-02');

-- 主キーを付けて再構築:
CREATE TABLE events_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);
INSERT INTO events_new (name, occurred_at) SELECT name, occurred_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

SELECT * FROM events;

これがSQLiteで定番のマイグレーション手順です。実際のコードではトランザクションで囲み、他のテーブルからこのテーブルを参照している場合は外部キーを一時的に無効化しておきましょう。教訓としては、CREATE TABLE の時点で主キーをきちんと設計しておく、ということに尽きます。

sqlite 主キー設計のチェックリスト

新しいテーブルを書くときは、次の点を自問してみてください。

その行に自然な一意の ID があるか? 単一の整数で済むなら INTEGER PRIMARY KEY を使う。
実は複数カラムの組み合わせが ID になっていないか(中間テーブルなど)? その場合はテーブルレベルで PRIMARY KEY (col_a, col_b) を指定する(sqlite 複合主キー)。
主キーがテキストなど整数以外の型か? その場合は NOT NULL を明示的に付ける。
本当に AUTOINCREMENT が必要か? たいていは不要です。
テーブルが小さく、読み取り中心で、主キーが整数以外か? なら WITHOUT ROWID を検討する(rowid のドキュメントで扱います)。

次は rowid

INTEGER PRIMARY KEY は「rowid のエイリアス」としてちらっと登場しましたが、この rowid こそが SQLite の通常テーブルすべての土台になっている存在で、しっかり理解しておく価値があります。それが次のページのテーマです。

よくある質問

SQLiteで主キーはどう定義する？

CREATE TABLEの中でカラムにPRIMARY KEYを付けるだけです。例えば id INTEGER PRIMARY KEY のように書きます。複数カラムをまとめて主キーにしたい場合は、テーブルレベルで PRIMARY KEY (col_a, col_b) と指定します。いずれの場合も、その値（または組み合わせ）が行ごとにユニークである必要があります。

INTEGER PRIMARY KEY と他の主キーは何が違う？

INTEGER PRIMARY KEY は特別な存在で、テーブル内部のrowidのエイリアスになります。つまりB-tree本体に直接格納されるため、追加のインデックスは作られません。一方、それ以外の型や複合主キーは、別途ユニークインデックスが作成されます。単一カラムの数値IDなら、INTEGER PRIMARY KEY の方が高速かつコンパクトです。

SQLiteの主キーに AUTOINCREMENT は必要？

基本的に不要です。INTEGER PRIMARY KEY は NULL を渡してINSERTするだけで、自動でユニークなrowidを割り当ててくれます。AUTOINCREMENT を付けると「削除された行のIDを再利用しない」という保証が追加されますが、引き換えにsqlite_sequenceテーブルが裏で動くオーバーヘッドが生じます。IDを単調増加させたい明確な理由がない限り、付けないのが無難です。

主キーなのに NULL が入ってしまうのはなぜ？

これは互換性のために残されている古いバグの仕様です。通常のテーブルでは、INTEGER 以外の型の主キーカラムは NOT NULL を明示しないと NULL を許容してしまいます。例外は INTEGER PRIMARY KEY で、これだけは絶対にNULLを受け付けません。安全のため、主キーには必ず NOT NULL を付けるか、ルールが正しく強制される STRICT テーブルを使うのがおすすめです。

SQLite主キー入門：INTEGER・複合キー・AUTOINCREMENT