zukucode
主にWEB関連の情報を技術メモとして発信しています。
PostgreSQL

PostgreSQL UNIQUE制約で値の重複を防ぐ

同じメールアドレスを複数の利用者へ登録させたくない場合は、UNIQUE制約を使用します。

登録前に同じ値を検索すると、利用者へ早く分かりやすいメッセージを返せます。しかし、検索と登録の間に別の処理が登録する可能性は残ります。そのため、事前確認は入力支援、UNIQUE制約はデータ整合性の保証として役割を分けます。

テーブル作成時に指定する

CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE
);

同じemailを2回登録するとエラーになります。

制約へ名前を付ける

CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    name varchar(100) NOT NULL,
    email varchar(255) NOT NULL,
    CONSTRAINT uq_users_email UNIQUE (email)
);

制約名を付けると、エラーの原因や後から削除する対象を確認しやすくなります。

既存のテーブルへ追加する

ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

すでに重複データがある場合は追加できません。先に以下のSQLなどで重複を確認します。

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

複数列の組み合わせを一意にする

CREATE TABLE organization_members (
    organization_id uuid NOT NULL,
    user_id uuid NOT NULL,
    role varchar(30) NOT NULL,
    CONSTRAINT uq_organization_members
        UNIQUE (organization_id, user_id)
);

同じ組織と利用者の組み合わせは1件だけ登録できます。別の組織であれば同じ利用者を登録できます。

NULLの扱いに注意する

PostgreSQLの通常のUNIQUE制約では、NULLは複数行に登録できます。

値を必須にして1件だけにしたい場合は、NOT NULLも指定します。

SELECTで存在を確認してからINSERTしても、同時に別の処理が同じ値を登録する可能性があります。

最終的な重複防止はデータベースのUNIQUE制約で行い、アプリケーションでは制約違反を適切なエラーへ変換します。

UNIQUE制約を使用すると、複数の処理が同時に実行された場合もデータベースで重複を防げます。

PostgreSQL

関連記事