MENU
  1. ホーム
  2. SQL
  3. SQLにおけるER図のキー: 主キー、外部キー、候補キーの識別方法

SQLにおけるER図のキー: 主キー、外部キー、候補キーの識別方法

SQL

SQLデータベース設計の基本であるER図（エンティティ・リレーションシップ図）は、データベース内のデータとその関係を視覚的に表現するための重要なツールです。本記事では、ER図における主キー、外部キー、候補キーの識別方法について詳しく解説します。これらのキーの役割とその識別方法を理解することで、より効率的かつ正確なデータベース設計が可能になります。

目次

ER図とは

ER図（エンティティ・リレーションシップ図）は、データベース内のエンティティ（データの対象）とそれらの間の関係を視覚的に表現する図です。ER図を使用することで、データベース設計者はシステム全体のデータ構造を明確に理解し、データの重複や不整合を防ぐことができます。ER図は主にエンティティ、属性、リレーションシップの3つの基本要素から構成されます。

エンティティ

エンティティとは、データベース内で表現される実体であり、具体的な物や事象を指します。例えば、顧客、製品、注文などがエンティティに該当します。

属性

属性とは、エンティティが持つ特性や情報を表します。例えば、顧客エンティティの属性には、名前、住所、電話番号などがあります。

リレーションシップ

リレーションシップは、エンティティ同士の関係を示します。例えば、顧客と注文の間には「注文する」というリレーションシップがあります。

主キー（Primary Key）の定義と識別方法

主キー（Primary Key）は、テーブル内の各レコードを一意に識別するためのキーです。主キーは必ずユニークであり、NULL値を持つことはできません。主キーを適切に設定することで、データの一意性と整合性を維持することができます。

主キーの定義

主キーは、テーブル内のレコードを一意に特定するために使用される1つ以上の列（フィールド）で構成されます。主キーは一意性を保証するため、各レコードは異なる値を持つ必要があります。

主キーの特徴

  • 一意性: 各レコードを一意に識別できる。
  • 非NULL: NULL値を持つことができない。
  • 単一または複合: 一つの列で構成されることもあれば、複数の列を組み合わせて構成されることもある（複合主キー）。

主キーの識別方法

主キーを識別するためには、以下の基準を使用します。

  • 一意性の確認: 候補キーの中から一意性を持つ列を選びます。
  • NULL値の確認: 候補キーの中からNULL値を持たない列を選びます。

SQLでの主キーの設定

以下は、SQLで主キーを設定する例です。

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactName VARCHAR(100),
    Country VARCHAR(50)
);

この例では、CustomerID列が主キーとして設定されており、各顧客レコードを一意に識別します。

外部キー（Foreign Key）の定義と識別方法

外部キー（Foreign Key）は、テーブル間のリレーションシップを定義するために使用されるキーです。外部キーは、あるテーブルの列が他のテーブルの主キーを参照することで、データの整合性を保ちます。

外部キーの定義

外部キーは、テーブルの列が他のテーブルの主キーまたは候補キーを参照するキーです。これにより、データベース内のテーブル間の関係性が確立されます。

外部キーの特徴

  • リレーションシップの構築: 外部キーは、あるテーブルのレコードが他のテーブルのレコードとどのように関連しているかを示します。
  • 参照整合性: 外部キー制約を使用することで、参照先のデータが存在することを保証します。参照先のデータが削除された場合、関連するデータの更新や削除が制御されます。

外部キーの識別方法

外部キーを識別するためには、以下の基準を使用します。

  • 参照先の確認: 参照するテーブルの主キーまたは候補キーを特定します。
  • リレーションシップの確認: 関連するテーブル間のリレーションシップを確認し、外部キーを設定します。

SQLでの外部キーの設定

以下は、SQLで外部キーを設定する例です。

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber INT,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

この例では、OrdersテーブルのCustomerID列がCustomersテーブルのCustomerID列を参照する外部キーとして設定されています。これにより、各注文がどの顧客に関連しているかを示します。

候補キー（Candidate Key）の定義と識別方法

候補キー（Candidate Key）は、テーブル内のレコードを一意に識別できる可能性のある列または列の組み合わせです。候補キーは、主キーとして選択されることができるすべてのキーを指します。

候補キーの定義

候補キーは、テーブル内の各レコードを一意に特定できる一つ以上の列で構成されます。テーブルには複数の候補キーが存在する場合がありますが、そのうちの一つが主キーとして選ばれます。

候補キーの特徴

  • 一意性: 各レコードを一意に識別できる。
  • 非NULL: 候補キーの列はNULL値を持つことができない。
  • 複数存在可能: 一つのテーブルには複数の候補キーが存在することがある。

候補キーの識別方法

候補キーを識別するためには、以下の基準を使用します。

  • 一意性の確認: 各列または列の組み合わせが一意の値を持つかどうかを確認します。
  • 非NULLの確認: 各候補キーの列がNULL値を持たないことを確認します。
  • 最小性の確認: 最小の列数で一意性を保証する列の組み合わせを特定します。

SQLでの候補キーの設定

以下は、SQLで候補キーを示す例です。

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    NationalIDNumber VARCHAR(15) NOT NULL,
    EmployeeNumber VARCHAR(10) NOT NULL,
    EmployeeName VARCHAR(100),
    CONSTRAINT UQ_NationalIDNumber UNIQUE (NationalIDNumber),
    CONSTRAINT UQ_EmployeeNumber UNIQUE (EmployeeNumber)
);

この例では、EmployeesテーブルにおいてEmployeeIDが主キーとして設定されていますが、NationalIDNumberおよびEmployeeNumberも一意制約（UNIQUE制約）が設定されており、これらが候補キーであることを示しています。

主キーと外部キーの関係

主キーと外部キーの関係は、データベース設計におけるリレーションシップを表現する重要な要素です。主キーと外部キーを適切に設定することで、データの整合性と参照整合性が確保されます。

主キーと外部キーの基本的な関係

主キーは、テーブル内の各レコードを一意に識別するためのキーです。一方、外部キーは、他のテーブルの主キーを参照するためのキーです。外部キーを使用することで、テーブル間のリレーションシップが確立されます。

リレーションシップの種類

主キーと外部キーを使用することで、以下のようなリレーションシップを表現できます。

  • 一対多（1:N）リレーションシップ: 1つの主キーが複数の外部キーに関連する場合。
  • 多対多（N:M）リレーションシップ: 多くの主キーが多くの外部キーに関連する場合。この場合、中間テーブルを使用してリレーションシップを表現します。

リレーションシップの例

以下は、CustomersテーブルとOrdersテーブルの間に一対多のリレーションシップを設定する例です。

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactName VARCHAR(100),
    Country VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber INT,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

この例では、CustomersテーブルのCustomerIDが主キーとして定義されており、OrdersテーブルのCustomerIDが外部キーとして設定されています。これにより、各注文がどの顧客に関連しているかを示すことができます。

参照整合性の確保

外部キー制約を使用することで、参照整合性を確保します。例えば、参照されている主キーが削除された場合、それに関連する外部キーの処理（削除や更新）を制御することができます。

実際のデータベース設計におけるキーの選定

データベース設計において、適切なキーの選定はデータの整合性と効率的なクエリパフォーマンスを確保するために重要です。ここでは、実際のデータベース設計でキーを選定する際の基準とベストプラクティスについて解説します。

キー選定の基準

キーを選定する際には、以下の基準を考慮します。

  • 一意性: 各レコードを一意に識別できるかどうかを確認します。
  • 安定性: 時間が経過してもキーの値が変更されないことが望ましいです。変更が頻繁に発生するキーは避けます。
  • シンプルさ: キーはできるだけシンプルで短いものを選びます。複雑なキーは理解しにくく、クエリパフォーマンスに影響を与えることがあります。
  • 自然キー vs. サロゲートキー: 実世界のデータを反映する自然キー（例えば、社員番号やメールアドレス）を使うか、システムが生成するサロゲートキー（例えば、自動インクリメントのID）を使うかを決定します。

主キーの選定

主キーを選定する際は、以下の点に注意します。

  • 一意性と非NULL制約: 主キーは必ず一意であり、NULL値を持たない列を選びます。
  • 安定性: 主キーは変更されない列を選びます。例えば、社員番号や社会保障番号などは適切な主キーになり得ます。

外部キーの選定

外部キーを選定する際は、以下の点に注意します。

  • リレーションシップの明確化: 外部キーは、参照するテーブルの主キーまたは候補キーと明確なリレーションシップを持つ必要があります。
  • 参照整合性の維持: 外部キーを設定することで、参照整合性を確保し、データの一貫性を保ちます。

候補キーの選定

候補キーを選定する際は、以下の点に注意します。

  • 一意性の確認: 候補キーは、テーブル内の他の列と一意性を保つ必要があります。
  • 冗長性の排除: 必要以上に多くの候補キーを設定しないようにし、シンプルな設計を心がけます。

ベストプラクティス

  • サロゲートキーの使用: 多くの実務環境では、シンプルで一意性が保証されるサロゲートキーを主キーとして使用することが一般的です。例えば、自動インクリメントの整数IDを使用します。
  • 自然キーの補完: 自然キーもデータの整合性を保つために使用しますが、主キーとしてはサロゲートキーを使用し、自然キーには一意制約（UNIQUE制約）を設定することが推奨されます。

ER図におけるキーの表記方法

ER図（エンティティ・リレーションシップ図）では、キーの種類を明確に表現するために、特定の記号やスタイルが使用されます。ここでは、ER図における主キー、外部キー、候補キーの表記方法について説明します。

主キーの表記方法

主キーは、ER図でエンティティの属性の中で一意性を持つ属性として特別に表記されます。一般的な表記方法には以下のようなものがあります。

  • 下線: 主キーを表す属性に下線を引きます。
  • 例: CustomerID
  • PKラベル: 属性名の横に「(PK)」を追加します。
  • 例: CustomerID (PK)

外部キーの表記方法

外部キーは、ER図で他のエンティティの主キーを参照する属性として表記されます。一般的な表記方法には以下のようなものがあります。

  • FKラベル: 属性名の横に「(FK)」を追加します。
  • 例: CustomerID (FK)
  • 破線: 外部キーの属性を破線で囲みます。
  • 例: -- CustomerID --

候補キーの表記方法

候補キーは、主キーとして選択される可能性のあるキーとして表記されます。一般的な表記方法には以下のようなものがあります。

  • CKラベル: 属性名の横に「(CK)」を追加します。
  • 例: NationalIDNumber (CK)
  • 波線: 候補キーの属性を波線で囲みます。
  • 例: ~ NationalIDNumber ~

ER図の具体例

以下は、ER図におけるキーの表記方法を示す具体例です。

|----------------------|
|      Customers       |
|----------------------|
| CustomerID (PK)      |
| CustomerName         |
| ContactName          |
| NationalIDNumber (CK)|
|----------------------|

|----------------------|
|        Orders        |
|----------------------|
| OrderID (PK)         |
| OrderNumber          |
| CustomerID (FK)      |
|----------------------|

この例では、CustomersテーブルのCustomerIDが主キーとして下線で表記されており、OrdersテーブルのCustomerIDが外部キーとして表記されています。また、CustomersテーブルのNationalIDNumberが候補キーとして波線で表記されています。

演習問題とその解答例

理解を深めるために、ER図における主キー、外部キー、候補キーの識別方法に関する演習問題を提供します。これらの問題を解くことで、実際のデータベース設計におけるキーの役割と重要性をより深く理解できます。

演習問題1

次のエンティティを含むER図を考えてください。

Employees エンティティ

  • EmployeeID
  • EmployeeName
  • DepartmentID
  • NationalIDNumber

Departments エンティティ

  • DepartmentID
  • DepartmentName

次の質問に答えてください。

  1. Employeesエンティティの主キーは何ですか？
  2. Departmentsエンティティの主キーは何ですか？
  3. Employeesエンティティの外部キーは何ですか？
  4. Employeesエンティティの候補キーは何ですか？

解答例1

  1. Employeesエンティティの主キーはEmployeeIDです。
  2. Departmentsエンティティの主キーはDepartmentIDです。
  3. Employeesエンティティの外部キーはDepartmentIDです。
  4. Employeesエンティティの候補キーはNationalIDNumberです。

演習問題2

以下のSQLスクリプトを見て、各テーブルの主キー、外部キー、候補キーを特定してください。

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(100),
    StartDate DATE,
    EndDate DATE
);

CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    ProjectID INT,
    AssignmentDate DATE,
    PRIMARY KEY (EmployeeID, ProjectID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);

次の質問に答えてください。

  1. Projectsテーブルの主キーは何ですか？
  2. EmployeeProjectsテーブルの主キーは何ですか？
  3. EmployeeProjectsテーブルの外部キーは何ですか？

解答例2

  1. Projectsテーブルの主キーはProjectIDです。
  2. EmployeeProjectsテーブルの主キーは複合主キーで、EmployeeIDとProjectIDの組み合わせです。
  3. EmployeeProjectsテーブルの外部キーはEmployeeIDとProjectIDです。

まとめ

本記事では、SQLにおけるER図のキーについて、主キー、外部キー、候補キーの識別方法を詳しく解説しました。ER図はデータベース設計における重要なツールであり、各キーの適切な設定はデータの一意性と整合性を保つために不可欠です。主キーは各レコードを一意に識別し、外部キーはテーブル間のリレーションシップを確立し、候補キーは主キーとして選択可能なキーを示します。これらのキーの役割と識別方法を理解し、実際のデータベース設計に応用することで、効率的かつ信頼性の高いデータベースを構築できます。

SQL
SQL 外部キー 主キー ER図 候補キー

コメント

コメントする

目次