SQL Academy · レッスン

FOREIGN KEYと参照アクション

FOREIGN KEYでテーブル同士を関連付け、適切な動作となるようON DELETE CASCADE / SET NULL / RESTRICTを選びます。

「FOREIGN KEYと参照アクション」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

外部キーの役割

FOREIGN KEYによって、存在しない親を参照する行をデータベースが拒否するようになります。

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total NUMERIC(10,2) NOT NULL
);

-- Inserting an order with user_id=999 fails if no such user exists.

外部キーを使う理由

FKは参照整合性を保証します。

孤児行を防ぎます
INSERT時にアプリケーションのバグを検出します
ER図を正確に保ちます
自動的なカスケード動作を可能にします

参照アクション

親の行が削除または更新されたときに、何が起きるべきでしょうか。5つの選択肢があります。

NO ACTION — エラー（デフォルト、遅延可能）
RESTRICT — エラー（遅延不可）
CASCADE — 子も削除または更新します
SET NULL — 子のFKをNULLに設定します
SET DEFAULT — 子のFKをデフォルト値に設定します

ON DELETE CASCADE

「ユーザーが削除されたら、そのユーザーの注文も削除する」という動作です。

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL
    REFERENCES users(id) ON DELETE CASCADE,
  total NUMERIC(10,2) NOT NULL
);

ON DELETE SET NULL

「マネージャーが削除されても、従業員は残し、マネージャーだけを解除する」という動作です。

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id) ON DELETE SET NULL
);

デフォルト：RESTRICTに近い動作

参照アクションを指定しない場合、子を持つ親を削除しようとすると失敗します。

ERROR:  update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

FK列には必ずインデックスを作成する

PostgreSQLは子側にインデックスを自動作成しません。必ず追加してください。インデックスがないと、親を削除する際にすべての子テーブルがフルスキャンされます。

CREATE INDEX orders_user_id_idx ON orders(user_id);

複合外部キー

複合主キーを参照します。

CREATE TABLE order_items (
  order_id BIGINT,
  line_no INT,
  PRIMARY KEY (order_id, line_no)
);
CREATE TABLE shipments (
  order_id BIGINT,
  line_no INT,
  FOREIGN KEY (order_id, line_no)
    REFERENCES order_items(order_id, line_no)
);

外部キーのチェックを遅延する

鶏と卵のような関係にある2つの行をINSERTする必要がある場合があります。FKをDEFERRABLE INITIALLY DEFERREDとして設定します。

ALTER TABLE orders
  ALTER CONSTRAINT orders_user_id_fkey DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  INSERT INTO orders ... ;     -- FK temporarily not checked
  INSERT INTO users ... ;
COMMIT;                         -- checked here

オンラインで外部キーを追加する

大きなテーブルでは、長時間のロックを避けるためにNOT VALID + VALIDATEを使用します。

ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

カスケードは慎重に扱う

CASCADEによる削除は伝播します。ユーザーを削除すると、注文、支払い、監査ログまで消える可能性があります。カスケードがビジネス上の意図と一致していることを確認してください。

まとめ

外部キーはガードレールです。

REFERENCESとアクションによって、関連付けと動作を宣言します
子側のFK列にインデックスを作成します
オンラインで追加する場合はNOT VALID + VALIDATEを使用します

理解度チェック

親であるユーザーが削除されたとき、そのユーザーの注文を自動的に削除する正しい参照アクションは何ですか。

よくある質問

「FOREIGN KEYと参照アクション」レッスンは無料ですか？

はい。「FOREIGN KEYと参照アクション」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「FOREIGN KEYと参照アクション」で何を学びますか？

FOREIGN KEYでテーブル同士を関連付け、適切な動作となるようON DELETE CASCADE / SET NULL / RESTRICTを選びます。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「FOREIGN KEYと参照アクション」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る