実行できるSQLiteドキュメント: UNIQUE制約

Q: SQLiteでUNIQUE制約はどう書けばいい？

単一カラムなら email TEXT UNIQUE のようにカラム定義に UNIQUE を付けるだけ。複数カラムをまとめてユニークにしたい場合は、テーブル定義の最後に UNIQUE(col1, col2) を書きます。SQLiteは内部でユニークインデックスを作って制約を管理しており、重複が発生する INSERT や UPDATE はそのまま弾かれます。

Q: UNIQUEとPRIMARY KEYは何が違うの？

PRIMARY KEY はテーブルに1つしか持てませんが、UNIQUE はいくつでも付けられます。また PRIMARY KEY は NOT NULL を兼ねる扱い（STRICTテーブルや INTEGER PRIMARY KEY の場合）ですが、UNIQUE カラムには NULL を複数入れることができます。行を識別するキーには PRIMARY KEY、それ以外で重複を許したくないカラムには UNIQUE、と使い分けるのが基本です。

Q: UNIQUE列なのにNULLが何個も入るのはなぜ？

SQLでは NULL は「値が不明」を表すため、NULL 同士を比べても等しいとは判定されません。だから UNIQUE カラムでも NULL の行はいくつでも入り、重複チェックの対象になるのは非NULLの値だけです。NULLも1件までに絞りたい場合は、NOT NULL を付けるか部分ユニークインデックスを使いましょう。

Q: 「UNIQUE constraint failed」エラーが出たときはどうする？

これは INSERT や UPDATE の結果、UNIQUE（または PRIMARY KEY）カラムで値が重複してしまうときに出るエラーです。挿入する値を変える、既存の行をあらかじめ削除する、あるいは INSERT ... ON CONFLICT（いわゆるupsert）で衝突時の挙動を明示する、のいずれかで対応します。

UNIQUE制約は「重複NG」のサイン

UNIQUE制約は、特定のカラム(あるいは複数カラムの組み合わせ)で同じ値が重複しないようにSQLiteへ伝えるための仕組みです。「同じメールアドレスのユーザーを2人作らせない」「商品コードは1回しか登場しない」といったルールを表現するときに使います。

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

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

SELECT * FROM users;

3 つ目の INSERT は UNIQUE constraint failed: users.email で失敗します。SQLite は書き込みのたびに UNIQUE 制約をチェックし、重複が発生する操作を拒否します。最初の 2 行は保存されますが、3 行目は登録されません。

内部的には、UNIQUE 制約はユニークインデックス(sqlite unique index)として実装されています。これは SQLite が高速検索に使うのと同じデータ構造なので、チェックのコストは低く、対象カラムには自動でインデックスが張られます。

カラム単位 vs テーブル単位の書き方

UNIQUE の書き方は 2 通りあります。カラム定義の横にインラインで書く方法と、テーブル定義の末尾に独立した句として書く方法です。

-- カラムレベル: シンプル、1つのカラム。
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- テーブルレベル: 同じ効果だが、配置が異なる。
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

単一カラムであれば、両者は事実上同じなので読みやすい方を選べばOKです。ただし複数カラムにまたがる一意性を表現したい場合は、テーブルレベルの書き方が必須になります。

sqlite 複合ユニークキー：複数カラムを組み合わせる

カラム単体では重複してもよいけれど、組み合わせ としては一意であってほしいケースがあります。たとえば、1人のユーザーは複数のコースを受講でき、1つのコースにも複数のユーザーが登録できますが、同じ (user_id, course_id) のペアが2回現れてはいけません。

CREATE TABLE enrollments (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE(user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);
INSERT INTO enrollments (user_id, course_id) VALUES (1, 101);  -- OK、コースが異なる
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- OK、ユーザーが異なる
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- 失敗:ペアが既に存在する

SELECT * FROM enrollments;

制約はペアに対してかかっていて、片方のカラム単体にかかっているわけではありません。ユーザー1は複数のコースを受講できるし、コース100にも複数のユーザーが登録できます。ただし、同じ組み合わせは1回だけ、というわけです。

これは多対多のリレーションで使う中間テーブル(join table)の定番パターンですね。

sqlite uniqueとprimary keyの違い

名前も似ているし関係も深いですが、UNIQUEとPRIMARY KEYはイコールではありません。

1つのテーブルに PRIMARY KEY は1つだけ。一方で UNIQUE 制約はいくつでも付けられます。
PRIMARY KEY は行のアイデンティティそのもの。外部キーの参照先になり、rowid のエイリアスとしても機能します。
UNIQUE は単に「この値(または組み合わせ)が重複しない」という意味だけです。
通常のテーブルでは、UNIQUE カラムには NULL を入れられますが、PRIMARY KEY には入れられません(歴史的な例外が1つありますが、ここでは触れません)。

よく見かける形はこんな感じです。

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- 内部的な識別子、変更されない
    email    TEXT NOT NULL UNIQUE,  -- ユーザー向けの識別子、一意である必要がある
    username TEXT NOT NULL UNIQUE   -- もう一つの一意なハンドル
);

id はデータベースの他のテーブルから参照されるカラムです。一方で email や username がユニークなのは、アプリケーションの要件としてそうしているだけで、identity（同一性）を表すものではありません。ユーザーがメールアドレスを変更しても id は変わらない —— カラムを分けているのは、まさにこのためです。

sqlite unique と NULL の落とし穴

これは、ほぼ全員が最初にハマるポイントです。SQLite では、UNIQUE 制約のついたカラムでも NULL はいくつでも入れられます。

CREATE TABLE invites (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES ('ada@example.com');
INSERT INTO invites (email) VALUES ('ada@example.com');  -- これは失敗する

SELECT * FROM invites;

NULLが3つあっても問題なし。ところが'ada@example.com'が2つあると、これは衝突です。

理由はこうです。SQLではNULLは「未知の値」として扱われるため、未知同士を比較しても等しいとは判定されません。だからユニーク性チェックでも重複とは見なされないわけです。NULLを1つだけに制限したい場合は、NOT NULL UNIQUEを付けるのが一番すっきりします。NULLは許可しつつ、他のカラムとの組み合わせで1つだけにしたいなら、部分インデックス(partial index)の出番です(これは後のインデックスの章で扱います)。

衝突の処理:ON CONFLICTの使い方

デフォルトでは、UNIQUE制約に違反するとそのステートメントは中断されます。でも実際には、既存の行を置き換えたい、新しい行を無視したい、特定のカラムだけ更新したい、といったケースもありますよね。SQLiteではこういった挙動を2通りの方法で指定できます。

1つ目は、制約自体にON CONFLICTを組み込む書き方です:

CREATE TABLE settings (
    key   TEXT UNIQUE ON CONFLICT REPLACE,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');
INSERT INTO settings (key, value) VALUES ('theme', 'dark');   -- 最初の行を置き換える
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

themeを2回目に挿入したタイミングで、既存の行が削除され、新しい行に置き換わります。他に指定できるオプションはIGNORE（黙って無視）、ABORT（デフォルト）、FAIL、ROLLBACKです。

2つ目のやり方は、ステートメント単位で指定するsqlite upsert構文です。特定のカラムだけを更新できるので、こちらのほうが柔軟に使えます。

CREATE TABLE counters (
    name  TEXT UNIQUE,
    count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, count) VALUES ('hits', 1);
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;

SELECT * FROM counters;

最初の INSERT で行が作成され、続く2回は UNIQUE 制約に引っかかって DO UPDATE 側に流れ、count がインクリメントされます。これがいわゆる INSERT ... ON CONFLICT を使った sqlite upsert 構文で、専用のページで後ほど詳しく取り上げます。

sqlite unique制約とunique indexの違い

CREATE UNIQUE INDEX は UNIQUE 制約と実質的に同じ役割を果たします。というより、UNIQUE 制約を定義すると裏側では sqlite unique index が自動的に作成されるので、見た目が違うだけで中身はほぼ同じ仕組みなんです。

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

CREATE UNIQUE INDEX idx_users_email ON users(email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');  -- 失敗する

どちらを選ぶべきか:

制約（CONSTRAINT） はユニーク性がテーブル定義そのものの一部である場合に使います。カラムの定義のすぐ隣に書かれるので、スキーマを読めば一目瞭然です。
ユニークインデックス は、部分インデックス（WHERE 句付き）にしたい、特定の名前を付けたい、既存のテーブルを作り直さずに追加したい、といった場合に向いています。SQLite の ALTER TABLE では制約を追加できませんが、インデックスはいつでも後から追加できます。

書き込み時の挙動はまったく同じです。違いは、このルールをスキーマのどこに置きたいか、という点に尽きます。

既存テーブルに UNIQUE 制約を追加する

SQLite の ALTER TABLE は意図的に機能が絞られていて、ALTER TABLE ... ADD CONSTRAINT は用意されていません。実際に取れる方法は次の 2 つです:

-- 方法1: ユニークインデックスを追加する。既存のテーブルでも動作し、同じ制約が適用される。
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email TEXT
);

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

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (email) VALUES ('ada@example.com');  -- ここで失敗する

方法その2 ―― どうしてもテーブル定義そのものに UNIQUE 制約を組み込みたい場合は、テーブルを作り直す手順を踏むことになります。制約付きの新しいテーブルを作って、データをコピーして、古いテーブルを削除し、リネームする、という流れです。詳しくは次のページで解説します。

ひとつ注意点があります。すでに重複データが入っているカラムにユニーク制約を後付けしようとすると、CREATE UNIQUE INDEX は失敗します。先に重複行を整理してから、インデックスを張りましょう。

UNIQUE制約に失敗したときのエラーの読み方

エラーメッセージを見れば、どの制約に引っかかったのかが一目でわかります。

Error: UNIQUE constraint failed: users.email
Error: UNIQUE constraint failed: enrollments.user_id, enrollments.course_id

1つ目は users.email という単一カラムに対する UNIQUE 制約、2つ目は複合ユニークキーです。複数カラムが並んでいるのは、組み合わせ として既に存在しているからですね。UNIQUE constraint failed に出くわしたら、こんな手順で対処します。

どの行が衝突しているのかを特定する(SELECT ... WHERE email = '...')。
その行を更新するのか、INSERT をスキップするのか、別の値を使うのかを決める。
重複が 想定内 でマージしたいなら、INSERT ... ON CONFLICT DO UPDATE を使った upsert 構文に切り替える。

このエラーがはっきり出てくれるのはありがたい話です。たいていの場合は本当に気づきたいエラーですし、書き込みが黙って失敗するより、重複が黙って通ってしまう方がずっと厄介ですから。

次回:テーブルの削除と変更

UNIQUE 制約は、既存テーブルに対して単純な ALTER TABLE で後付けすることができません。この制約があるため、SQLite ではスキーマ変更のときに独特の手順 ―― いわゆるテーブル再構築 ―― を踏むことになります。次のページでは、テーブルをきれいに削除する基本とあわせて、この再構築の流れを取り上げます。

よくある質問

SQLiteでUNIQUE制約はどう書けばいい？

単一カラムなら email TEXT UNIQUE のようにカラム定義に UNIQUE を付けるだけ。複数カラムをまとめてユニークにしたい場合は、テーブル定義の最後に UNIQUE(col1, col2) を書きます。SQLiteは内部でユニークインデックスを作って制約を管理しており、重複が発生する INSERT や UPDATE はそのまま弾かれます。

UNIQUEとPRIMARY KEYは何が違うの？

PRIMARY KEY はテーブルに1つしか持てませんが、UNIQUE はいくつでも付けられます。また PRIMARY KEY は NOT NULL を兼ねる扱い（STRICTテーブルや INTEGER PRIMARY KEY の場合）ですが、UNIQUE カラムには NULL を複数入れることができます。行を識別するキーには PRIMARY KEY、それ以外で重複を許したくないカラムには UNIQUE、と使い分けるのが基本です。

UNIQUE列なのにNULLが何個も入るのはなぜ？

SQLでは NULL は「値が不明」を表すため、NULL 同士を比べても等しいとは判定されません。だから UNIQUE カラムでも NULL の行はいくつでも入り、重複チェックの対象になるのは非NULLの値だけです。NULLも1件までに絞りたい場合は、NOT NULL を付けるか部分ユニークインデックスを使いましょう。

「UNIQUE constraint failed」エラーが出たときはどうする？

これは INSERT や UPDATE の結果、UNIQUE（または PRIMARY KEY）カラムで値が重複してしまうときに出るエラーです。挿入する値を変える、既存の行をあらかじめ削除する、あるいは INSERT ... ON CONFLICT（いわゆるupsert）で衝突時の挙動を明示する、のいずれかで対応します。

SQLite UNIQUE制約の使い方｜複合キーとNULLの挙動