DB区分値カラムの制約パターン

はじめに

DBで区分値を扱いたい場合、カラムの制約としていくつかのパターンが存在します。
この記事では4つのパターンのメリット・デメリットについて取り上げます。
なお、DBはPostgreSQLを前提に記載します。

制約なし

区分値は文字列として保持し、値を保証する制約を付けません。
区分値自体はソースコード内に定義したり、汎用区分値テーブルにデータとして持ちます。
区分値として正しい値が設定されているか否かは、APIパラメータのバリデーションで判定します。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    status VARCHAR(1) NOT NULL -- VARCHAR 1桁として指定
);

メリット

制約を付けていないので、その分実装コストは低くなります。
また、文字列であればカラムに追加できるため、区分値の追加・変更・削除に柔軟に対応できます。

デメリット

カラムに有効な区分値が入っているか保証ができません。
代わりに実施するAPIのバリデーションに依存することになり、DBレイヤとして見たときの整合性は低くなります。

CHECK

カラムごとに文字列を指定して、特定の値のみ指定できるように設定します。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    status VARCHAR(1) NOT NULL,

    -- CHECKを指定
    CONSTRAINT check_user_status
        CHECK (status IN ('1', '2', '3'))
);

メリット

特定の文字列のみカラムに追加可能となります。制約を付けるうえでは最も手軽な方法です。

デメリット

区分値の追加・変更・削除時は、一度CHECK制約を削除し、新しい設定を付け直す必要があります。

ALTER TABLE users
DROP CONSTRAINT check_user_status;

ALTER TABLE users
ADD CONSTRAINT check_user_status
CHECK (status IN ('1', '2'));

また、同一区分を複数カラムで扱う場合、同じ指定が重複することになります。
区分の追加があった場合、全てのカラムに対する制約を変更する必要があります。

ENUM

カラムにENUM型を指定し、あらかじめ指定した文字列のみに制限します。

CREATE TYPE user_status AS ENUM ('1', '2', '3');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    status user_status NOT NULL -- ENUMを型指定
);

メリット

特定の文字列のみカラムに追加可能となります。
ENUMに追加した値は、関連するカラムの制約としてまとめて更新されます。

デメリット

ENUM内の値を削除するコマンドがないため、削除したい場合は型を作り直して紐付ける必要があります。
利用しなくなった値はそのまま残しておくか、新しいENUMを作って指定し直す必要があります。
値を残しておく場合、利用しなくなった値を設定できる状態となってしまうため、データとして不整合が起こる可能性があります。

区分値テーブル

区分値を保持するテーブルを作り、カラムのFK（外部キー）とします。
区分値テーブルに存在する値のみをカラムに指定できるようになります。

CREATE TABLE statuses (
    status_code CHAR(1) PRIMARY KEY,
    status_name VARCHAR(50) NOT NULL
);

INSERT INTO statuses (status_code, status_name) VALUES
('1', 'Active'),
('2', 'Inactive'),
('3', 'Pending');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    status CHAR(1) NOT NULL REFERENCES statuses (status_code) -- FKを指定
);

メリット

区分をデータ管理しているため、区分値の追加・変更・削除に対してメンテナンス性・参照整合性が高い状態となります。区分値を削除する場合も、事前に使われていないことを確認したうえで、区分値テーブルのデータを削除するだけで対応可能です。
また、区分値に紐づく情報として、ラベルやソート順などを紐付けてデータ管理できます。

デメリット

区分ごとにテーブルを追加するため、他の方法と比較して実装コストが高めです。
FKを用いるため、他の方法よりパフォーマンスが少し低くなります。

また、区分の種類が増えるたびに、区分値テーブル数は増えていきます。
区分値テーブルはFKとして参照整合性を担保する都合上、複数の区分で1テーブルを共用することができません。
区分の種類が数十となる場合、区分テーブルのみで数十個が存在することになります。