リレーショナルデータベースにおける主キー、ユニークキー、外部キーの役割と違い

リレーショナルデータベース管理システム（RDBMS）において、データの正確性、一貫性、完全性を保証することは、信頼性の高いアプリケーション構築の基盤です。この基盤を支えるのが、主キー（Primary Key）、ユニークキー（Unique Key）、外部キー（Foreign Key）という3つの重要な概念です。これらはデータベースがデータ整合性規則を強制するための核となるメカニズムであり、その適切な理解と適用は、データベース設計の品質、データ操作の効率、そしてシステム全体の安定性に直接影響します。

1. キーの基本概念と制約

リレーショナルモデルでは、データは二次元のテーブルに整理され、これらのテーブル内のデータの品質と関係の正確性を保証するための中心的なツールが「キー」です。キーは単なるフィールドの属性ではなく、データ列に課される一連の規則、すなわち「制約」です。

1.1 主キー（Primary Key）：エンティティの唯一の識別子

主キーは、リレーションデータベースにおいて最も基本的かつ重要な制約です。その主要な目的は、テーブル内の各レコード（行、タプル）に対して、他に重複しない不動の識別子を提供することです。

定義と主要な制約

データベース理論と実践に基づき、主キーは、テーブル内の各行を一意に識別できる1つまたは複数のフィールドの組み合わせとして定義されます。この目的を達成するために、主キー制約は以下の2つの厳格なルールを強制します。

  1. 一意性 (Uniqueness): 主キー列（または列の組み合わせ）の各値は一意でなければならず、重複する値は一切許されません。既存のレコードと主キー値が重複するデータを挿入または更新しようとすると、データベースシステムはその操作を拒否し、エラーを返します。
  2. 非NULL性 (Non-Nullability): 主キーを構成するいかなる部分もNULLであってはなりません。NULLはデータベースでは「不明」または「存在しない」を意味し、不明な識別子ではレコードを一意に識別するという重要な役割を果たすことができません。したがって、主キー制約は本質的に一意性制約 (UNIQUE) と非NULL制約 (NOT NULL) の強力な組み合わせです。

適切に設計されたデータベーステーブルは、1つだけの主キーを持つ必要があります。この主キーは、テーブル内の単一列（例：member_id）であることも、複数の列の組み合わせであることもあり、後者は複合主キー (Composite Primary Key) と呼ばれます。

役割と目的：エンティティ整合性

主キーの第一の役割は、エンティティ整合性 (Entity Integrity) を強制することです。エンティティ整合性は、リレーショナルモデルの3大整合性規則の1つであり（他は参照整合性、ドメイン整合性）、どのテーブルも主キーを持ち、主キー値が非NULLかつ一意であることを要求します。この規則は、テーブル内の各行データが区別可能で識別可能な独立したエンティティであることを保証します。主キーがなければ、特定のレコードを確実に特定、更新、または削除することができず、データは混乱し管理不能な状態に陥ります。

典型的な用途

  • 一意な行識別子： これは主キーの最も基本的な用途で、テーブルの各行に安定した一意な識別子を提供します。
  • テーブル間関係の基盤： 主キーは、外部キーによって参照されるターゲットとして、テーブル間の関連を構築する橋渡し役となります。子テーブルは親テーブルの主キーを参照することで関係を確立します。

SQLテーブル作成例:

-- 単一列主キー
CREATE TABLE MemberTbl (
    member_id INT PRIMARY KEY, -- member_id を主キーとして定義
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100)
);

-- 複合主キー
CREATE TABLE TransactionItems (
    transaction_id INT,
    item_sequence_no INT,
    item_quantity INT,
    PRIMARY KEY (transaction_id, item_sequence_no) -- transaction_id と item_sequence_no の組み合わせが複合主キー
);

1.2 ユニークキー（Unique Key）：ビジネスルールにおける一意性の保証

ユニークキーは、その名の通り、列データの「一意性」を保証することを主な目的とします。しかし、主キーとは制約の厳格さや用途において顕著な違いがあり、より大きな設計の柔軟性を提供します。

定義と主要な制約

ユニークキー（またはユニーク制約）は、それが適用される単一または複数の列の組み合わせにおいて、すべての値が一意であり、重複が許されないことを保証します。主キーとの主な違いはNULL値の扱いにあります。

  1. 一意性 (Uniqueness): 主キーと同様に、ユニークキー列の値は重複できません。
  2. NULL値の許容性 (Nullability): これが主キーとの最も本質的な違いです。ユニークキーはNULL値を含むことを許容します。ただし、許可されるNULL値の数については、データベースシステムによって実装が異なり、この点は後述します。

さらに、1つのテーブルに複数のユニークキーを定義できますが、主キーは1つしか持てません。これにより、1つのテーブル内で複数の異なる次元の一意性ビジネスルールを強制することができます。

役割と目的：ドメイン整合性とビジネスロジック

ユニークキーの主な役割は、特定のビジネスルールを強制し、特定のフィールドがレコードの「識別子」としては適切でなくても、ビジネスロジック上で重複してはならないことを保証することです。これはデータ品質とドメイン整合性に貢献しますが、そのレベルはエンティティ整合性より下位です。

例えば、ユーザーテーブルでuser_idが主キー（システム内部の識別子）である場合でも、ビジネス上はユーザーのemail_addressやlogin_nameも一意であるべきです。2人のユーザーが同じメールアドレスで登録されることを望まない場合、email_address列をユニークキーとして定義することが非常に適切です。これはレコードの「IDカード」ではありませんが、重要で一意であるべきビジネス属性です。

典型的な用途

  • 主キー以外の列の一意性の強制： これは最も一般的な用途で、ユーザーの電子メールアドレス、ログイン名、従業員の社員番号（主キーがオートインクリメントIDの場合）、製品のSKUコードなどが挙げられます。
  • 代替の参照ターゲットの提供： 外キーは主キーだけでなく、ユニークキーも参照できます。これは特定の設計パターンで役立つ場合がありますが、あまり一般的ではありません。

SQLテーブル作成例:

CREATE TABLE Staff (
    staff_id INT PRIMARY KEY,
    employee_code VARCHAR(20) UNIQUE, -- 社員番号は一意であるべきだが、新規採用者で一時的にNULLを許容
    contact_email VARCHAR(100) NOT NULL UNIQUE, -- メールアドレスは一意かつ非NULL
    full_name VARCHAR(100)
);

1.3 外部キー（Foreign Key）：テーブル間の関係維持

主キーがテーブル内のレコードにアイデンティティを確立するものであるならば、外部キーはテーブル間にリンクを確立するためのものであり、リレーションデータベースにおける「リレーション（関係）」という言葉の具現化です。

定義と主要な制約

外部キーは、あるテーブル内の一連の列であり、その値が別のテーブルの主キーまたはユニークキーを参照します。外部キーを含むテーブルは通常「子テーブル」または「参照元テーブル」と呼ばれ、参照されるテーブルは「親テーブル」または「参照先テーブル」と呼ばれます。

外部キー制約は以下のルールを強制します。

  1. 参照の有効性： 子テーブルの外部キー列の値は、親テーブルの参照される主キー/ユニークキー列に実際に存在するか、またはNULL（その外部キー列がNULLを許容する場合）である必要があります。
  2. 「孤立レコード」の発生防止： データベースシステムは、「孤立レコード」（参照先の親レコードがすでに存在しない子テーブルのレコード）が発生する可能性のある操作を阻止します。例えば、注文に参照されている顧客を削除したり、注文の顧客IDを存在しないIDに更新したりすることはできません。

外部キーは、同じテーブルの主キーを参照することもできます。この特殊なケースは自己参照外部キー (Self-referencing Foreign Key) と呼ばれ、組織構造における従業員と上司の関係など、階層構造を表す際によく使用されます。

役割と目的：参照整合性

外部キーの核となる役割は、参照整合性 (Referential Integrity) を強制することです。参照整合性は、テーブル間の関係が有効で、一貫性があり、信頼できるものであることを保証します。これにより、無効なデータの挿入が防止され、関連データの更新および削除操作が制約され、データベース全体の論理的一貫性が維持されます。

外部キーを通じて、データベースは孤立したテーブルの集まりから、相互に関連し、論理的に厳密なデータネットワークへと変化します。例えば、SalesOrdersテーブルのcustomer_account_id外部キーは、すべての注文がCustomerAccountsテーブルに存在する顧客に属していることを保証します。

典型的な用途

  • テーブル間関係の確立と維持： 注文と顧客、記事と著者、学生とコースといった関係を構築します。
  • データの一貫性保証： 存在しないエンティティを指す参照の作成を防ぎ、データ参照の有効性を保証します。
  • カスケード操作： 親テーブルのレコードが削除または更新されたときに、子テーブルの関連レコードがどのように応答するかを定義します（例：カスケード削除 (ON DELETE CASCADE) やカスケードNULL設定 (ON DELETE SET NULL)）。

SQLテーブル作成例:

-- 親テーブル
CREATE TABLE CustomerAccounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(100) NOT NULL
);

-- 子テーブル
CREATE TABLE SalesOrders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_account_id INT,
    -- 外部キー制約を定義
    FOREIGN KEY (customer_account_id) REFERENCES CustomerAccounts(account_id)
);

この例では、SalesOrdersテーブルのcustomer_account_idが外部キーであり、CustomerAccountsテーブルのaccount_id主キーを参照しています。

2. 物理実装とパフォーマンス：キー、制約、そしてインデックスの共生関係

主キー、ユニークキー、外部キーを理解するには、論理的な概念だけでなく、データベースの物理ストレージ層における「インデックス」（Index）との密接な関係も把握する必要があります。制約は論理的な規則ですが、インデックスはデータ検索効率を高める物理的なデータ構造です。ほとんどのデータベースシステムは、インデックスを利用してキーの一意性制約を効率的に強制し、キーに関連するクエリを高速化します。

2.1 主キーとインデックス：必然の組み合わせ

テーブルに主キーを定義すると、データベース管理システムはほとんどの場合、その主キーに対して一意インデックス (Unique Index) を自動的に作成します。このインデックスは、新しく挿入または更新された値がすでに存在するかどうかを高速にチェックするためにデータベースが使用する重要な要素です。インデックスがなければ、新しいレコードを挿入するたびに、データベースはテーブル全体をスキャン（フルテーブルスキャン）して一意性を確認する必要があり、大規模なテーブルでは壊滅的なパフォーマンスになります。

クラスタ化インデックス (Clustered Index)

一部のデータベースシステムとストレージエンジンでは、主キーとインデックスの関係はさらに特殊です。

  • MySQL (InnoDB ストレージエンジン): 広く使われているInnoDBストレージエンジンでは、主キーインデックスはクラスタ化インデックスです。これは、テーブルの物理データ行自体が主キーの順序でディスク上に格納されることを意味します。テーブル全体が大きなB-Treeインデックス構造であり、リーフノードには完全なデータ行が含まれます。1つのテーブルには1つのクラスタ化インデックスしか持てないため、主キーの選択はデータストレージとクエリパフォーマンスに深く影響します。クラスタ化インデックスを使用すると、主キーに基づく範囲クエリや単一ポイント検索が非常に高速になります。
  • SQL Server: MySQLのInnoDBと同様に、SQL Serverは主キーを作成する際にデフォルトでクラスタ化インデックスを作成します。ただし、SQL Serverでは、主キーを非クラスタ化インデックスとして作成し、クラスタ化インデックスを他の列に指定するオプションもあります。
  • Oracle: Oracleは、クラスタ化インデックスと同様の動作をする索引構成表 (Index-Organized Table, IOT) というメカニズムを提供しており、テーブルデータは主キーインデックス構造内に格納されます。
  • PostgreSQL: PostgreSQLの主キーは、デフォルトで通常のB-Tree一意インデックスを作成します。これはクラスタ化インデックスではありません。PostgreSQLには、特定のインデックスに基づいてテーブルの物理的な並べ替えを一度だけ実行するCLUSTERコマンドがありますが、InnoDBやSQL Serverのように動的にクラスタ構造を維持するわけではありません。

暗黙的な主キー

ユーザーが明示的に主キーを定義しない場合でも、一部のデータベースエンジンは主キーを探すか作成しようとします。例えば、MySQLのInnoDBエンジンは、まずテーブル内の最初のUNIQUE NOT NULLインデックスを主キーとして検索します。見つからない場合は、内部的なクラスタ化インデックスキーとして非表示の6バイトの自動増分IDを自動的に作成します。これにより、すべてのInnoDBテーブルに（明示的または暗黙的に）主キーが存在することが保証され、これはそのストレージアーキテクチャの基礎となります。

2.2 ユニークキーとインデックス：機能的な連携

主キーと同様に、ユニークキー制約を定義すると、データベースシステムも自動的に一意インデックスを作成します。このインデックスの目的は同じで、一意性の高速チェックです。

主キーインデックスとの主な違いは次のとおりです。

  1. クラスタ化の有無： ユニークキーによって作成されるインデックスは通常非クラスタ化インデックス (Non-Clustered Index) です（SQL Serverなどのシステムで明示的にクラスタ化インデックスとして指定され、かつテーブルに主キーがクラスタ化インデックスでない場合を除く）。非クラスタ化インデックスのリーフノードには、インデックスキー値と、実際のデータ行へのポインタ（またはInnoDBでは主キーの値）が格納されます。
  2. 数： 1つのテーブルに複数のユニークキーを定義できるため、複数のユニークキーによって生成される一意インデックスを持つこともできます。

PostgreSQLには興味深い違いがあります。ユニーク制約 (unique constraint) とユニークインデックス (unique index) を明確に区別しています。ユニーク制約を作成すると自動的にユニークインデックスが作成されますが、制約を作成せずにユニークインデックスのみを作成することも可能です。機能的にはほぼ同等ですが、制約は概念的にデータモデルの一部であり、インデックスはよりパフォーマンス最適化の実装詳細です。

2.3 外部キーとインデックス：パフォーマンス最適化の鍵

外部キーとインデックスの関係は、特にパフォーマンス最適化の面でより微妙で重要です。外部キー制約自体は参照整合性を保証するためのものですが、外部キー列にインデックスを作成しないと、深刻なパフォーマンス問題を引き起こす可能性があります。

なぜ外部キーにインデックスが必要なのか？

次のシナリオを考えてみましょう。親テーブルCustomerAccountsからレコードを削除する場合、データベースは参照整合性を維持するために、子テーブルSalesOrdersで削除しようとしているaccount_idを参照しているレコードがないかチェックする必要があります。SalesOrders.customer_account_id列にインデックスがない場合、データベースはSalesOrdersテーブルに対してフルテーブルスキャンを実行せざるを得ません。SalesOrdersテーブルに数百万件のレコードがある場合、この削除操作は異常に遅くなり、長時間のテーブルロックを引き起こし、システム全体の同時実行性能に影響を与える可能性があります。

同様に、親テーブルの主キーを更新する場合（一般的ではありませんが、発生する可能性があります）、子テーブルのチェックも必要になります。さらに重要なのは、親テーブルと子テーブルが関与するJOINクエリにおいてです。例えば：

SELECT ca.account_name, so.order_date
FROM CustomerAccounts ca
JOIN SalesOrders so ON ca.account_id = so.customer_account_id
WHERE ca.account_id = 101;

もしSalesOrders.customer_account_idにインデックスがあれば、データベースは顧客101に関連するすべての注文を効率的に見つけることができます。インデックスがなければ、SalesOrdersテーブルに対して非効率なスキャンを実行する必要があるかもしれません。

データベースシステムのデフォルト動作の違い

外部キーにインデックスを自動的に作成するかどうかについては、主要なデータベースシステム間で動作が異なります。

  • MySQL: 以前のバージョンでは、MySQLは外部キーにインデックスを自動的に作成しませんでした。しかし現在、特にInnoDBストレージエンジンでは、外部キー制約を作成する際に、チェックや結合操作を最適化するために、通常は外部キー列に通常の（一意ではない）インデックスを自動的に作成します。
  • SQL Server & Oracle: これらのシステムは通常、外部キーにインデックスを自動的に作成しません。したがって、開発者やDBAは、ほとんどすべての外部キー列に手動でインデックスを作成しなければなりません。これは非常に重要なベストプラクティスです。外部キーにインデックスを追加し忘れることは、一般的なパフォーマンスボトルネックの原因となります。
  • PostgreSQL: PostgreSQLも外部キーにインデックスを自動的に作成しません。SQL ServerやOracleと同様に、手動で作成する必要があります。

まとめ： 主キーとユニークキーにインデックスを作成することは、制約を強制するためにデータベースが取る自動的な動作です。一方、外部キーにインデックスを作成することは、データ操作（削除、更新など）とクエリ（JOINなど）のパフォーマンスを保証するための強く推奨されるベストプラクティスであり、一部のデータベースでは手動で実行する必要があります。

3. 詳細な比較と考察

この章では、主キー、ユニークキー、外部キーを多角的に比較し、実践において混同しやすい微妙な違いについて深く掘り下げます。

3.1 主要な特性の比較表

特性 主キー (Primary Key) ユニークキー (Unique Key) 外部キー (Foreign Key)
主な目的 エンティティ整合性の強制、行の一意な識別 ビジネスルールにおける一意性の強制 参照整合性の強制、テーブル間の関係構築
一意性 必須、一意であること 必須、一意であること 任意、重複を許容する（例：複数の注文が同じ顧客に属する）
NULL値 一切不許可 許可、ただし数と動作はDBMSによって異なる 許可（列定義で許可されている場合）、関係が存在しないことを示す
テーブルごとの数 1つのみ 複数可能 複数可能
インデックス自動作成 はい、通常は一意インデックス はい、通常は一意インデックス いいえ、または状況による、手動作成を強く推奨
インデックスタイプ 多くの場合クラスタ化インデックス (MySQL InnoDB, SQL Serverなど) 通常は非クラスタ化インデックス 通常は非クラスタ化インデックス
参照関係 参照される側（外部キーに参照される） 参照される側にもなれる（外部キーに参照される） 参照する側、別のテーブルの主キーまたはユニークキーを指す

3.2 主キー vs. ユニークキー：識別子とビジネス属性の議論

両者ともに一意性を強制しますが、その設計思想と適用シナリオは大きく異なります。

  • 安定性： 主キーは安定不変であるべきです。一度レコードが作成されたら、その主キー値は変更すべきではありません。主キーの変更は非常にコストがかかり危険な操作であり、それを参照しているすべての子テーブルにカスケード更新が必要になる可能性があります。一方、ユニークキーが表すビジネス属性（例：メールアドレス、電話番号）は現実世界で変更される可能性があります。変更される可能性のある列を主キーとすることは、好ましくない設計です。
  • 簡潔性： 良い主キーは簡潔であるべきです。これが、代理主キー (Surrogate Key)（例：オートインクリメント整数やUUID）が非常に人気がある理由です。それらはビジネス上の意味を持たず、単なる識別子として機能し、通常は単一列の数値型であり、処理効率が高いです。対照的に、ユニークキーは通常自然キー (Natural Key) の一部（例：メールアドレス、運転免許証番号）であり、実際のビジネス上の意味を持ち、より長い文字列または複数の列の組み合わせである可能性があります。
  • NULLの扱い： これが最も厳密な技術的違いです。主キーのNOT NULL制約は絶対です。一方、ユニークキーのNULLに対する寛容さは、「オプションだが一意」なビジネスシナリオを処理できるようにします。例えば、従業員は個人ウェブサイトを持っていないかもしれないが、もし記入されていればそれは一意であるべきです。

結論： 主キーを選択する際には、安定性、簡潔性、効率性を保証するために、代理主キーを優先すべきです。ビジネス上の意味を持ち、一意性を保証する必要があるフィールド（例：ログイン名、メールアドレス、社員番号）はユニークキーとして定義します。

3.3 ユニークキーにおけるNULL値のデータベース間動作の違い

ユニークキーはNULL値を許容しますが、具体的にいくつのNULLを許容するかは、異なるデータベースシステム間で顕著な違いがあり、これは「落とし穴」となることがあります。この違いは、NULLの哲学的解釈に起因します。NULLはNULLと等しいのか？SQL標準では、NULLはいかなる値とも等しくなく、それ自身とも等しくありません。

  • Oracle, PostgreSQL, MySQL (InnoDB), SQLite: これらのデータベースはNULL != NULLの原則を厳密に遵守します。したがって、ユニークキーが定義された列に、任意の数のNULL値を挿入できます。データベースはこれらのNULL値を重複しているとは見なさないためです。これにより、最大の柔軟性が提供されます。
-- Oracle, PostgreSQL, MySQLでは合法
CREATE TABLE SampleUniqueNull (id INT, email VARCHAR(100) UNIQUE);
INSERT INTO SampleUniqueNull (id, email) VALUES (1, NULL);
INSERT INTO SampleUniqueNull (id, email) VALUES (2, NULL); -- 合法
INSERT INTO SampleUniqueNull (id, email) VALUES (3, NULL); -- 合法
  • Microsoft SQL Server: SQL Serverはこの点で有名な例外です。ユニーク制約を処理する際にNULL != NULLのロジックを完全には従いません。SQL Serverでは、単一列のユニークキーに対して、最大1つのNULL値しか挿入できません。2つ目のNULLを挿入しようとすると、SQL Serverはエラーを返し、一意性制約違反と見なします。最初のNULLを特別な値として扱い、それ以降のNULLを最初のNULLと重複していると見なしているようです。
-- SQL Serverでは
CREATE TABLE SampleUniqueNull (id INT, email VARCHAR(100) UNIQUE);
INSERT INTO SampleUniqueNull (id, email) VALUES (1, NULL); -- 合法
INSERT INTO SampleUniqueNull (id, email) VALUES (2, NULL); -- エラー！一意性制約違反
  • ANSI SQL 標準: ANSI SQL標準自体がこの問題について規定が曖昧であり、異なる実装方法を許容している点が、ベンダー間の動作不一致の根本原因となっています。

開発者への影響: この違いは、クロスデータベース互換アプリケーションを開発する必要がある開発者にとって非常に重要です。ユニークキーに複数のNULLを格納するロジックに依存すると、SQL Serverでは失敗します。逆に、SQL Serverの動作に慣れている開発者は、他のデータベースでも1つのNULLしか許されないと誤解する可能性があります。

3.4 外部キーの参照操作 (Referential Actions)

外部キーは単なる静的な参照ではなく、親テーブルのレコードが削除 (DELETE) されたり、参照されているキー値が更新 (UPDATE) されたりしたときに、子テーブルがどのように応答すべきかという動的な動作規則も定義します。これらの規則は、外部キーの作成時にON DELETEおよびON UPDATE句を使用して指定されます。

  • CASCADE (カスケード):
    • ON DELETE CASCADE: 親テーブルのレコードが削除された場合、そのレコードを参照しているすべての子テーブルのレコードも自動的に削除されます。これは強力だが危険なオプションであり、大規模なデータ削除につながる可能性があるため、慎重に使用する必要があります。請求書を削除すると、そのすべての請求書明細も削除されるべき、といった強力な集約関係に適用されます。
    • ON UPDATE CASCADE: 親テーブルの参照されるキー値が更新された場合、その値を参照しているすべての子テーブルの外部キー列も自動的に新しい値に更新されます。これは自然キーを主キーとして使用するシナリオで役立ちますが、主キー自体の変更は推奨されないため、このオプションの使用頻度は比較的低いです。
  • SET NULL (NULL設定):
    • ON DELETE SET NULL: 親テーブルのレコードが削除された場合、そのレコードを参照しているすべての子テーブルの外部キー列の値が自動的にNULLに設定されます。これは子テーブルの外部キー列がNULLを許容している必要があります。オプションの関係を表すシナリオに適しています。例えば、従業員が退職した場合（従業員テーブルから削除）、彼が担当していたプロジェクトのmanager_idはNULLに設定できます。
    • ON UPDATE SET NULL: 親テーブルのキー値が更新された場合、子テーブルの外部キー列がNULLに設定されます。
  • SET DEFAULT (デフォルト値設定):
    • ON DELETE SET DEFAULT: 親テーブルのレコードが削除された場合、子テーブルの外部キー列の値が自動的にその列のデフォルト値に設定されます。これはその列にデフォルト値が定義されており、そのデフォルト値が親テーブルで有効である必要があります。
    • ON UPDATE SET DEFAULT: 同様に、親テーブルのキー値が更新された場合にトリガーされます。
  • RESTRICT (制限) と NO ACTION (アクションなし):
    • これらはほとんどのデータベースのデフォルト動作です。
    • ON DELETE RESTRICT/NO ACTION: 子テーブルのレコードが親テーブルのレコードを参照している場合、データベースは該当の親レコードの削除を禁止します。RESTRICTとNO ACTIONはほとんどのシステム（PostgreSQLなど）で最終的な効果は同じですが、チェックのタイミングがわずかに異なる場合があります（RESTRICTは操作開始時にチェックし、NO ACTIONは制約が遅延可能な場合はトランザクション終了時にチェックします）。これは最も安全で保守的な戦略です。

これらの参照操作は、データの一貫性を維持するための強力な自動化ツールであり、適切に利用することでアプリケーションのロジックコードを大幅に簡素化できます。

4. 高度なトピック：複合キーの影響

単一の列ではレコードを一意に識別したり、関係を構築したりできない場合、複数の列で構成される複合キーを使用する必要があります。複合キー（複合主キー、複合ユニークキー、複合外部キーを含む）は、特に多対多の関係や複雑なエンティティを扱う際に、データベース設計において重要な役割を果たします。

4.1 複合主キーと複合ユニークキー

定義： 複合キーは、2つ以上の属性（列）が組み合わさって構成されるキーであり、これらの属性の組み合わせ値は一意でなければなりません。

典型的なシナリオ：結合テーブル (Junction/Linking Table)
多対多の関係の処理は、複合主キーの最も典型的な使用例です。例えば、1人の生徒が複数の授業を履修でき、1つの授業が複数の生徒によって履修される場合があります。この関係を表すために、StudentEnrollmentsのような中間的な「結合テーブル」が必要です。

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_title VARCHAR(100)
);

CREATE TABLE StudentEnrollments (
    student_id INT,
    course_id INT,
    enroll_date DATE,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id), -- 複合主キー
    FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id) ON DELETE CASCADE
);

StudentEnrollmentsテーブルでは、student_idとcourse_idの組み合わせ(student_id, course_id)が複合主キーを構成します。これにより、各生徒は同じ授業に一度しか登録できないことが保証されます。個々のstudent_idやcourse_idは重複可能です。

4.2 複合外部キー

複合外部キーとは、複数の列で構成される外部キーのことで、同じ列数と互換性のあるデータ型を持つ複合主キーまたは複合ユニークキーを参照する必要があります。

シナリオ： 各授業の課題を記録するCourseAssignmentsテーブルがあるとします。その主キーは(course_id, assignment_seq)かもしれません。ここで、生徒の提出物を記録するAssignmentSubmissionsテーブルが必要になります。

CREATE TABLE CourseAssignments (
    course_id INT,
    assignment_seq INT,
    assignment_name VARCHAR(200),
    PRIMARY KEY (course_id, assignment_seq),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

CREATE TABLE AssignmentSubmissions (
    submission_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    assignment_seq INT,
    submission_datetime TIMESTAMP,
    -- 複合外部キー
    FOREIGN KEY (course_id, assignment_seq) REFERENCES CourseAssignments(course_id, assignment_seq),
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

AssignmentSubmissionsテーブルでは、(course_id, assignment_seq)が複合外部キーを構成し、CourseAssignmentsテーブルの複合主キーを参照しています。これにより、各提出物が実在する、特定の授業の特定の課題に対応することが保証されます。

4.3 複合キーが参照整合性とクエリ最適化に与える影響

参照整合性の強制：
複合キーにより、参照整合性のルールをより複雑なエンティティ関係に適用できるようになります。データベースシステムは、複合外部キーのすべての列の組み合わせ値が、参照される複合キーに存在するか、または（許可されている場合）すべての列がNULLであること（MATCH SIMPLEマッチングモードの場合）を保証します。これにより、データモデルの表現能力が大幅に向上し、現実世界の複雑な依存関係を正確に記述できます。

クエリ最適化：
複合キー（主キーまたはユニークキー）にインデックスを作成すると、データベースは多列インデックス (Multi-column Index) を作成します。この種のインデックスのパフォーマンスは、クエリ条件（WHERE句）がインデックス列をどのように使用するかと密接に関連しています。

  • インデックス列の順序が重要： (col_A, col_B)上のインデックスの場合、クエリ最適化ツールは、次の条件のクエリを処理するためにこのインデックスを効率的に利用できます。
    • WHERE col_A = ?
    • WHERE col_A = ? AND col_B = ?
    • WHERE col_A > ? （範囲クエリ）
  • 最左プレフィックスの原則 (Leftmost Prefix Principle): しかし、クエリ条件が単にWHERE col_B = ?である場合、その(col_A, col_B)インデックスは通常、効率的に利用できません。クエリがインデックスの最初の列col_Aをスキップしているためです。
  • JOINへの影響： 前述のStudentEnrollmentsテーブルの例のように、複合キーを使用してJOIN操作を実行する場合、StudentsとStudentEnrollmentsの結合を含むクエリにおいて、StudentEnrollmentsテーブル上の(student_id, course_id)の主キーインデックスは、JOIN条件ON s.student_id = se.student_idに対して非常に有利です。

したがって、複合キーを設計する際には、論理的な一意性だけでなく、一般的なクエリパターンも考慮し、フィルタリングによく使用され、選択性の高い列を複合インデックスの前に配置することで、最適なクエリ最適化効果を得るべきです。

5. 設計に関する推奨事項

これまでの詳細な分析を通して、主キー、ユニークキー、外部キーが、リレーショナルデータベース設計における機能は異なるものの、相互に補完し合う3つの整合性制約ツールであることがわかります。これらが共同でデータ品質保証システムの骨格を形成します。

  • 主キー (Primary Key) は、エンティティ整合性の守護者です。その存在は、テーブル内の各行データが一意に識別され、参照可能なエンティティであることを保証します。それはデータの世界の「身分証明書」です。
  • ユニークキー (Unique Key) は、ビジネスロジックの実行者です。ビジネスにとって不可欠な特定の属性（例：メールアドレス、ユーザー名）が重複しないことを保証し、主キー以外の層でデータの一意性を保証します。
  • 外部キー (Foreign Key) は、参照整合性の維持者です。テーブル間に信頼性の高い、管理された関連を確立し、孤立したデータ島を意味のある情報ネットワークに接続し、「孤立データ」の発生を防ぎます。

データベース設計のベストプラクティス：

  1. 代理主キーの優先： 非常に十分な理由がない限り、すべての主要なエンティティテーブルにはビジネス上の意味を持たない代理主キー（オートインクリメント整数やUUIDなど）を設計すべきです。これにより、主キーの安定性、効率性、簡潔性が保証されます。
  2. ビジネスルールにユニークキーを活用： ビジネスレベルで一意性を保つ必要があるすべての自然キー（例：メールアドレス、運転免許証番号、製品SKU）をユニークキー制約として定義します。
  3. すべての外部キーにインデックスを作成： これは、データベースのパフォーマンスを向上させる上で最も重要でありながら、見過ごされがちな点です。データベースが自動的に作成するかどうかにかかわらず、各外部キー列（または列の組み合わせ）にインデックスが存在することを確認し、JOIN、UPDATE、DELETE操作におけるパフォーマンスボトルネックを回避すべきです。
  4. 参照操作の慎重な選択： CASCADE、SET NULL、RESTRICTなどの参照操作の意味を深く理解してください。デフォルトのRESTRICT/NO ACTIONが最も安全であり、結果を完全に理解している場合にのみCASCADEを使用してください。
  5. データベース間の違いを理解し適応する： 移植性の高いアプリケーションを設計する際には、特にベンダーの実装によって生じる動作の違い、特にユニークキーのNULL値の処理方法に注意を払う必要があります。
  6. 複合キーの適切な設計： 複合キーを使用する必要がある場合は、その構成列の順序を慎重に検討し、一意性制約を満たすだけでなく、最も一般的なクエリパターンを最大限に最適化できるように、最左プレフィックスの原則に従ってください。

主キー、外部キー、ユニークキーの深い理解と熟練した活用は、データベース設計者や開発者の専門的レベルを測る重要な指標です。これらの制約を適切に配置することで、データが一貫性があり、論理的に厳密なデータベースを構築できるだけでなく、上位アプリケーションの効率的で安定した運用基盤を築くことができます。

タグ: リレーショナルデータベース SQL 主キー 外部キー ユニークキー

9月21日 13:43 投稿

ホットタグ

©2026 異端開発室