• [アーティクル]

連鎖参照整合性制約

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

CREATE TABLE ステートメントおよび ALTER TABLE ステートメントの REFERENCES 句は、ON DELETE 句および ON UPDATE 句をサポートしています。連鎖動作は、[外部キーのリレーションシップ] ダイアログ ボックスを使用して定義することもできます。

  • [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

  • [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

NO ACTION は、ON DELETE または ON UPDATE を指定しない場合の既定値です。

  • ON DELETE NO ACTION
    他テーブルの既存の行にある外部キーで参照されるキーを含む行を削除すると、エラーが発生して DELETE ステートメントがロールバックされるように指定します。

  • ON UPDATE NO ACTION
    他テーブルの既存の行にある外部キーで参照されるキーを含む行のキー値を更新すると、エラーが発生して UPDATE ステートメントがロールバックされるように指定します。

CASCADE、SET NULL、または SET DEFAULT を指定すると、キー値の削除または更新は、外部キーのリレーションシップを定義したテーブルに反映され、変更が実行されたテーブルまでさかのぼって追跡できるようになります。連鎖参照動作が連鎖先のテーブルにも定義されている場合は、削除または更新する行についても、指定された連鎖動作が反映されます。CASCADE は、timestamp 型の列を持つ外部キーや主キーには指定できません。

  • ON DELETE CASCADE
    他テーブルの既存の行にある外部キーで参照されるキーを含む行を削除する場合、その外部キーを含む行もすべて削除されるように指定します。

  • ON UPDATE CASCADE
    他テーブルの既存の行にある外部キーで参照されるキーを含む行のキー値を更新すると、外部キーを構成するすべての値が、そのキーに設定した新しい値に更新されるように指定します。

    注意注

    CASCADE は、timestamp 型の列が外部キーまたは参照先キーの一部である場合は指定できません。

  • ON DELETE SET NULL
    他テーブルの既存の行にある外部キーで参照されるキーを含む行を削除すると、参照される行で外部キーを構成するすべての値が NULL に設定されるように指定します。この制約を適用するには、参照先のテーブルのすべての外部キー列で NULL 値が許容されている必要があります。

  • ON UPDATE SET NULL
    他テーブルの既存の行にある外部キーで参照されるキーを含む行を更新すると、参照される行の外部キーを構成するすべての値が NULL に設定されるように指定します。この制約を適用するには、参照先のテーブルのすべての外部キー列で NULL 値が許容されている必要があります。

  • ON DELETE SET DEFAULT
    他テーブルの既存の行にある外部キーで参照されるキーを含む行を削除すると、参照される行の外部キーを構成するすべての値が既定値に設定されるように指定します。この制約を適用するには、参照先のテーブルのすべての外部キー列にデフォルト定義が必要です。NULL 値を許容する列に明示的な既定値が設定されていない場合、NULL 値がその列の暗黙的な既定値になります。ON DELETE SET DEFAULT によって設定される NULL 以外の値は、外部キー制約の有効性を保つため、主テーブルの対応する値にする必要があります。

  • ON UPDATE SET DEFAULT
    他テーブルの既存の行にある外部キーで参照されるキーを含む行を更新すると、参照される行の外部キーを構成するすべての値が既定値に設定されるように指定します。この制約を適用するには、参照先のテーブルのすべての外部キー列にデフォルト定義が必要です。NULL 値を許容する列に明示的な既定値が設定されていない場合、NULL 値がその列の暗黙的な既定値になります。ON UPDATE SET DEFAULT によって設定される NULL 以外の値は、外部キー制約の有効性を保つため、主テーブルの対応する値にする必要があります。

AdventureWorks2008R2 データベースの Purchasing.ProductVendor テーブルに対する FK_ProductVendor_Vendor_VendorID 制約を考えてみます。この制約により、ProductVendor テーブルの VendorID 列から Purchasing.Vendor テーブルの VendorID 主キー列への外部キー リレーションシップが確立されます。ON DELETE CASCADE をこの制約に指定した場合、Vendor テーブルの VendorID が 100 である行を削除すると、ProductVendor テーブルの VendorID が 100 である行が 3 つ削除されます。ON UPDATE CASCADE をこの制約に指定した場合、Vendor テーブルの VendorID 値を 100 から 155 に更新すると、ProductVendor テーブルの VendorID 値が現在 100 である 3 つの行に含まれている VendorID 値も更新されます。

INSTEAD OF DELETE トリガが設定されているテーブルには、ON DELETE CASCADE を指定できません。INSTEAD OF UPDATE トリガが設定されているテーブルには、ON DELETE SET NULL、ON DELETE SET DEFAULT、ON UPDATE CASCADE、ON UPDATE SET NULL、および ON UDATE SET DEFAULT を指定できません。

複数の連鎖動作

個々の DELETE ステートメントまたは UPDATE ステートメントで、一連の連鎖参照動作を開始できます。たとえば、TableA、TableB、および TableC という 3 つのテーブルを含むデータベースがあるとします。TableB 内の外部キーには、TableA の主キーに対する ON DELETE CASCADE が定義されています。TableC 内の外部キーには、TableB の主キーに対する ON DELETE CASCADE が定義されています。DELETE ステートメントで TableA 内の行を削除すると、TableA から削除された主キーと一致する外部キーを含む行が TableB からも削除され、TableB から削除された主キーと一致する外部キーを含む行が TableC からも削除されます。

1 回の DELETE または UPDATE 操作によって起動される連鎖参照動作は、循環参照を含まないツリー形式になっている必要があります。DELETE または UPDATE 操作によって生じる一連の連鎖参照動作の影響を複数回受けるテーブルがあってはいけません。また、連鎖参照動作のツリーはすべての特定のテーブルへのパスが一意である必要があります。ツリーの分岐は、NO ACTION が指定されているテーブルか既定値であるテーブルに到達したところで終了します。

トリガと連鎖参照動作

連鎖参照動作によって、AFTER UPDATE トリガまたは AFTER DELETE トリガは次のように起動します。

元の DELETE または UPDATE によって直接発生するすべての連鎖参照動作が、まず実行されます。

連鎖動作を受けたテーブルに AFTER トリガが定義されている場合、すべての連鎖動作が実行された後でトリガが起動します。AFTER トリガの起動順序は連鎖動作の逆です。1 つのテーブル内のトリガの起動順序は、最初または最後に起動することが決まっているトリガがある場合を除きランダムです。その起動順序は sp_settriggerorder での指定に従います。

UPDATE 操作または DELETE 操作を直接受けたテーブルから複数の連鎖チェーンが始まる場合、それぞれのチェーンでトリガが起動する順序は特定できません。ただし、必ず 1 本のチェーンのトリガがすべて起動した後で別のチェーンのトリガが起動を開始します。

UPDATE 操作または DELETE 操作を直接受けるテーブルの AFTER トリガは、どの行も影響を受けないとしても起動します。その際、他のテーブルには連鎖が波及しません。

あるトリガで他のテーブルに対し UPDATE 操作または DELETE 操作が行われた場合、操作の後に 2 次的な連鎖チェーンが開始される場合があります。この 2 次的なチェーンは、最初に発生したチェーンのすべてのトリガが起動した後でそれぞれの UPDATE 操作または DELETE 操作ごとに処理されます。その後の UPDATE 操作または DELETE 操作についても、この処理が再帰的に繰り返されます。

CREATE、ALTER、DELETE、またはその他の DDL (データ定義言語) 操作をトリガ内で実行すると、DDL トリガが起動する場合があります。その結果、新たな連鎖チェーンを開始したりトリガを起動したりする DELETE 操作または UPDATE 操作が実行されることもあります。

特定の連鎖参照動作のチェーン内でエラーが発生した場合、エラーが表示され、そのチェーンの AFTER トリガは起動されずに、チェーンを開始した DELETE 操作または UPDATE 操作がロールバックされます。

INSTEAD OF トリガを含んだテーブルに、連鎖動作を指定する REFERENCES 句を同時に指定することはできません。ただし、連鎖動作の対象になるテーブルの AFTER トリガは、別のテーブルやビューに対して INSERT、UPDATE、または DELETE ステートメントを実行し、そのオブジェクトに定義されている INSTEAD OF トリガを起動できます。

連鎖参照制約カタログ情報

sys.foreign_keys カタログ ビューにクエリを実行すると返される値から、外部キーに指定した連鎖参照制約がわかります。

値

説明

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

sp_fkeys および sp_foreignkeys が返す UPDATE_RULE 列および DELETE_RULE 列は、CASCADE、SET NULL、SET DEFAULT のいずれかが指定されている場合 0 を返し、NO ACTION が指定されているかそれが既定値である場合 1 を返します。

sp_help のオブジェクトとして外部キーを指定すると、出力結果セットには次に示す新しい列が含まれます。

列名

データ型

説明

delete_action

nvarchar(9)

DELETE 操作が CASCADE、SET NULL、SET DEFAULT、NO ACTION、N/A (適用なし) のどれであるかを示します。

update_action

nvarchar(9)

UPDATE 操作が CASCADE、SET NULL、SET DEFAULT、NO ACTION、N/A (適用なし) のどれであるかを示します。