顧客 ID が消えた注文シート (参照整合性)

事故 — 顧客を消したら注文の宛先が不明になった

顧客シートで、退会した顧客 C-999 の行を削除した。 翌日、注文シートを見ると C-999 の注文が 3 件残っている。 宛先の顧客情報が引けなくなり、発送業務が止まった。

原因 — Excel はシート間の参照を強制しない

Excel の VLOOKUP や参照式は「参照先が消えたら #N/A になる」だけ。「参照している行がいるから消せない」と教えてくれる仕組みがない。 削除時に「参照している注文はどうする？」と警告するか、削除を禁止するか、あるいは注文も一緒に消すか、 の判断を DBMS に任せる仕組みが必要になる。

解決策 — 外部キー制約 (FOREIGN KEY) と ON DELETE 挙動

RDB では、テーブル定義時に 「この列の値は別テーブルの主キーとして必ず存在すること」を宣言できる。存在しない値を挿入したり、参照されている行を削除しようとすると、DBMS が拒否する。

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id CHAR(6) NOT NULL,
  product_id CHAR(6) NOT NULL,
  qty INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT,
  FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE RESTRICT
);

-- 存在しない顧客への注文は挿入拒否
INSERT INTO orders (id, customer_id, product_id, qty)
  VALUES (1001, 'C-999', 'P-042', 1);
-- ERROR: insert or update on table "orders" violates foreign key constraint

-- 注文が残ったまま顧客を削除しようとしても拒否
DELETE FROM customers WHERE id = 'C-999';
-- ERROR: update or delete on table "customers" violates foreign key constraint
--        DETAIL: Key (id)=(C-999) is still referenced from table "orders".

ON DELETE の 4 種類の挙動

• RESTRICT / NO ACTION (既定): 子から参照が残っていたら親の削除を拒否
• CASCADE: 親を消すと子も自動的に消える。例: カート削除 → カート明細行も削除
• SET NULL: 子の FK 列を NULL にする (FK 列が NULL 可の場合のみ)
• SET DEFAULT: 子の FK 列を定義された既定値に戻す

境界事例と実務判断

• CASCADE は便利だが危険: 「意図しない大量削除」を招きやすい。 純粋に親に従属するデータ (明細行、タグ紐付け) のみに限定するのが安全
• 論理削除 (soft delete) との相性: deleted_at 列を持たせて論理削除する場合、 FK は物理行の存在を見るので「論理削除された親を子から参照できる」問題が残る。 ビジネスロジックで補完するか、履歴テーブルを分けるかの判断が必要
• パフォーマンスへの影響: FK チェックのコストは通常無視できる。 性能問題が実測で確認された時だけ、局所的に外すかインデックス設計を見直す

関連トピック

キー設計そのものは キーの階層 、参照関係を図で表現する話は 関連 (リレーションシップ) を参照。