Articles

主キーと外部キーの制約

Posted on
  • 2017年07月25日
  • 9分で読める
    • s
    • M
    • M
    • c
    • J
    • +1

以下に適用されます。 yesSQL Server 2016 (13.x)以降 YesAzure SQL Database YesAzure SQL Managed Instance

主キーと外部キーは、SQL Serverのテーブルでデータの整合性を確保するために使用できる2種類の制約条件です。

このトピックは以下のセクションで構成されています。

主キーの制約

外部キーの制約

関連するタスク

主キーの制約

テーブルには通常、テーブル内の各行を一意に識別する値を含む列または列の組み合わせがあります。 この列は、テーブルの主キー (PK) と呼ばれ、テーブルのエンティティの整合性を確保します。

テーブルに主キー制約を指定すると、データベース エンジンは主キー カラムに対して一意のインデックスを自動的に作成し、データの一意性を確保します。 また、このインデックスは、主キーをクエリで使用する際にデータへの高速アクセスを可能にします。

次の図のように、Purchasing.ProductVendor テーブルの ProductID と VendorID カラムは、このテーブルの複合主キー制約を形成しています。 これにより、ProductVendorテーブルのすべての行は、ProductIDとVendorIDの組み合わせが一意であることが確認されます。

複合主キー制約

  • 1つのテーブルには、1つの主キー制約しか含めることができません。

  • 主キーは、16列、キーの長さの合計が900バイトを超えることはできません。

  • 主キー制約によって生成されたインデックスは、テーブル上のインデックスの数が 999 個の非クラスター化インデックスと 1 個のクラスター化インデックスを超える原因となることはありません。

  • 主キー制約にclusteredまたはnonclusteredが指定されていない場合、テーブル上にclusteredインデックスがない場合はclusteredが使用されます。

  • 主キー制約内で定義されたすべての列は、not nullとして定義されなければなりません。

  • 主キーが CLR ユーザー定義型の列で定義されている場合、その型の実装はバイナリ順序をサポートする必要があります。

外部キーの制約

外部キー (FK) は、2 つのテーブルのデータ間のリンクを確立し、強制するために使用される列または列の組み合わせで、外部キー テーブルに格納できるデータを制御します。 外部キーの参照では、あるテーブルの主キー値を保持する列またはカラムが、別のテーブルの列またはカラムによって参照されることで、2つのテーブル間にリンクが作成されます。

例えば、Sales.SalesOrderHeaderテーブルは、Sales.SalesPersonテーブルへの外部キーリンクを持っています。これは、販売注文と営業担当者の間に論理的な関係があるためです。 SalesOrderHeaderテーブルのSalesPersonID列は、SalesPersonテーブルの主キー列と一致します。 SalesOrderHeaderテーブルのSalesPersonID列は、SalesPersonテーブルの外部キーとなります。 この外部キーの関係を作ることで、SalesPersonテーブルにSalesPersonIDの値がまだ存在しない場合、SalesOrderHeaderテーブルにSalesPersonIDの値を挿入することはできません。

1つのテーブルは、最大253個の他のテーブルや列を外部キーとして参照することができます（発信参照）。 SQL Server 2016 (13.x)では、1つのテーブルのカラムを参照できる他のテーブルとカラムの数(着信参照)の制限が、253から10,000に増加しています。 (最低でも130の互換性レベルが必要です。)この増加には次のような制限があります:

  • 253以上の外部キー参照は、DELETE DML操作でのみサポートされます。

  • 自分自身への外部キー参照を持つテーブルは、依然として 253 個の外部キー参照に制限されています。

  • 253 個以上の外部キー参照は、カラムストア インデックス、メモリ最適化テーブル、Stretch Database、またはパーティション化された外部キー テーブルでは現在利用できません。

外部キー制約のインデックス

主キー制約とは異なり、外部キー制約を作成しても対応するインデックスは自動的に作成されません。

  • 外部キー列は、一方のテーブルの外部キー制約の列を他方のテーブルの主キーまたはユニーク キーの列と一致させることにより、関連するテーブルからのデータをクエリで結合する際の結合条件で頻繁に使用されます。 インデックスを使用すると、データベース エンジンは外部キー テーブルの関連データをすばやく見つけることができます。 ただし、このインデックスの作成は必須ではありません。 関連する 2 つのテーブルからのデータは、テーブル間に主キーまたは外部キー制約が定義されていなくても結合できますが、2 つのテーブル間に外部キー関係があるということは、キーを基準として使用するクエリで結合できるように 2 つのテーブルが最適化されていることを示します。

  • 主キー制約への変更は、関連するテーブルの外部キー制約でチェックされます。

Referential Integrity

外部キー制約の主な目的は、外部キー テーブルに格納できるデータを制御することですが、主キー テーブルのデータへの変更も制御します。 例えば、Sales.SalesPersonテーブルから営業担当者の行が削除され、その営業担当者のIDがSales.SalesOrderHeaderテーブルの販売注文に使用されていた場合、2つのテーブル間のリレーショナル・インテグリティは崩れ、削除された営業担当者の販売注文は、SalesPersonテーブルのデータへのリンクがないままSalesOrderHeaderテーブルに孤児となってしまいます。 この制約は、主キーテーブルのデータに変更を加えても、その変更によって外部キーテーブルのデータへのリンクが無効になるようなことがないことを保証することで、参照整合性を確保します。 主キーテーブルの行を削除したり、主キーの値を変更しようとしても、削除または変更された主キーの値が他のテーブルの外部キー制約の値と一致する場合、そのアクションは失敗します。

カスケード参照整合性

カスケード参照整合性制約を使用することで、既存の外部キーが指すキーをユーザーが削除または更新しようとしたときに、データベース エンジンが取るアクションを定義することができます。

NO ACTION
データベース エンジンはエラーを発生させ、親テーブルの行に対する削除または更新アクションはロールバックされます。

CASCADE
親テーブルで行が更新または削除されると、参照テーブルでも対応する行が更新または削除されます。 CASCADE は、タイムスタンプ列が外部キーまたは参照キーの一部である場合には指定できません。 INSTEAD OF DELETEトリガがあるテーブルでは、ON DELETE CASCADEは指定できません。

SET NULL
親テーブルの対応する行が更新または削除されると、外部キーを構成するすべての値がNULLに設定されます。 この制約を実行するには、外部キーの列がNULLである必要があります。

SET DEFAULT
親テーブルの対応する行が更新または削除された場合、外部キーを構成するすべての値がデフォルト値に設定されます。 この制約が実行されるためには、すべての外部キーの列がデフォルトの定義を持つ必要があります。 列がNULL可能で、明示的なデフォルト値が設定されていない場合、NULLがその列の暗黙のデフォルト値になります。 INSTEAD OF UPDATE トリガーを持つテーブルには指定できません。

CASCADE、SET NULL、SET DEFAULT、NO ACTION は、互いに参照関係を持つテーブルで組み合わせることができます。 データベース エンジンは、NO ACTION が発生した場合、関連する CASCADE、SET NULL、SET DEFAULT のアクションを停止し、ロールバックします。 DELETE ステートメントで CASCADE、SET NULL、SET DEFAULT、NO ACTION のアクションが組み合わされている場合、データベース エンジンが NO ACTION をチェックする前に CASCADE、SET NULL、SET DEFAULT のアクションがすべて適用されます。

トリガーと連鎖的参照アクション

連鎖的参照アクションは、次のような方法で AFTER UPDATE または AFTER DELETE トリガーを起動します。

  • 影響を受けるテーブルに AFTER トリガーが定義されている場合、これらのトリガーはすべてのカスケード アクションが実行された後に発火します。 これらのトリガーは、カスケード アクションとは逆の順序で起動します。 1つのテーブルに複数のトリガーがある場合、そのテーブルに専用の最初または最後のトリガーがない限り、ランダムな順序で発火します。

  • 複数のカスケード チェーンが UPDATE または DELETE アクションの直接のターゲットであるテーブルから発生した場合、これらのチェーンがそれぞれのトリガーを起動する順序は指定されません。

  • UPDATE または DELETE アクションの直接のターゲットであるテーブル上の AFTER トリガーは、影響を受ける行があるかどうかに関係なく起動します。 この場合、カスケードの影響を受ける他のテーブルはありません。

  • 前のトリガーのいずれかが他のテーブル上でUPDATEまたはDELETE操作を実行した場合、これらのアクションは二次カスケード チェーンを開始できます。 これらのセカンダリ チェーンは、すべてのプライマリ チェーン上のすべてのトリガーが発火した後、UPDATE または DELETE 操作ごとに一度に処理されます。

  • トリガーの内部で CREATE、ALTER、DELETE などのデータ定義言語 (DDL) 操作を実行すると、DDL トリガーが起動することがあります。

  • 特定のカスケード参照アクション チェーン内でエラーが発生した場合、エラーが発生し、そのチェーン内で AFTER トリガーは発生せず、チェーンを作成した DELETE または UPDATE 操作はロールバックされます。

  • INSTEAD OF トリガーを持つテーブルは、カスケード動作を指定する REFERENCES 句も持つことができません。

    • 関連するタスク

    次の表は、主キー制約と外部キー制約に関連する一般的なタスクの一覧です。

    タスク トピック
    主キーを作成する方法について説明します。 Create Primary Keys
    Delete Primary Keys
    Delete Primary Keys
    Primary Keyの修正方法について説明します。 Modify Primary Keys
    Described how to create foreign key relationships
    Create Foreign Key Relationships
    Described how to modify foreign key relationships. Modify Foreign Key Relationships
    Described how to delete foreign key relationships. Delete Foreign Key Relationships
    Described how to view foreign key properties. 外部キーのプロパティを表示する
    レプリケーションのために外部キー制約を無効にする方法について説明します。 レプリケーションのために外部キー制約を無効にする
    INSERTまたはUPDATEステートメントの間に外部キー制約を無効にする方法について説明します。 INSERTおよびUPDATEステートメントで外部キー制約を無効にする

    コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です