Partial unique index を使って enum のある値を持つ行を unique にする

やりたいこと

User がいる

User はセッションをN個持つ

code:sql

CREATE DATABASE mopp;

\connect mopp

-- 初期化

DROP TYPE IF EXISTS session_status;

DROP TABLE IF EXISTS sessions;

DROP TABLE IF EXISTS users;

-- 作成

CREATE TABLE users (

id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

name VARCHAR(32) NOT NULL

);

CREATE UNIQUE INDEX users_uniq ON users (name);

CREATE TYPE session_status AS ENUM ('in_progress', 'done', 'expired');

CREATE TABLE sessions (

id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

user_id uuid NOT NULL,

status session_status DEFAULT 'in_progress',

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

);

-- ここがポイント

CREATE UNIQUE INDEX sessions_uniq ON sessions (user_id, status) WHERE status = 'in_progress';

-- 動作確認

INSERT INTO users (name) VALUES ('mopp');

SELECT id as mopp_id FROM users WHERE name = 'mopp';

\gset

INSERT INTO sessions (user_id, status) VALUES (:'mopp_id', 'in_progress');

-- INSERT INTO sessions (user_id, status) VALUES (:'mopp_id', 'in_progress');

-- 2回目は UNIQUE INDEX があるのでエラーになる

アンチパターン？

他のテーブルと JOIN するときに常に条件をしていすることになって面倒

ので、テーブルを分離したほうがいいのかもしれない

参考文献