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

PostgreSQL 外部キーで存在しない親IDを防ぐ

注文の明細が、存在しない注文IDを参照するとデータの意味が分からなくなります。アプリケーションで事前確認しても、確認後に別の処理が親を削除する可能性があります。外部キーを設定し、データベースでも関係を保証します。

親と子のテーブルを作る

CREATE TABLE orders (
    order_id bigint PRIMARY KEY,
    ordered_at timestamptz NOT NULL
);

CREATE TABLE order_items (
    order_item_id bigint PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(order_id),
    product_name text NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0)
);

先に親テーブルを作り、子のorder_idから参照します。NOT NULLを付けないと、子が親を持たないNULLの行も作れるため、必須の関係なら両方指定します。

存在しない親を試す

INSERT INTO order_items (order_item_id, order_id, product_name, quantity)
VALUES (1, 999, 'ノート', 2);

ID 999の注文がなければ外部キー違反になります。アプリケーションでは、DBエラーをそのまま利用者に見せず、状況に応じたメッセージへ変換します。

親を削除するときの方針

この定義では、明細が残る注文を削除しようとすると拒否されます。明細も一緒に削除したい設計なら、外部キーへON DELETE CASCADEを指定できます。

order_id bigint NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE

ただし、注文履歴のように削除してはいけないデータへ安易にCASCADEを指定しないでください。非表示や取消状態を使う方が適する場合もあります。

PostgreSQLでは参照元のorder_items.order_idにインデックスが自動作成されるとは限りません。注文から明細を頻繁に探すなら、CREATE INDEX ON order_items (order_id);も検討します。外部キーとインデックスは役割が違います。

PostgreSQL

関連記事