家 › 設定 › 外部キーを含むリレーションが呼び出されます。主キー制約と外部キー制約。図3.4。正規化された関係のセット

外部キーを含むリレーションが呼び出されます。主キー制約と外部キー制約。図3.4。正規化された関係のセット

最終更新日: 2017/07/02

データベースには、さまざまなリンクによって相互接続されたテーブルを含めることができます。関係は、異なるタイプのエンティティ間の関連を表します。

リレーションシップを選択するときは、主テーブルまたは親テーブル (主キーテーブル/マスターテーブル) と依存する子テーブル (外部キーテーブル/子テーブル) が選択されます。子テーブルは親テーブルに依存します。

外部キーは通信を整理するために使用されます。外部キーは、あるテーブルの 1 つ以上の列を表し、別のテーブルの潜在的なキーでもあります。外部キーはメインテーブルの主キーと一致する必要はありません。ただし、原則として、依存テーブルの外部キーはメインテーブルの主キーを指します。

テーブル間の関係には次のタイプがあります。

1対1

1 対多

多対多(多対多)

1対1のコミュニケーション

このタイプの接続はあまり見られません。この場合、あるエンティティのオブジェクトは、別のエンティティの 1 つのオブジェクトのみに関連付けることができます。たとえば、一部のサイトでは、ユーザーは 1 つのブログしか持てません。つまり、1 人のユーザー - 1 つのブログという関係が生じます。

多くの場合、このタイプのリレーションシップには、1 つの大きなテーブルを複数の小さなテーブルに分割することが含まれます。この場合、主親テーブルには頻繁にアクセスされるデータが引き続き含まれますが、子従属テーブルには通常、アクセス頻度が低いデータが格納されます。

この点において、従属テーブルの主キーは、同時にメインテーブルの主キーを参照する外部キーでもあります。

たとえば、Users テーブルはユーザーを表し、次の列があります。

UserId(id, 主キー)

名前 (ユーザー名)

また、Blogs テーブルはユーザーのブログを表し、次の列があります。

BlogId (識別子、主キーおよび外部キー)

名前（ブログ名）

この場合、BlogId 列には、users テーブルの UserId 列の値が格納されます。つまり、BlogId 列は主キーと外部キーの両方として機能します。

1対多の関係

これは最も一般的な接続タイプです。このタイプのリレーションシップでは、子テーブルの複数の行が親テーブルの 1 つの行に依存します。たとえば、1 つのブログに複数の記事を含めることができます。この場合、blogs テーブルが親で、article テーブルが子になります。つまり、1 つのブログに多数の記事が含まれます。または別の例として、複数のサッカー選手がサッカーチームでプレーすることもできます。そして同時に、1 人のサッカー選手は一度に 1 つのチームでしかプレーできません。つまり、1 つのチーム - 多くのプレーヤーです。

たとえば、ブログ記事を表す Articles というテーブルがあり、次の列があるとします。

ArticleId(id, 主キー)

BlogId (外部キー)

タイトル（記事タイトル）

本文（記事本文）

この場合、articles テーブルの BlogId 列には、blogs テーブルの BlogId 列の値が格納されます。

多対多の関係

このタイプのリレーションシップでは、テーブル A の 1 つの行をテーブル B の多数の行に関連付けることができます。さらに、テーブル B の 1 つの行をテーブル A の多数の行に関連付けることができます。典型的な例は学生とコースです。1 人の学生が、複数のコースを受講するため、1 つのコースに複数の学生が登録することができます。

別の例は、記事とタグです。1 つの記事に対して複数のタグを定義でき、複数の記事に対して 1 つのタグを定義できます。

しかし、SQL Server では、データベースレベルで 2 つのテーブル間に直接の多対多の関係を確立できません。これは補助ステージングテーブルを通じて行われます。このステージングテーブルのデータが別のエンティティを表す場合があります。

たとえば、記事とタグの場合、2 つの列を持つ Tags テーブルがあるとします。

TagId(識別子、主キー)

テキスト（タグテキスト）

また、次のフィールドを含む中間テーブル ArticleTags を作成します。

TagId (識別子、主キーおよび外部キー)

ArticleIdId (識別子、主キーおよび外部キー)

技術的には、2 つの 1 対多の関係が得られます。 ArticleTags テーブルの TagId 列は、Tags テーブルの TagId 列を参照します。また、ArticleTags テーブルの ArticleId 列は、Articles テーブルの ArticleId 列を参照します。つまり、ArticleTags テーブルの TagId 列と ArticleId 列は複合主キーを表し、Articles テーブルと Tags テーブルとの関係の外部キーでもあります。

参照データの整合性

主キーと外部キーを変更する場合は、次の点に注意する必要があります。参照データの整合性(参照整合性)。その基本的な考え方は、データベース内の 2 つのテーブルが同じデータを保存して一貫性を維持するというものです。データの整合性は、テーブル間に正しいリンクがあり、テーブル間に正しく構築された関係を表します。どのような場合にデータの整合性が侵害される可能性がありますか?

削除異常(削除異常)。メインテーブルから行が削除されるときに発生します。この場合、従属テーブルの外部キーは、メインテーブルから削除された行を参照し続けます。

挿入異常（挿入異常）。行が依存テーブルに挿入されるときに発生します。この場合、従属テーブルの外部キーは、メインテーブルのどの行の主キーとも一致しません。

異常を更新する(アップデート異常)。このような異常により、同じテーブルの複数の行に同じオブジェクトに属するデータが含まれる可能性があります。ある行のデータを変更すると、別の行のデータと競合する可能性があります。

削除異常

削除の異常を解決するには、外部キーに次の 2 つの制約のいずれかを設定する必要があります。

従属テーブルの行にメインテーブルの行が必ず必要な場合は、外部キーに対してカスケード削除が設定されます。つまり、メインテーブルから行が削除されると、関連する行が従属テーブルから削除されます。

依存テーブルの行がメインテーブルの行との関係を許可しない場合 (つまり、そのような関係はオプションです)、関連する行がメインテーブルから削除されるときに、外部キーは NULL に設定されます。外部キー列は NULL 可能である必要があります。

挿入異常

依存テーブルにデータを追加する際の挿入異常を解決するには、外部キーを表す列が NULL 可能である必要があります。したがって、追加されたオブジェクトがメインテーブルと接続していない場合、外部キー列には NULL 値が含まれます。

異常を更新する

更新異常の問題を解決するには、後で説明する正規化を使用します。

最終更新日: 2019 年 4 月 27 日

外部キーを使用すると、テーブル間の関係を確立できます。外部キーは、従属する従属テーブルの列に設定され、メインテーブルの列の 1 つを指します。通常、外部キーは、関連するマスターテーブルの主キーを指します。

テーブルレベルで外部キーを設定するための一般的な構文は次のとおりです。

外部キー (column1、column2、...columnN) REFERENCES main_table (main_table_column1、main_table_column2、...main_table_columnN)

外部キー制約を作成するには、FOREIGN KEY の後に、外部キーを表すテーブル列を指定します。 REFERENCES キーワードの後には、関連テーブルの名前が示され、その後、外部キーが指す関連列の名前が括弧内に示されます。 REFERENCES 式の後には ON DELETE 式と ON UPDATE 式があり、それぞれメインテーブルから行を削除するときと更新するときのアクションを指定します。

たとえば、2 つのテーブルを定義し、外部キーを使用してそれらをリンクしてみましょう。

CREATE TABLE Customers (Id INT PRIMARY KEY AUTO_INCREMENT、Age INT、FirstName VARCHAR(20) NOT NULL、LastName VARCHAR(20) NOT NULL、Phone VARCHAR(20) NOT NULL UNIQUE); CREATE TABLE 注文 (Id INT PRIMARY KEY AUTO_INCREMENT、CustomerId INT、CreatedAt Date、FOREIGN KEY (CustomerId) REFERENCES Customers (Id));

この場合、Customers テーブルと Orders テーブルが定義されます。顧客がメインであり、クライアントを代表します。注文は依存しており、顧客による注文を表します。 Orders テーブルは、CustomerId 列を通じて Customers テーブルとその ID 列にリンクされています。つまり、CustomerId 列は、Customers テーブルの Id 列を指す外部キーです。

CONSTRAINT 演算子を使用して、外部キー制約の名前を指定できます。

CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT order_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id));

削除時と更新時

ON DELETE ステートメントと ON UPDATE ステートメントを使用すると、関連する行がメインテーブルからそれぞれ削除または変更されたときに実行されるアクションを設定できます。次のオプションをアクションとして使用できます。

CASCADE: マスターテーブル内の関連する行が削除または変更されると、依存テーブルから行が自動的に削除または変更されます。

SET NULL: メインテーブルから関連行を削除または更新するときに、外部キー列を NULL に設定します。 (この場合、外部キー列は NULL 設定をサポートする必要があります)

RESTRICT: 依存テーブルに関連する行がある場合、メインテーブルの行の削除または変更を拒否します。

アクションなし: 制限と同じ。

SET DEFAULT: メインテーブルから関連行を削除するときに、外部キー列を DEFAULT 属性を使用して指定されたデフォルト値に設定します。このオプションは原則として利用可能ですが、InnoDB エンジンはこの式をサポートしていません。

カスケード削除

カスケード削除を使用すると、メインテーブルから行を削除するときに、依存テーブルからすべての関連行を自動的に削除できます。これを行うには、CASCADE オプションを使用します。

CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT、CustomerId INT、CreatedAt Date、FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE);

ON UPDATE CASCADE 式も同様に機能します。主キーの値を変更すると、それに関連付けられた外部キーの値も自動的に変更されます。ただし、主キーが変更されることはほとんどなく、変更可能な値を持つ列を主キーとして使用することは一般に推奨されないため、ON UPDATE 式が実際に使用されることはほとんどありません。

NULLの設定

外部キーに SET NULL オプションを設定する場合、外部キー列は NULL を許可する必要があります。

CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT、CustomerId INT、CreatedAt Date、FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);

この図は、仮想企業の従業員に関する情報を含む表 (次数 5 の比率) を示しています。テーブルの行はタプルに対応します。実際には、各行は 1 つの現実世界のオブジェクト (この場合は従業員) の説明であり、その特性が列に含まれています。関係関係はエンティティのセットに対応し、タプルはエンティティに対応します。関係関係を表すテーブル内の列は、属性.

各属性はドメイン上で定義されるため、ドメインは特定の属性の有効な値のセットと考えることができます。同じ関係の複数の属性、さらには異なる関係の属性を同じドメイン上で定義できます。

値がタプルを一意に識別する属性は、と呼ばれます。鍵 (または単に鍵）。この値は企業の従業員ごとに一意であるため、キーは従業員番号属性です。いくつかの属性の値を連結することによってのみタプルが識別される場合、そのリレーションは複合キーを持つと言われます。

主キー- リレーショナルデータモデルにおいて、主キー (またはデフォルトキー) として選択される、関係の潜在的なキーの 1 つ。

リレーションには複数のキーを含めることができます。キーの 1 つは常に宣言されます 主要な、その値は更新できません。他のすべてのリレーションキーが呼び出されます 可能なキー.

理論的な観点から見ると、すべての潜在的な (可能な) リレーションキーは同等です。つまり、同じ一意性と最小性のプロパティを持ちます。ただし、主キーは通常、潜在的なキーの中から、特定の実用的な目的 (たとえば、 外部の 他の点でキーを使用したり、クラスター化インデックスを作成したりできます。したがって、原則として、サイズ (物理ストレージ) が最も小さいもの、および/または含まれる属性の数が最も少ないものが主キーとして選択されます。

もし 主キーと呼ばれる単一の属性で構成されます。 簡単な鍵で.

もし 主キー 2 つ以上の属性で構成されるものを、 複合キー。したがって、名、姓、父称、パスポート番号、パスポートシリーズは、2 人以上で同じである可能性があるため、個別に主キーにすることはできません。しかし、同じシリーズと番号を持つ同じ種類の個人文書は 2 つありません。したがって、人物に関するデータを含むリレーションでは、主キーは個人文書の種類、そのシリーズ、および番号で構成される属性のサブセットになる可能性があります。

階層データモデルやネットワークデータモデルとは異なり、リレーショナルデータモデルにはグループ関係の概念がありません。異なる関係のタプル間の関連を反映するには、それらのキーの複製が使用されます。

他のリレーションシップのキーのコピーである属性は、 外部キー.

たとえば、DEPARTMENT 関係と EMPLOYEE 関係の間の関係は、主キーをコピーすることによって作成されます。 「部門番号」 最初の関係から二番目の関係まで。したがって、特定の部門の従業員のリストを取得するには、次のことが必要です。 1) DEPARTMENT テーブルから、属性値を設定します。 「部門番号」 、この「Department_Name」に対応します。 2) EMPLOYEE テーブルのすべてのレコード、属性値を選択します 「部門番号」これは前のステップで取得したものと同じです。従業員がどの部門で働いているかを調べるには、次の逆の操作を実行する必要があります。 1) 決定します。 「部門番号」 EMPLOYEE テーブルから。 2) 取得した値を使用して、DEPARTMENT テーブル内のエントリを見つけます。

18. リレーショナルデータベースの正規化、データベース設計における正規形の概念。

正規形 - リレーショナルデータモデルにおける関係のプロパティ。冗長性の観点からそれを特徴づけます。データのサンプリングまたは変更により論理的に誤った結果が生じる可能性があります。正規形は、関係が満たさなければならない一連の要件として定義されます。

データベースを標準形式に変換するプロセスは次のように呼ばれます。 正規化 。正規化は、データベース構造を最小限の冗長性を提供する形式にすることを目的としています。つまり、正規化は、作業の生産性を低下または向上させたり、データベースのボリュームを削減または増加させることを目的としたものではありません。正規化の最終的な目標は、データベースに保存されている情報の潜在的な不整合を軽減することです。

冗長性の除去は、原則として、主要な事実のみが各関係に保管されるように関係を分解することによって実行されます (つまり、保管されている他の事実から推論されない事実)。

機能的な依存関係。

リレーショナルデータベースには、構造情報と意味情報の両方が含まれています。データベースの構造は、データベースに含まれるリレーションシップの数とタイプ、およびこれらのリレーションシップのタプル間に存在する 1 対多のリレーションシップによって決まります。セマンティック部分は、これらの関係の属性間に存在する一連の機能依存関係を記述します。関数の依存性を定義しましょう。

19. 1NF: 基本的な定義と変換ルール。

第一正規形について議論するには、次の 2 つの定義が必要です。

単純な属性 - 値がアトミック（分割不可能）である属性。

複合属性 - 同じまたは異なるドメインで定義できるいくつかのアトミック属性を接続することによって取得されます (ベクトルまたはデータ集合体とも呼ばれます)。

第一正規形の定義:

すべての属性の値がアトミックである場合、リレーションは 1NF になります。。それ以外の場合、それはまったくテーブルではないため、そのような属性は分解する必要があります。

例を見てみましょう:

企業の人事部門のデータベースには、従業員に関する情報を保存し、関連して提示できるようにする必要があります。

従業員(従業員番号、名前、生年月日、職歴、子供)。

この関係を注意深く検討すると、属性は次のようになります。 "職歴"そして "子供たち"属性は複雑であり、 "職歴"別の複雑な属性が含まれています 「給与_履歴」。
これらのユニットは次のようになります。

 JOB_HISTORY (RECEPTION_DATE、NAME、SALARY_HISTORY)、

 SALARY_HISTORY (APPOINTMENT_DATE、SALARY)、

 子供 (CHILD_NAME、BIRTH_YEAR)。

それらの接続を図に示します。 3.3.

図3.3。最初の態度。

元の関係 SERVANT を第 1 正規形にするには、次の図に示すように、それを 4 つの関係に分解する必要があります。

図3.4。正規化された関係のセット。

ここでは、各リレーションシップの主キーが青色の枠で強調表示され、外部キーの名前は青色のフォントで表示されます。外部キーは、ソースリレーションに存在する関数の依存関係を表すために使用されることを思い出してください。これらの機能の依存関係は、矢印付きの線で示されています。

正規化アルゴリズムは、E.F. Codd によって次のように説明されています。

ツリーの最上位のリレーション (図 3.3.) から始めて、その主キーが取得され、その主キーのドメインまたはドメインの組み合わせを挿入することによって、直下の各リレーションが展開されます。
このように拡張された各リレーションの主キーは、拡張前のリレーションが持っていた主キーと、親リレーションの追加された主キーで構成されます。
この後、すべての非単純ドメインが親関係から削除され、ツリーの最上位ノードが削除され、同じ手順が残りのサブツリーのそれぞれに対して繰り返されます。

20. 2NF: 基本的な定義そして変換ルール。

多くの場合、リレーションシップの主キーには複数の属性が含まれます (この場合、複合) - たとえば、図に示されている CHILDREN という関係を参照してください。 3.4 質問 19. 同時に、概念が導入されます。 完全な機能依存.

意味：

非キー属性が全体としてキー全体に機能的に依存している場合、非キー属性は機能的に複合キーに完全に依存していますが、その構成属性のいずれにも機能的に依存していません。

例：

SUPPLY (N_SUPPLIER, PRODUCT, PRICE) という関係があるとします。
サプライヤーが異なる製品を供給する場合もあれば、同じ製品が異なるサプライヤーから供給される場合もあります。その場合、リレーションキーは次のようになります。 「N_サプライヤー + 製品」。すべてのサプライヤーが同じ価格で商品を供給できるようにします。次に、次のような機能的な依存関係があります。

N_サプライヤー、製品 -> 価格
製品 -> 価格

キーに対する価格属性の不完全な機能依存により、次のような異常が発生します。品目の価格が変更されると、そのサプライヤーに関するすべてのレコードを変更するために、関係の完全なビューが必要になります。この異常は、2 つの意味論的事実が 1 つのデータ構造に結合されているという事実の結果です。次の展開により、2NF の関係が得られます。

配達 (N_SUPPLIER、製品)
PRODUCT_PRICE (製品、価格)

だからあなたは与えることができます

第 2 正規形の定義: リレーションが 1NF にあり、キー以外の各属性が完全に機能的にキーに依存している場合、そのリレーションは 2NF にあります。

21. 3NF: 基本的な定義そして変換ルール。

第 3 正規形について説明する前に、次の概念を導入する必要があります。 推移関数依存.

意味：

X、Y、Z をある関係の 3 つの属性とする。この場合、X --> Y および Y --> Z ですが、逆の対応はありません。 Z -/-> Y および Y -/-> X。この場合、Z は X に推移的に依存します。
STORAGE ( 固い、WAREHOUSE、VOLUME)、倉庫から商品を受け取る企業とこれらの倉庫の量に関する情報が含まれます。キー属性 - "固い"。各企業が 1 つの倉庫からのみ商品を受け取ることができる場合、これに関して次の機能上の依存関係が存在します。

固い -> ストック
ストック -> 音量

この場合、次のような異常が発生します。

現時点で倉庫から商品を受け取っている企業がない場合、その量に関するデータをデータベースに入力することはできません（キー属性が定義されていないため）。
倉庫の量が変更された場合は、関係全体を表示し、この倉庫に関連付けられているすべての会社のカードを変更する必要があります。

これらの異常を排除するには、元の関係を 2 つに分解する必要があります。

ストレージ（固い、ストック）
ストレージ_ボリューム ( ストック、音量）

第 3 正規形の定義:

リレーションが 2NF にあり、キー以外の各属性が主キーに推移的に依存しない場合、そのリレーションは 3NF にあります。

InterBase は次のタイプの制限を使用できます。

PRIMARY KEY - テーブルの主キー。
UNIQUE - 一意のテーブルキー。
外部キー- 外部キー。別のテーブルへのリンクを提供し、親テーブルとテーブル間の参照整合性を保証します。子テーブル.

用語に関する注意

あなたがこのコースの著者のように、興味のある質問に対する答えを、さまざまな著者によるさまざまな作品から総合的に探したい場合は、定義のいくつかの混乱に気付かずにはいられません。 メイン（マスター） -> 部下（詳細）テーブル。主テーブルは親テーブルと呼ばれることが多く、従属テーブルは子テーブルと呼ばれることが多いことを思い出してください。

これはおそらく、これらの定義がローカル DBMS および SQL サーバー DBMS でどのように解釈されるかによるものです。

ローカル DBMS では、主テーブルには主データが含まれ、従属テーブルには追加データが含まれます。たとえば、関連する 3 つのテーブルを考えてみましょう。 1 つ目は売上に関するデータ、2 つ目は製品に関するデータ、3 つ目は顧客に関するデータが含まれています。

米。 18.1.

ここでは、主要な情報が sales テーブルに格納されているため、これがメイン (親) テーブルになります。追加情報は product テーブルと customer テーブルに保存されます。これは、これらが子であることを意味します。これは理解できます。1 人の娘が 2 人の実の母親を持つことはできませんが、1 人の母親が 2 人の娘を産むことは十分に可能です。

ただし、SQL データベースサーバーでは、リレーションシップの定義が異なります。テーブル内の 1 つのフィールドが別のテーブルのフィールドを参照する場合、それはリレーションシップと呼ばれます。外部キー。そしてそれが参照するフィールドは次のように呼ばれます親か主キー。外部キー (別のテーブルのレコードへのリンク) を持つテーブルは子と呼ばれることが多く、外部キーを持つテーブルは子と呼ばれます。親キー- 親。また、リレーションシップの定義では、親は一意のレコードを 1 つだけ持つことができ、そのレコードは複数のレコードから参照できると述べられています。子テーブル.

したがって、上の例では、sales テーブルに製品 ID と顧客 ID という 2 つの外部キーがあります。図の右側の両方のテーブルには、親キー"識別子"。 sales テーブルには同じ顧客または製品が繰り返し現れる可能性があるため、図の右側の両方のテーブルが親であり、左側のテーブルが子であることがわかります。私たちは今勉強中なので InterBase - SQLデータベースサーバーについては、後続の講義でこれらの定義に従って説明します。この混乱についてこれ以上頭を悩ませないために、すぐに同意しましょう。子テーブル別のテーブルへの外部キー (FOREIGN KEY) があります。

主キー

主キー- 主キーは、データベースにおける主要な種類の制限の 1 つです。主キーはテーブル内のレコードを一意に識別するように設計されており、一意である必要があります。主キー PRIMARY KEY はテーブル内にあり、通常は親 (Parent) と呼ばれます。主キーはローカルデータベースの主インデックスと混同しないでください。主キーはインデックスではなく、制約です。主キーを作成する場合インターベース彼のために自動的に作成します一意のインデックス。ただし、作成すると、一意のインデックス、これでは作成されません主キー制約。テーブルには主キー PRIMARY KEY を 1 つだけ持つことができます。

従業員のリストを含むテーブルがあるとします。 Last Name フィールドには重複した値 (同名) を含めることができるため、主キーとして使用することはできません。まれですが、同じ名前を持つ同名人もいます。さらにまれに、完全な同名者が存在するため、「姓」+「名」+「父称」の 3 つのフィールドすべてであっても、レコードの一意性を保証できず、主キーにすることはできません。この場合、解決策は、前と同様に、この人のシリアル番号を含む識別子フィールドを追加することです。このようなフィールドは通常、自動インクリメントされます (自動インクリメントフィールドの編成については次の講義で説明します)。それで、

主キー テーブル内の 1 つ以上のフィールドであり、その組み合わせはレコードごとに一意です.

主キーに単一の列が含まれている場合 (ほとんどの場合)、PRIMARY KEY 指定子は次の場合に使用されます。列の定義:

CREATE TABLE Prim_1(Stolbec1 INT NOT NULL 主キー、Stolbec2 VARCHAR(50))

主キーが複数の列に基づいて構築されている場合、指定子はすべてのフィールドを定義した後に配置されます。

CREATE TABLE Prim_2(Stolbec1 INT NOT NULL、Stolbec2 VARCHAR(50) NOT NULL、主キー (Stolbec1, Stolbec2))

例からわかるように、主キーは NOT NULL 列制約が必要です.

個性的

個性的- 固有のキー。 UNIQUE 指定子は、このフィールドのすべての値が一意である必要があることを示します。したがって、そのようなフィールドにも値を含めることはできません。ヌル。 UNIQUE キーは主キーの代替であると言えますが、違いがあります。主な違いは、主キーは 1 つだけである必要があるのに対し、一意のキーは複数存在できることです。さらに、UNIQUE 制約は、PRIMARY KEY または他の UNIQUE 制約に使用されたのと同じ列のセットに構築することはできません。主キーと同様に、一意のキーは、他のテーブルの親であるテーブルにあります。

主キーと同様に、UNIQUE 制約を使用して宣言された列を使用すると、その親キーとその親キーとの間の参照整合性を強制できます。子テーブル。この場合、外部キーは子テーブルこのフィールドを参照します。主キーと同様に、一意のキーが作成されると、一意のインデックス。しかし、その逆はありません。 1 つの主キーと 2 つの一意キーを持つテーブルを作成する例:

CREATE TABLE Prim_3(Stolbec1 INT NOT NULL 主キー、Stolbec2 VARCHAR(50) NOT NULL UNIQUE、Stolbec3 FLOAT NOT NULL UNIQUE)

外部キー

外部キー- 外部キー。これは、テーブル間の参照整合性を確保するための非常に強力なツールであり、正しいリンクの存在を監視するだけでなく、リンクを自動的に管理することもできます。外部キーは、他のテーブルの子 (Child) であるテーブルに含まれます。参照整合性主キーまたはキーを参照する外部キーによって正確に提供されます。

そこで、私たちは静かに、主キーと外部キーという非常に重要なトピックに取り組みました。前者がほぼすべての人に使用されている場合、後者はどういうわけか無視されます。しかし無駄だった。外部キーは問題ではなく、データの整合性を保つのに非常に役立ちます。

1.2.5. 主キー

キーフィールドについてはすでにたくさん説明しましたが、これまで一度も使用したことはありません。最も興味深いのは、すべてがうまくいったことです。これは、Microsoft SQL Server および MS Access データベースの長所、あるいは短所かもしれません。このトリックは Paradox テーブルでは機能せず、キーフィールドがないとテーブルは読み取り専用になります。

キーはある程度制約であり、宣言が同様の方法で行われ、CONSTRAINT ステートメントも使用されるため、キーは CHECK ステートメントと組み合わせて考慮することができます。例を挙げてこのプロセスを見てみましょう。これを行うには、「guid」と「vcName」という 2 つのフィールドからなるテーブルを作成します。これにより、「guid」フィールドが主キーとして設定されます。

CREATE TABLE Globally_Unique_Data (guid uniqueidentifier DEFAULT NEWID()、vcName varchar(50)、CONSTRAINT PK_guid PRIMARY KEY (Guid))

ここで最も重要な部分は CONSTRAINT 行です。ご存知のとおり、このキーワードの後には制約の名前が続き、キー宣言も例外ではありません。主キーに名前を付けるには、命名タイプ PK_name を使用することをお勧めします。name は主キーとなるフィールドの名前です。略語 PK は主キーに由来します。

この後に、制約で使用した CHECK キーワードの代わりに PRIMARY KEY 演算子があり、これはチェックが必要ではなく主キーが必要であることを示します。キーを構成する 1 つ以上のフィールドが括弧内に示されています。

キーフィールドでは 2 つの行が同じ値を持つことはできないことに注意してください。主キー制約は一意制約と同一です。これは、姓を格納するフィールドを主キーにすると、そのようなテーブルに異なる名前の 2 つの Ivanov を書き込むことができなくなることを意味します。これは主キー制約に違反します。これが、キーが制約であり、CHECK 制約と同じ方法で宣言される理由です。ただし、これは一意性のある主キーと副キーにのみ当てはまります。

この例では、主キーは uniqueidentifier (GUID) タイプのフィールドです。このフィールドのデフォルト値は、NEWID サーバープロシージャの結果です。

注意

テーブルに対して作成できる主キーは 1 つだけです

例を簡略化するために、キーとして数値タイプを使用することをお勧めします。データベースが許可する場合は、「オートインクリメント」タイプ (数値が自動的に増減する) の方がよいでしょう。 MS SQL Server では、このフィールドは IDENTITY であり、MS Access では「カウンタ」タイプのフィールドです。

次の例は、自動インクリメント整数フィールドを主キーとして持つ積テーブルを作成する方法を示しています。

CREATE TABLE Products (id int IDENTITY(1, 1)、product varchar(50)、Price Money、数量 numeric(10, 2)、CONSTRAINT PK_id PRIMARY KEY (id))

キーフィールドには理解しやすい数値が格納され、操作がより簡単かつ視覚的に行われるため、最も頻繁に使用するのはこのタイプのキーです。

主キーは複数の列で構成できます。次の例では、フィールド「id」と「Product」が主キーを形成するテーブルを作成します。これは、両方のフィールドに一意のインデックスが作成されることを意味します。

CREATE TABLE Products1 (id int IDENTITY(1, 1), Product varchar(50), Price Money, Quantity numeric(10, 2), CONSTRAINT PK_id PRIMARY KEY (id, [製品名]))

多くの場合、プログラマーは整数の形式のキーフィールドを持つデータベースを作成しますが、同時にタスクでは特定のフィールドが一意である必要があることが明確に示されます。一意である必要があるフィールドから主キーをすぐに作成してはいかがでしょうか。そうすれば、この問題に対して個別のソリューションを作成する必要がなくなります。

複数列の主キーの唯一の欠点は、リレーションシップの作成の問題です。ここでは、さまざまな方法を使用してそれから抜け出す必要がありますが、問題はまだ解決できます。 uniqueidentifier タイプのフィールドを入力し、それを使用して接続を確立するだけです。はい、この場合、一意の主キーと uniqueidentifier 型のフィールドを取得しますが、結果としてこの冗長性は、主キーが uniqueidentifier である同じテーブルより大きくなることはなく、必要なフィールドには一意性制約が設定されます。一意であること。何を選ぶか？特定のタスクと、どのような作業がより快適かによって異なります。

1.2.6. 外部キー

外部キーは CONSTRAINT 制約でもあり、2 つのテーブル間の関係を表します。 2 つのテーブルがあるとします。

名前 – 人の名前が含まれ、識別子フィールド (キーフィールド)、名前で構成されます。
Phones は、識別子 (キーフィールド)、名前テーブルに接続するための外部キー、および電話番号を格納するための文字列フィールドで構成される電話テーブルです。

1 人が複数の電話機を所有できるため、データストレージを異なるテーブルに分割しました。図 1.4 は、2 つのテーブル間の関係を視覚的に示しています。すでにリンクテーブルを使用したことがある場合は、これで十分です。接続について初めて聞いた場合は、問題を詳しく見てみましょう。

たとえば、3人がけのテーブルを考えてみましょう。表 1.3 に「名前」テーブルの内容を示します。行は 3 つだけで、それぞれに独自のマスターキーがあります。一意性を高めるために、テーブルを作成するときに、キーを自動的に増加するフィールドにします。

表 1.3 名前テーブルの内容

表1.4。電話テーブルの内容

表 1.4 には 5 つの電話番号が含まれています。マスターキーフィールドには一意のマスターキーも含まれており、これを自動的に増分することもできます。二次キーは、Names テーブルの主キーとの関係です。この接続はどのように機能するのでしょうか? Petrov は、Names テーブルの主キーとして数値 1 を持ち、Phones テーブルの二次キーで数値 1 を検索し、Petrov の電話番号を取得します。残りのエントリについても同様です。図 1.5 に接続を視覚的に示します。

このタイプのデータストレージは非常に便利です。関連テーブルを作成できない場合は、名前テーブルですべての電話番号を 1 つのフィールドに入力する必要があります。これは、使用、保守、データ検索の観点からすると不便です。

テーブル内に複数の Names フィールドを作成できますが、いくつ必要かという疑問が生じます。携帯電話は 1 人につき 1 台しか持てませんが、たとえば私は仕事用を除いて 3 台持っています。フィールドの数が多いとデータの冗長性が生じます。

「名前」テーブルで電話番号ごとに姓を含む個別の行を作成できますが、これが簡単なのは、姓を入力するだけで済み、複数の電話番号で Petrov の複数のエントリを簡単に作成できるような単純な例の場合に限られます。数字。フィールドが 10 または 20 ある場合はどうなるでしょうか? したがって、外部キーによってリンクされた 2 つのテーブルの作成をリスト 1.6 に示します。

リスト1.6。外部キーでリンクされたテーブルの作成

CREATE TABLE 名 (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Phones (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)、CONSTRAINT FK_idName FOREIGN KEY (idName) REFERENCES Names (idName))

出品内容をよくご確認ください。これは、すでに説明した演算子の一部を使用しているため、非常に興味深いものであり、追加の例が役立つでしょう。どちらのテーブルでも、最初に作成されるキーフィールドは int 型で、1 から開始して 1 ずつ自動的に増分されます。キーフィールドは、CONSTRAINT 制約を使用してメインキーになります。

Phones テーブルの説明の最後の行には、新しい宣言、つまり FOREIGN KEY 演算子を使用した外部キーの宣言が含まれています。ご覧のとおり、これも制限であり、その理由は後で説明します。別のテーブルにリンクする必要があるテーブルフィールドは括弧内に示されています。この後にキーワード REFERENCES (リンク)、接続に使用するテーブルの名前 (Names)、および括弧内にフィールドの名前 (「idName」) が続きます。したがって、図 1.4 に示す接続が作成されました。

注意！

外部キーは、別のテーブルの主キーまたは一意制約のみを参照できます。これは、REFERENCES キーワードの後にテーブル名が必要であり、括弧内に指定できるのは主キーまたは UNIQUE 制約のあるフィールドのみであることを意味します。他のフィールドは指定できません。

さて、テーブルにデータを入力できるかどうか。次の 3 チームは、表 1.3 で見た 3 つの名前を追加します。

INSERT INTO 名前(vcName) VALUES("Petrov") INSERT INTO 名前(vcName) VALUES("Ivanov") INSERT INTO 名前(vcName) VALUES("Sidorov")

すでに SQL を使用したことがある場合は、電話テーブルのエントリを追加できます。これらのコマンドは省略しますが、CD の Chapter1 ディレクトリにある foreign_keys.sql ファイルで確認できます。

ここでのタスクは、外部キーの制限的な動作が何であるかを確認することです。それを理解しましょう。異なるテーブルの 2 つのフィールド間の明示的な関係を指定しました。姓を含むテーブルの同じ名前のフィールドに存在しない (名前は別の名前に変更できる)、「idName」フィールドの識別子を持つレコードを電話テーブルに追加しようとすると、エラーが発生します。。これにより、2 つのテーブル間の関係が壊れ、外部キー制約により、関係なしではレコードが存在できなくなります。

この制限は、レコードを変更または削除する場合にも適用されます。たとえば、Petrov という姓の行を削除しようとすると、外部キー制約エラーが発生します。外部に関連する行を持つレコードは削除できません。まず、このエントリのすべての電話番号を削除する必要があります。その後初めて、Petrov という姓の行を削除できます。

外部キーを作成する場合は、ON DELETE CASCADE または ON UPDATE CASCADE を指定できます。この場合、Names テーブルから Petrov のレコードを削除するか、識別子を変更すると、Petrov の行に関連付けられている Phones テーブル内のすべてのレコードが自動的に更新されます。一度もない。いいえ、大文字で書く必要があります。決してこれを行わないでください。すべてを手動で削除または変更する必要があります。ユーザが誤って名前テーブルからエントリを削除すると、対応する電話機も削除されます。制限の半分がなくなってしまったら、外部キーを作成しても意味がありません。すべてを手動で行う必要があり、識別子の変更は決して推奨されません。

テーブル自体の削除も従属テーブル、つまり Phones から開始する必要があり、その後でのみメインの Names テーブルを削除できます。

最後に、2 つのテーブルから名前と電話番号の一致を取得することがいかに美しいかを示します。

SELECT vcName, vcPhone FROM Names, Phones WHERE Names.idName=Phones.idName

このようなクエリについては、第 2 章で詳しく説明します。ここでは、関連テーブルの威力を理解していただくために例を挙げました。

テーブルには最大 253 個の外部キーを含めることができます。これは、最も複雑なデータベースでも十分です。個人的には、テーブルごとの外部キーの数が 7 を超えないデータベースを扱う必要がありました。それを超える場合は、例外はありますが、データベースの設計が間違っている可能性があります。

テーブル自体にも、最大 253 個の外部キーを含めることができます。テーブル内の外部キーはそれほど一般的ではなく、通常は 3 つ以下です。ほとんどの場合、テーブルには他のテーブルへのリンクが多数存在します。

外部キーは、それが作成されたのと同じテーブルを参照できます。たとえば、表 1.5 に示すような、組織内の役職の表があるとします。テーブルは、主キー、外部キー、役職の 3 つのフィールドで構成されます。どの組織にも多くの役職がある可能性がありますが、その名前と従属構造を 1 つの表に表示するのは非常に合理的です。これを行うには、外部キーを位置テーブルの主キーに関連付ける必要があります。

表1.5。内部リンクのあるテーブル

その結果、ゼネラルディレクターはゼロの外部キーを持っていることがわかります。この地位は他のすべての地位の先頭に立っています。 CMディレクターと総務ディレクターにとって、外国のキーポイントは総ディレクターの列を指します。これは、これら 2 つの役職が CEO に直属であることを意味します。等々。

これらすべてを SQL クエリとして作成する方法を見てみましょう。

CREATE TABLE 位置 (idPosition int IDENTITY(1,1)、idParentPosition int、vcName varchar(30)、CONSTRAINT PK_idPosition PRIMARY KEY (idPosition)、CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) REFERENCES Positions (idPosition))

ご覧のとおり、外部キーは作成しているのと同じテーブルを参照しているだけです。 CD の Chapter1 ディレクトリにある、foreign_keys_to_self.sql ファイルには、このテーブルを作成し、データを入力し、従属を考慮して位置を表示する例が示されています。次の章では、このようなテーブルを操作する可能性について詳しく見ていきます。

1対1の関係

ここまでは、メインデータテーブルの 1 行が関連テーブルの 1 行に対応する、古典的なリレーションシップについて見てきました。この関係は 1 対多と呼ばれます。しかし、他にも接続があります。ここでは別の接続、つまりメインテーブル内の 1 つのレコードが別のレコードの 1 つに接続されている場合の 1 対 1 について見ていきます。これを実装するには、両方のテーブルの主キーをリンクするだけで十分です。主キーは繰り返すことができないため、両方のテーブルで 1 つの行のみを関連付けることができます。

次の例では、主キー関係を持つ 2 つのテーブルを作成します。

CREATE TABLE 名前 (idName uniqueidentifier DEFAULT NEWID()、vcName varchar(50)、CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID()、vcPhone varchar(10)、CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)、制約 FK_idPhone 外部キー (idPhone) 参照名 (idName))

外部キーが必要なテーブルは 1 つだけです。リレーションシップは 1 対 1 であるため、どのテーブルに作成しても問題ありません。

多対多

最も複雑な関係は多対多で、1 つのテーブルの多くのレコードが別のテーブルの多くのレコードと一致します。これを実装するには、2 つのテーブルでは不十分で、3 つのテーブルが必要です。

まず、多対多のリレーションシップがいつ使用できるかを理解する必要があります。家の居住者のリストと電話番号のリストという 2 つのテーブルがあるとします。 1 つのアパートに複数の番号がある場合があります。つまり、1 つの姓に 2 つの電話番号がある場合があります。一対多の関係があることがわかります。一方、1 つのアパート (共同アパート、または所有者の電話を使用する入居者のみ) に 2 つの家族が住むこともできます。これは、電話と居住者間の接続も 1 対多であることを意味します。そして最も困難な選択肢は、共同アパートに 2 台の電話を置くことです。この場合、両方の番号がアパートの複数の居住者によって使用されます。つまり、「多くの」家族が「多くの」電話（多対多の通信）を使用できることがわかりました。

多対多の関係を実装するにはどうすればよいですか? 一見すると、これはリレーショナルモデルでは不可能です。約 10 年前、私はさまざまなオプションを探すのに長い時間を費やしましたが、その結果、冗長データが含まれる 1 つのテーブルを作成するだけでした。しかし、ある日、私に 1 つのタスクが与えられました。そのおかげで、条件から優れた解決策が浮かび上がりました。アパートの住民と電話番号のテーブルを 2 つ作成し、そこに主キーのみを実装する必要がありました。このテーブルでは外部キーは必要ありません。ただし、テーブル間の接続は 3 番目の接続テーブルを介して行う必要があります。一見すると、これは難しくてわかりにくいですが、この方法を理解すると、このソリューションの真価がわかります。

表 1.6 と表 1.7 に、それぞれ姓と電話のテーブルの例を示します。また、表 1.8 にリンクテーブルを示します。

表1.6。姓表

表1.7。電話テーブル

表1.8。電話テーブル

次に、多対多の関係におけるデータ検索ロジックがどのようになるかを見てみましょう。イワノフに属するすべての電話を見つける必要があるとします。 Ivanov の主キーは 1 です。リンクテーブルで、「名前との関係」フィールドが 1 に等しいすべてのレコードが見つかります。これらはレコード 1 と 2 になります。これらのレコードの「電話との関係」フィールドには、はそれぞれ識別子 1 と 2 であり、これは、イワノフが電話テーブルの 1 行目と 2 行目にある番号を所有していることを意味します。

次に、逆問題を解決しましょう。電話番号 567575677 に誰がアクセスできるかを判断します。電話テーブルのこの番号にはキー 3 があります。リンクテーブル内のすべてのレコードを探します。「電話接続」フィールドのレコードは次と等しいです。 3. これらは番号 4 と 5 のレコードで、「名前リンク」フィールドにはそれぞれ値 2 と 3 が含まれています。姓の表を見ると、2 番と 3 番の下にペトロフとシドロフが表示されます。これは、これら 2 人の住民が電話番号 567575677 を使用していることを意味します。

3 つのテーブルをすべて確認し、どの電話番号がどの居住者に属しているのか、またその逆も理解していることを確認してください。この関係を見れば、それが 3 ペニーほどの単純さであり、プロジェクトにすぐに実装できることが理解できるでしょう。

CREATE TABLE 名前 (idName uniqueidentifier DEFAULT NEWID()、vcName varchar(50)、CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE 電話 (idPhone uniqueidentifier DEFAULT NEWID()、vcPhone varchar(10)、CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)) CREATE TABLE LinkTable (idLinkTable uniqueidentifier DEFAULT NEWID()、idName uniqueidentifier、idPhone uniqueidentifier、CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable)、CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERENCES Phones (idPhone)、CONSTRAINT FK_idName FOREIGN KEY (idName REF) ERENCES Names (idName) ))

リンクテーブルには、名前テーブルと電話テーブルにリンクする 2 つの外部キーと、レコードが一意であることを保証する 1 つの主キーがあります。

この特定の問題を解決するのに便利なため、主キーとして GUID フィールドを選択しました。実際には、2 つのテーブルにレコードを挿入する必要があり、どちらの場合も同じキーを指定する必要があります。 GUID 値を生成し、両方のテーブルにデータを挿入するときに使用できます。

自動的に増加するフィールドをキーとして使用することもできますが、この場合、問題の解決が少し難しくなる、または問題を解決するのが不便になります。たとえば、電話番号を追加する場合は、まず対応する行をテーブルに挿入し、次にそれを検索し、その行に割り当てられているキーを特定して、接続を確立する必要があります。

この段階ではテーブルの作成のみに限定されていますが、セクション 2.8 ではこのトピックに戻り、関連するテーブルの操作方法を学び続けます。このスキームには 2 つのテーブルだけが含まれるため、1 対 1 と 1 対多のリレーションシップの操作はそれほど違いはありません。多対多の関係はリンクテーブルのせいで少し複雑になるため、セクション 2.27 で個別に説明します。

カテゴリ内で人気のあるもの: