すべてのガイドに戻る
SQL··読了時間 5 分

主キーと外部キー:データベースの関係を支える基礎

SQL における主キーと外部キーの違いを理解しましょう。データの完全性を強制し、リレーションシップを構築し、カスケード処理と連動させる方法を学びます。

注文の外部キーによって参照される顧客の主キー 42 と、具体的な SQL 制約
このガイドの内容
  1. 主キーの理解
  2. 外部キーの理解
  3. 主キーと外部キーの連携の仕組み
  4. 複合キー
  5. カスケードアクション
  6. ベストプラクティス
  7. 参考資料と詳細な読み物

SQL の例では、別のダイアレクトが指定されていない限り PostgreSQL を使用します。短いスニペットは、参照されているテーブルがすでに存在することを前提としています。適用する前に、ターゲットデータベースで DDL を検証してください。

主キーの理解

A 主キー データベーステーブル内のすべての行を一意に識別するカラム、またはカラムのセットです。2つの行が同じ主キー値を共有することはできず、その値が〜になることはありません NULL。テーブルは最大で1つの主キーを宣言でき、アプリケーションテーブルには1つ選択することをお勧めします。

自然キー対サロゲートキー

A 自然キー 現実世界のドメインに既に存在するデータを使用します。例えば、国の ISO コード(US, FR) または本の ISBN。ナチュラルキーには意味がありますが、時間の経過とともに変更される可能性があり、プライバシーに関する懸念が生じる可能性があります (社会保障番号など)。

A サロゲートキー ビジネス上の意味を持たない人工的な識別子であり、通常は自動インクリメントされる整数またはUUIDです。サロゲートキーは安定しており、コンパクトで、インデックス作成が容易であるため、ほとんどの最新のスキーマで好まれています。

-- Natural key example
CREATE TABLE countries (
    iso_code  CHAR(2)      PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

-- Surrogate key example
CREATE TABLE users (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  DEFAULT now()
);

外部キーの理解

A 外部キー 1つのテーブル内のカラム(またはカラムのグループ)であり、同じテーブル内のキーを含む、主キーまたは他の適切な一意キーを参照します。これは〜を強制します 参照整合性:非 NULL の外部キー値には、一致する参照先の行が存在しなければなりません。NULL 許容および複合外部キーには、明示的な NULL 許容性と一致規則が必要です。

CREATE TABLE posts (
    id        SERIAL       PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INT          NOT NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES users (id)
);

この制約が適用されている場合、データベースは次のすべてを拒否します: INSERT 〜へ posts 場所 author_id 既存の〜と一致しません users.id.

ERD内のリレーションシップ線として示される外部キー制約
主キーは行を特定し、外部キーはそれらの識別子をダイアグラム内の目に見えるリレーションシップに変えます。

主キーと外部キーの連携の仕組み

主キーと外部キーはリレーショナルデータベースのバックボーンです。あるテーブルの主キーがアンカーになり、関連するテーブルの外部キーがそのアンカーを指し示します。このリンクによって、宣言された関係が強制されます。次のこともできます JOIN テーブル、および「ユーザー」などの現実世界のエンティティ間の関係をモデル化する has many 投稿」または「注文 に属する 顧客。"

-- Retrieve every post alongside the author's email
SELECT p.title, u.email
FROM   posts p
JOIN   users u ON u.id = p.author_id;

SQL を記述する前にエンティティのスケッチを作成するには、次を開きます: ERDビルダー。上記のテーブルと外部キーの定義を検査するには、それらを次の場所に貼り付けます: SQL から ERD エディター.

複合キー

単一の列だけでは一意性を保証するのに十分でない場合があります。 複合キー (別名 複合キー) は、2つ以上のカラムを単一の主キーに結合します。このパターンは以下で一般的です 中間テーブル (結合テーブルまたは連想テーブルとも呼ばれる) は多対多のリレーションシップに使用されます。

-- Junction table for a many-to-many relationship
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id)  REFERENCES courses (id)
);

複合主キー (student_id, course_id) 各学生が多くのコースを受講でき、各コースに多くの学生が受講できるようにしながら、同じ学生が同じコースに 2 回登録できないようにします。

カスケードアクション

外部キーを定義する際、参照される行が更新または削除されたときに何が起きるかを指定することもできます。これらは 参照アクション 手動のクリーンアップを必要とせずに、データの整合性を維持します。

  • CASCADE — 親行が変更されたときに、子行を自動的に削除または更新します。
  • SET NULL — 外部キー列を以下に設定します NULL (カラムは NULL 許容である必要があります)。
  • SET DEFAULT — 列をデフォルト値にリセットします。この値は引き続き外部キーを満たす必要があります。サポート状況はエンジンによって異なります。MySQL InnoDB はこのアクションを拒否します。
  • RESTRICT / NO ACTION — 無効な参照を残す変更を拒否します。PostgreSQL では、制約が遅延可能な場合、NO ACTION は遅延される可能性があります。RESTRICT は即時です。
CREATE TABLE comments (
    id        SERIAL       PRIMARY KEY,
    body      TEXT         NOT NULL,
    post_id   INT          NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Deleting a post now automatically removes all its comments.

使用 CASCADE 慎重に — 親行を削除すると、関連するすべての子行が警告なしに消去されます。監査が重視されるシステムでは、以下を検討してください。 RESTRICT 代わりに論理削除フラグと組み合わせます。

ベストプラクティス

  • 推奨 サロゲートキー (SERIAL, BIGSERIAL、または UUID) をアプリケーションテーブルの主キーとして使用します。ドメイン識別子が要件に対して十分に安定している場合はナチュラルキーを使用し、変更が参照にどのように影響するかを計画してください。
  • 常時 制約に名前を付ける (例: fk_posts_author) エラーメッセージの意味が通じるようにします。
  • 外部キー列のインデックス作成。多くのデータベースでは外部キーにインデックスが自動的に作成されないため、インデックスが欠落していると結合やカスケード削除の速度が大幅に低下する可能性があります。
  • 以下について考えます: カスケードアクション すべての外部キーに対して。デフォルトは NO ACTION 最も安全です。〜に切り替えてください CASCADE 子行が親なしでは真に意味を失う場合にのみ。
  • モデルの識別ルールから複合キーを選択します。サロゲートキーは参照を単純化できますが、重複を防ぐ必要がある場合は、元のビジネスキーに対して UNIQUE 制約を維持してください。
  • まずスキーマを視覚的にモデル化します。 ERDビルダー 概念モデルをスケッチしてその SQL 変換を確認できるため、本番環境に到達する前に設計ミスを特定できます。

参考資料と詳細な読み物

もっと見る