主キー制約と外部キー制約

2017-06-13

主キーと外部キーは、SQL Server テーブル内のデータに整合性を適用するために使用できる 2 種類の制約です。これらは重要なデータベースオブジェクトです。

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

主キー制約

テーブルには通常、テーブルの各行を一意に識別する値が格納された単一の列または複数の列の組み合わせがあります。この列、または列の組み合わせを、テーブルの主キー (PK) と呼び、テーブルのエンティティが整合性を持つようにします。主キー制約は一意のデータを保証するため、ID 列で頻繁に定義されます。

主キー制約をテーブルに対して指定すると、重複のないインデックスをデータベースエンジンが主キー列に対して自動的に作成し、データが一意になるようにします。また、クエリの中で主キーが使用された場合は、このインデックスによってデータに高速にアクセスできます。主キー制約が複数の列で定義されている場合、1 つの列内で値が重複する可能性がありますが、主キー制約定義内のすべての列の値の組み合わせはそれぞれ一意である必要があります。

次の図に示すように、Purchasing.ProductVendor テーブルの ProductID 列と VendorID 列は、このテーブルの複合主キー制約を形成します。これにより、 ProductVendor テーブル内のすべての行に ProductID と VendorID の一意の組み合わせが含まれます。これによって重複行の挿入が防止されます。

複合主キー制約

テーブルに含めることができる主キー制約は 1 つだけです。
主キーは 16 列、キーの合計長は 900 バイトを超えることはできません。
主キー制約によって生成されたインデックスでは、テーブルのインデックスの数が 999 個の非クラスター化インデックスと 1 つのクラスター化インデックスを超えることはできません。
主キー制約にクラスター化または非クラスター化が指定されていない場合、クラスター化インデックスがテーブルに存在しない場合は、クラスター化が使用されます。
主キー制約中で定義する列はすべて、not null として定義する必要があります。 null 許容が指定されていない場合、主キー制約に参加しているすべての列の null 許容値は null 値に設定されません。
CLR ユーザー定義型の列に対して主キーを定義する場合は、型の実装でバイナリ順がサポートされている必要があります。

外部キー制約

外部キー (FK) は、2 つのテーブルのデータ間にリンクを確立および設定することによって外部キーテーブルに格納できるデータを制御するための単一の列または複数の列の組み合わせです。外部キー参照では、1 つのテーブルの主キー値が格納されている列が別のテーブルの 1 つ以上の列によって参照されたときに、2 つのテーブル間にリンクが作成されます。この列は、2 番目のテーブルの外部キーになります。

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

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

主キー制約とは異なり、外部キー制約を作成しても、対応するインデックスは自動的に作成されません。ただし、外部キーに手動でインデックスを作成することには、通常、次のような利点があります。

クエリ内で、一方のテーブルの外部キー制約が定義された列を、他方のテーブルの主キー列または一意なキー列と照合することによって関連テーブルのデータが結合されるとき、多くの場合、外部キー列が結合基準に使用されます。インデックスを使用すると、データベースエンジンにより、外部キーテーブルの関連データがすばやく検索されます。ただし、このインデックスを作成する必要はありません。 2 つのテーブル間に主キー制約または外部キー制約が定義されていなくても、関連する 2 つのテーブルのデータを結合できます。ただし、2 つのテーブル間に外部キーリレーションシップが存在するということは、それら 2 つのテーブルが、結合基準としてキーを使用するクエリ内で結合されるように最適化されていることを示します。
主キー制約に加えた変更が、関連テーブルの外部キー制約によってチェックされます。

参照整合性

外部キー制約の主な目的は、外部キーテーブルに格納できるデータを制御することですが、主キーテーブルのデータに対する変更も制御されます。たとえば、販売員の行が Sales.SalesPerson テーブルから削除され、 Sales.SalesOrderHeader テーブルの販売注文に販売員の ID が使用されている場合、2 つのテーブル間のリレーショナル整合性が失われます。削除された販売員の販売注文は SalesOrderHeader テーブルで孤立しており、 SalesPerson テーブル内のデータへのリンクはありません。

外部キー制約を定義すると、このような状況が発生するのを防ぐことができます。制約は、これらの変更によって外部キーテーブル内のデータへのリンクが無効になった場合に、主キーテーブル内のデータに変更を加えることができないことを保証することで、参照整合性を強制します。主キーテーブル内の行の削除または主キー値の変更が試行された場合、削除または変更された主キーの値が別のテーブルの外部キー制約の値に対応すると、アクションは失敗します。外部キー制約が定義された行を正常に変更または削除するには、まず、外部キーテーブルの外部キーデータを削除または変更し、外部キーから他の主キーデータへのリンクを設定する必要があります。

連鎖参照整合性

連鎖参照整合性制約を使用することで、既存の外部キーが参照しているキーをユーザーが削除または更新するときのデータベースエンジンの動作を定義できます。以下の連鎖動作を定義できます。

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

CASCADE 対応する行は、親テーブルで行が更新または削除されると、参照元テーブルで更新または削除されます。 timestamp列が外部キーまたは参照されるキーの一部である場合は、CASCADE を指定できません。 INSTEAD OF DELETE トリガーを持つテーブルには ON DELETE CASCADE を指定できません。 ON UPDATE CASCADE は、INSTEAD OF UPDATE トリガーを持つテーブルには指定できません。

SET NULL 親テーブルの対応する行が更新または削除されると、外部キーを構成するすべての値が NULL に設定されます。この制約を実行するには、外部キー列が NULL 値を使用できる必要があります。 INSTEAD OF UPDATE トリガーを持つテーブルには指定できません。

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 トリガーを起動します。

最初に、元の DELETE または UPDATE によって直接発生するすべての連鎖参照アクションが実行されます。
影響を受けるテーブルに AFTER トリガーが定義されている場合、これらのトリガーは、すべてのカスケードアクションが実行された後に発生します。 AFTER トリガーの起動順序は連鎖動作の逆です。 1 つのテーブルに複数のトリガーがある場合は、テーブルの専用の最初または最後のトリガーがない限り、ランダムな順序で起動します。その起動順序は sp_settriggerorderでの指定に従います。
UPDATE または DELETE アクションの直接ターゲットであるテーブルから複数のカスケードチェーンが生成された場合、これらのチェーンがそれぞれのトリガーを起動する順序は指定されません。ただし、必ず 1 本のチェーンのトリガーがすべて起動した後で別のチェーンのトリガーが起動を開始します。
UPDATE アクションまたは DELETE アクションの直接ターゲットであるテーブルの AFTER トリガーは、影響を受ける行があるかどうかに関係なく発生します。その際、他のテーブルには連鎖が波及しません。
上記のいずれかのトリガーが他のテーブルに対して UPDATE または DELETE 操作を実行する場合、これらのアクションはセカンダリカスケードチェーンを開始できます。これらのセカンダリチェーンは、すべてのプライマリチェーンのすべてのトリガーが起動した後、UPDATE または DELETE 操作ごとに一度に処理されます。このプロセスは、後続の UPDATE 操作または DELETE 操作で再帰的に繰り返される場合があります。
トリガー内で CREATE、ALTER、DELETE、またはその他のデータ定義言語 (DDL) 操作を実行すると、DDL トリガーが起動する可能性があります。その後、追加のカスケードチェーンとトリガーを開始する DELETE 操作または UPDATE 操作が実行される場合があります。
特定の連鎖参照アクションチェーン内でエラーが生成された場合、エラーが発生し、そのチェーンで AFTER トリガーが発生せず、チェーンを作成した DELETE または UPDATE 操作がロールバックされます。
INSTEAD OF トリガーを持つテーブルが、カスケードするアクションを指定する参照句を持つことはできません。ただし、カスケードアクションの対象となるテーブルの AFTER トリガーは、そのオブジェクトで定義されている INSTEAD OF トリガーを起動する別のテーブルまたはビューで INSERT、UPDATE、または DELETE ステートメントを実行できます。

課題	トピック
主キーを作成する方法について説明します。	主キーの作成
主キーを削除する方法について説明します。	主キーの削除
主キーを変更する方法について説明します。	主キーの変更
外部キーリレーションシップを作成する方法について説明します	外部キーのリレーションシップの作成
外部キーリレーションシップを変更する方法について説明します。	外部キーリレーションシップの変更
外部キーリレーションシップを削除する方法について説明します。	外部キーリレーションシップの削除
外部キーのプロパティを表示する方法について説明します。	外部キーのプロパティの表示
レプリケーションの外部キー制約を無効にする方法について説明します。	レプリケーションに対して外部キー制約を無効にする方法
INSERT ステートメントまたは UPDATE ステートメント中に外部キー制約を無効にする方法について説明します。	INSERT ステートメントまたは UPDATE ステートメントに対して外部キー制約を無効にする方法

次の方法で共有

主キー制約と外部キー制約