外部キー

外部キーは、あるテーブル内の属性のセットであり、別のテーブルの主キーを参照し、これら 2 つのテーブルをリンクします。リレーショナル データベースのコンテキストでは、外部キーは包含依存関係制約の対象となり、あるリレーションR内の外部キー属性で構成されるタプルは、他の (必ずしも異なるとは限らない) リレーション S にも存在する必要があります。さらに、それらの属性はS 内の候補キーでもあります。^{[1] [2] [3]}

つまり、外部キーは候補キーを参照する属性のセットです。たとえば、TEAM というテーブルには、PERSON テーブル内の候補キー PERSON_NAME を参照する外部キーである属性 MEMBER_NAME があります。MEMBER_NAME は外部キーであるため、TEAM のメンバーの名前として存在する値は、PERSON テーブルにも人の名前として存在する必要があります。つまり、TEAM のすべてのメンバーは PERSON でもあります。

注意すべき重要な点:-

• 参照関係はすでに作成されているはずです。
• 参照される属性は、参照される関係の主キーの一部である必要があります。
• 参照先属性と参照元属性のデータ型とサイズは同じである必要があります。

外部キーを含むテーブルは子テーブルと呼ばれ、候補キーを含むテーブルは参照テーブルまたは親テーブルと呼ばれます。^[4]データベースリレーショナルモデリングと実装では、候補キーは0個以上の属性のセットであり、その値はリレーション内の各タプル（行）に対して一意であることが保証されています。任意のタプルの候補キー属性の値または値の組み合わせは、そのリレーション内の他のタプルと重複することはできません。

外部キーの目的は参照先のテーブルの特定の行を識別することであるため、一般に、外部キーは主テーブルのいずれかの行の候補キーと等しいか、そうでなければ値がない（NULL値）ことが求められます。 ^[2]この規則は、2つのテーブル間の参照整合性制約と呼ばれます。 ^[5] これらの制約の違反は多くのデータベースの問題の原因となる可能性があるため、ほとんどのデータベース管理システムは、すべての非NULL外部キーが参照先のテーブルの行に対応することを保証するメカニズムを提供しています。^{[6] [7] [8]}

たとえば、すべての顧客データを含む CUSTOMER テーブルとすべての顧客注文を含む ORDER テーブルという 2 つのテーブルを持つデータベースを考えてみましょう。ビジネスでは、各注文が 1 人の顧客を参照する必要があるとします。これをデータベースに反映するには、ORDER テーブルに外部キー列 (CUSTOMERID など) を追加し、CUSTOMER の主キー (ID など) を参照します。テーブルの主キーは一意である必要があり、CUSTOMERID にはその主キー フィールドの値のみが含まれるため、値がある場合、CUSTOMERID は注文を行った特定の顧客を識別すると想定できます。ただし、CUSTOMER テーブルの行が削除されたり ID 列が変更されたりしたときに ORDER テーブルが最新の状態に保たれていない場合は、この想定はできなくなります。そのため、これらのテーブルでの作業が難しくなる可能性があります。実際のデータベースの多くは、マスター テーブルの外部キーを物理的に削除するのではなく「非アクティブ化」するか、変更が必要なときに外部キーへのすべての参照を変更する複雑な更新プログラムを使用して、この問題を回避しています。

外部キーはデータベース設計において重要な役割を果たします。データベース設計の重要な部分の1つは、外部キーを使用して1つのテーブルから別のテーブルを参照し、現実世界のエンティティ間の関係が参照によってデータベースに反映されるようにすることです。^[9] データベース設計のもう1つの重要な部分は、データベースの正規化です。データベースの正規化では、テーブルが分割され、外部キーによって再構築が可能になります。^[10]

参照元 (または子) テーブル内の複数の行が、参照先 (または親) テーブル内の同じ行を参照する場合があります。この場合、2 つのテーブル間の関係は、参照元テーブルと参照先テーブル間の1 対多関係と呼ばれます。

さらに、子テーブルと親テーブルは実際には同じテーブルである可能性があり、つまり、外部キーは同じテーブルを参照します。このような外部キーは、SQL:2003では自己参照または再帰外部キーと呼ばれます。データベース管理システムでは、これは通常、最初の参照と 2 番目の参照を同じテーブルにリンクすることによって実現されます。

テーブルには複数の外部キーがあり、各外部キーには異なる親テーブルがある場合があります。各外部キーは、データベース システムによって独立して適用されます。したがって、外部キーを使用してテーブル間のカスケード関係を確立できます。

外部キーは、値が別のリレーションの主キーと一致するリレーションの属性または属性セットとして定義されます。既存のテーブルにこのような制約を追加する構文は、次に示すようにSQL:2003で定義されています。句の列リストを省略すると、REFERENCES外部キーは参照先のテーブルの主キーを参照することになります。同様に、外部キーは SQL ステートメントの一部として定義できますCREATE TABLE。

CREATE TABLE child_table ( col1 INTEGER PRIMARY KEY 、col2 CHARACTER VARYING ( 20 )、col3 INTEGER 、col4 INTEGER 、FOREIGN KEY ( col3 、col4 ) REFERENCES parent_table ( col1 、col2 ) ON DELETE CASCADE )   
     
    
   
   
          

外部キーが単一の列のみである場合、次の構文を使用してその列をそのようにマークできます。

テーブルchild_tableを作成します( col1 INTEGER PRIMARY KEY 、col2 CHARACTER VARYING ( 20 )、col3 INTEGER 、col4 INTEGER REFERENCES parent_table ( col1 ) ON DELETE CASCADE )   
     
    
   
        

外部キーはストアド プロシージャステートメントを使用して定義できます。

sp_foreignkey子テーブル、親テーブル、col3 、col4    

• child_table : 定義する外部キーを含むテーブルまたはビューの名前。
• parent_table : 外部キーが適用される主キーを持つテーブルまたはビューの名前。主キーはあらかじめ定義されている必要があります。
• col3およびcol4 : 外部キーを構成する列の名前。外部キーには少なくとも 1 列、最大 8 列が必要です。

データベース管理システムは参照制約を強制するため、参照先のテーブル内の行を削除 (または更新) する場合は、データの整合性を確保する必要があります。参照元のテーブルに従属行がまだ存在する場合は、それらの参照を考慮する必要があります。SQL :2003では、このような場合に実行される 5 つの異なる参照アクションが指定されています。

• カスケード
• 制限
• アクションなし
• NULL に設定
• デフォルト設定

親 (参照先) テーブルの行が削除 (または更新) されるたびに、一致する外部キー列を持つ子 (参照元) テーブルのそれぞれの行も削除 (または更新) されます。これをカスケード削除 (または更新) と呼びます。

参照先テーブルの値を参照する参照元テーブルまたは子テーブルに行が存在する場合、値を更新または削除することはできません。

同様に、参照元テーブルまたは子テーブルから行への参照がある限り、その行は削除できません。

RESTRICT (および CASCADE) をよりよく理解するには、すぐにはわからないかもしれない次の違いに注目すると役立つかもしれません。参照アクション CASCADE は、CASCADE という単語が使用されている (子) テーブル自体の「動作」を変更します。たとえば、ON DELETE CASCADE は、実質的に「参照されている行が他のテーブル (マスター テーブル) から削除された場合、自分からも削除する」という意味です。ただし、参照アクション RESTRICT は、マスター テーブルではなく子テーブルで「動作」を変更します。ただし、RESTRICT という単語は子テーブルに表示され、マスター テーブルには表示されません。したがって、ON DELETE RESTRICT は、実質的に「誰かが他のテーブル (マスター テーブル) から行を削除しようとした場合、その他のテーブルからの削除を防止します(もちろん、自分からも削除しないでください。ただし、これはここでの重要な点ではありません)」という意味です。

RESTRICT は、Microsoft SQL 2012 以前ではサポートされていません。

NO ACTION と RESTRICT は非常によく似ています。NO ACTION と RESTRICT の主な違いは、NO ACTION ではテーブルの変更を試みた後に参照整合性チェックが行われることです。RESTRICT では、UPDATE または DELETE ステートメントの実行を試みる前にチェックが行われます。参照整合性チェックが失敗した場合、両方の参照アクションは同じ動作をします。つまり、UPDATEまたはDELETEステートメントはエラーになります。

つまり、参照アクション NO ACTION を使用して参照先のテーブルで UPDATE または DELETE ステートメントを実行すると、DBMS はステートメント実行の最後に参照関係が違反されていないことを確認します。これは、操作が制約に違反することを最初から想定する RESTRICT とは異なります。NO ACTION を使用すると、トリガーまたはステートメント自体のセマンティクスによって、制約が最終的にチェックされるまでに外部キー関係が違反されていない最終状態が生成され、ステートメントが正常に完了することがあります。

一般に、SET NULL または SET DEFAULT に対してDBMSが実行するアクションは、ON DELETE と ON UPDATE の両方で同じです。影響を受ける参照属性の値は、SET NULL の場合は NULL に変更され、SET DEFAULT の場合は指定されたデフォルト値に変更されます。

参照アクションは、通常、暗黙のトリガー(つまり、システムによって生成された名前を持つトリガーで、非表示になっていることが多い) として実装されます。そのため、ユーザー定義トリガーと同じ制限が適用され、他のトリガーに対する実行順序を考慮する必要があります。場合によっては、適切な実行順序を確保するために、または変更テーブルの制限を回避するために、参照アクションを同等のユーザー定義トリガーに置き換えることが必要になることがあります。

トランザクション分離には、もう 1 つの重要な制限があります。行への変更は、トランザクションが「見る」ことができないデータによって参照されているため、完全にカスケードできない可能性があります。例: トランザクションが顧客アカウントの番号を変更しようとしているときに、同時トランザクションが同じ顧客に対して新しい請求書を作成しようとします。CASCADE ルールは、トランザクションが参照できるすべての請求書行を修正して、番号が変更された顧客行との一貫性を保つことができますが、別のトランザクションにアクセスしてデータを修正することはできません。2 つのトランザクションがコミットしたときにデータベースがデータの一貫性を保証できないため、どちらか一方が強制的にロールバックされます (多くの場合、先着順でロールバックされます)。

テーブルaccount ( acct_num INT 、amount DECIMAL ( 10 、2 ) )を作成します。      

account の各行のSET @ sum = @ sum + NEW . amountの前に、 INSERT ON account の前にトリガーins_sumを作成します。      
             

外部キーを説明する最初の例として、会計データベースに請求書のテーブルがあり、各請求書が特定の仕入先に関連付けられているとします。仕入先の詳細 (名前や住所など) は別のテーブルに保存され、各仕入先には識別用の「仕入先番号」が与えられます。各請求書レコードには、その請求書の仕入先番号を含む属性があります。そして、「仕入先番号」は仕入先テーブルの主キーです。請求書テーブルの外部キーは、その主キーを指します。リレーショナル スキーマは次のようになります。主キーは太字で、外部キーは斜体でマークされています。

サプライヤー (サプライヤー番号、名前、住所)
 請求書 (請求書番号、テキスト、仕入先番号)

対応するデータ定義言語ステートメントは次のとおりです。

CREATE TABLEサプライヤー(サプライヤー番号INTEGER NOT NULL 、名前VARCHAR ( 20 ) NOT NULL 、住所VARCHAR ( 50 ) NOT NULL 、制約サプライヤー_pk主キー(サプライヤー番号)、制約number_valueチェック(サプライヤー番号> 0 ) )   
     
               
            
     
      


CREATE TABLE Invoice ( InvoiceNumber INTEGER NOT NULL 、Text VARCHAR ( 4096 )、SupplierNumber INTEGER NOT NULL 、CONSTRAINT invoice_pk PRIMARY KEY ( InvoiceNumber )、CONSTRAINT inumber_value CHECK ( InvoiceNumber > 0 )、CONSTRAINT supplier_fk FOREIGN KEY ( SupplierNumber ) REFERENCES Supplier ( SupplierNumber ) ON UPDATE CASCADE ON DELETE RESTRICT )   
      
             
     
     
       
   
       
         

• 候補キー
• 複合キー
• スーパーキー
• ジャンクションテーブル

• SQL-99 外部キー
• PostgreSQL 外部キー
• MySQL 外部キー
• FirebirdSQL 主キー
• SQLite の外部キーのサポート
• Microsoft SQL 2012 テーブル制約 (Transact-SQL)