外部キー

ウィキペディアから、無料の百科事典
ナビゲーションにジャンプ 検索にジャンプ

外部キーは、別のテーブルの主キーを参照するテーブル内の属性のセットです外部キーは、これら2つのテーブルをリンクします。別の言い方をすれば、リレーショナルデータベースのコンテキストでは外部キーは、特定の種類の包含依存性制約、具体的には1つの関係Rの外部キー属性で構成されるタプルが必要とする制約の対象となる属性のセットです。また、他の(必ずしも明確ではない)関係Sに存在し、さらにそれらの属性もSの候補キーでなければなりません。 [1] [2] [3]簡単に言うと、外部キーは候補キーを参照する属性のセットです。たとえば、TEAMというテーブルには、PERSONテーブル内の候補キーPERSON_NAMEを参照する外部キーである属性MEMBER_NAMEが含まれている場合があります。MEMBER_NAMEは外部キーであるため、TEAMのメンバーの名前として存在する値は、PERSONテーブルの個人の名前としても存在する必要があります。言い換えれば、チームのすべてのメンバーは人でもあります。

まとめ

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

外部キーの目的は参照されるテーブルの特定の行を識別することであるため、通常、外部キーはプライマリテーブルのある行の候補キーと等しいか、値がない(NULL値)必要があります。[ 2])。このルールは、2つのテーブル間の参照整合性制約と呼ばれます。[5] これらの制約の違反は多くのデータベースの問題の原因となる可能性があるため、ほとんどのデータベース管理システムは、null以外のすべての外部キーが参照されるテーブルの行に対応することを保証するメカニズムを提供します。[6] [7] [8]

たとえば、すべての顧客データを含むCUSTOMERテーブルとすべての顧客注文を含むORDERテーブルの2つのテーブルを持つデータベースについて考えてみます。各注文が単一の顧客を参照する必要があることをビジネスが要求するとします。これをデータベースに反映するために、外部キー列がORDERテーブル(CUSTOMERIDなど)に追加されます。この列は、CUSTOMERの主キー(IDなど)を参照します。テーブルの主キーは一意である必要があり、CUSTOMERIDにはその主キーフィールドの値のみが含まれているため、値がある場合、CUSTOMERIDは注文を行った特定の顧客を識別すると想定できます。ただし、CUSTOMERテーブルの行が削除されたり、ID列が変更されたりしたときに、ORDERテーブルが最新の状態に保たれていない場合、これは想定できなくなり、これらのテーブルの操作がより困難になる可能性があります。

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

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

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

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

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

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 

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

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

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

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

参照アクション

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

CASCADE

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

RESTRICT

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

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

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

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

アクションなし

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

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

SET NULL、SETDEFAULT

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

トリガー

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

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

CREATE  TABLE アカウント acct_num  INT  amount  DECIMAL 10、2 ;

 各行のアカウント挿入する前にトリガー ins_sum作成します@ sum = @ sum + NEW 金額;    
             

外部キーを説明する最初の例として、アカウントデータベースに請求書を含むテーブルがあり、各請求書が特定のサプライヤに関連付けられているとします。サプライヤーの詳細(名前や住所など)は別のテーブルに保管されます。各サプライヤーには、それを識別するための「サプライヤー番号」が与えられます。各請求書レコードには、その請求書のサプライヤー番号を含む属性があります。次に、「サプライヤ番号」がサプライヤテーブルの主キーになります。請求書テーブルの外部キーは、その主キーを指します。リレーショナルスキーマは次のとおりです。主キーは太字でマークされ、外部キーは斜体でマークされています。

サプライヤー(SupplierNumber、Name、Address)
 請求書(InvoiceNumber、Text、SupplierNumber

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

CREATE  TABLE  Supplier  
  SupplierNumber  INTEGER  NOT  NULL 
  Name            VARCHAR 20  NOT  NULL 
  Address         VARCHAR 50  NOT  NULL 
  CONSTRAINT  Supplier_pk PRIMARY KEY SupplierNumber )、CONSTRAINT number_value  CHECK SupplierNumber > 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 Supplier (SupplierNumber)
    制限を 削除する際更新 カスケード   

も参照してください

参照

  1. ^ コロネル、カルロス(2010)。データベースシステム:設計、実装、および管理インディペンデンスKY:南西部/センゲージラーニング。p。65. ISBN 978-0-538-74884-1
  2. ^ a b Elmasri、Ramez(2011)。データベースシステムの基礎アディソン-ウェスリー。pp。73–74  _ ISBN 978-0-13-608620-8
  3. ^ 日付、CJ(1996)。SQL標準のガイドアディソン-ウェスリー。p。206. ISBN 978-0201964264
  4. ^ シェルドン、ロバート(2005)。MySQLの開始。ジョン・ワイリー&サンズ。pp。119–122。ISBN 0-7645-7950-9
  5. ^ 「データベースの基本—外部キー」2010年3月13日取得
  6. ^ MySQL AB(2006)。MySQL管理者ガイドおよび言語リファレンスサムズパブリッシング。p。40. ISBN 0-672-32870-4
  7. ^ パウエル、ギャビン(2004)。Oracle SQL:例を使用したジャンプスタートエルゼビア。p。 11ASINB008IU3AHY_ 
  8. ^ マリンズ、クレイグ(2012)。DB2開発者ガイドIBMプレス。ASINB007Y6K9TK_ 
  9. ^ シェルドン、ロバート(2005)。MySQLの開始。ジョン・ワイリー&サンズ。p。156. ISBN 0-7645-7950-9
  10. ^ ガルシアモリーナ、ヘクター(2009)。データベースシステム:完全な本プレンティスホール。pp。93–95  _ ISBN 978-0-13-187325-4

外部リンク