SQL外部キー制約

概要: このチュートリアルでは、SQL外部キーと、テーブル間のリレーションシップを強制するための外部キー制約の作成方法について学習します。

SQL外部キー制約の概要

外部キーとは、2つのテーブルのデータ間のリンクを強制する1つまたは複数の列のことです。外部キー参照では、最初のテーブルの主キー列（または列のグループ）が、2番目のテーブルの列（または列のグループ）によって参照されます。2番目のテーブルの列（または列のグループ）が外部キーになります。

テーブルを作成または変更する際に、FOREIGN KEY制約を使用して外部キーを作成します。理解を深めるために、簡単な例を見てみましょう。

SQL外部キー制約の例

次のprojectsテーブルとproject_assignmentsテーブルを見てください。

CREATE TABLE projects (
    project_id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(255),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

CREATE TABLE project_milestones(
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

各プロジェクトには0個以上のマイルストーンがあり、1つのマイルストーンは1つのみのプロジェクトに属している必要があります。これらのテーブルを使用するアプリケーションは、project_milestonesテーブルの各行に対して、projectsテーブルに対応する行が存在することを保証する必要があります。言い換えれば、マイルストーンはプロジェクトなしでは存在できません。

残念ながら、ユーザーはクライアントツールを使用してデータベースを編集したり、アプリケーションにバグがある場合、projectsテーブルのどの行にも対応しない行がproject_milestonesテーブルに追加される可能性があります。または、ユーザーがprojectsテーブルの行を削除すると、project_milestonesテーブルに孤立した行が残る可能性があります。これにより、アプリケーションが正常に動作しなくなります。

解決策は、projectsテーブルとproject_milestonesテーブル間の関係を強制するために、SQL外部キー制約をproject_milestonesテーブルに追加することです。

テーブルを作成する際に、次のようにFOREIGN KEY制約を作成できます。

CREATE TABLE project_milestones (
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100),
    FOREIGN KEY (project_id)
        REFERENCES projects (project_id)
);Code language: SQL (Structured Query Language) (sql)

FOREIGN KEY句は、project_milestonesテーブルのproject_idを、projectsテーブルのproject_idを参照する外部キーにします。

FOREIGN KEY (project_id)
        REFERENCES projects (project_id)Code language: SQL (Structured Query Language) (sql)

次のように、FOREIGN KEY制約に名前を付けることができます。

CREATE TABLE project_milestones (
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100),
    CONSTRAINT fk_project FOREIGN KEY (project_id)
        REFERENCES projects (project_id)
);Code language: SQL (Structured Query Language) (sql)

fk_projectは、FOREIGN KEY制約の名前です。

既存のテーブルへのFOREIGN KEY制約の追加

既存のテーブルにFOREIGN KEY制約を追加するには、ALTER TABLEステートメントを使用します。

ALTER TABLE table_1
ADD CONSTRAINT fk_name FOREIGN KEY (fk_key_column)
   REFERENCES table_2(pk_key_column)Code language: SQL (Structured Query Language) (sql)

project_milestonesが事前に定義された外部キーなしで既に存在し、project_id列にFOREIGN KEY制約を定義するとします。これを行うには、次のALTER TABLEステートメントを使用します。

ALTER TABLE project_milestones
ADD CONSTRAINT fk_project FOREIGN KEY(project_id)
   REFERENCES projects(project_id);Code language: SQL (Structured Query Language) (sql)

外部キー制約の削除

外部キー制約を削除するには、次のようにALTER TABLEステートメントも使用します。

ALTER TABLE table_name
DROP CONSTRAINT fk_name;Code language: SQL (Structured Query Language) (sql)

MySQLを使用している場合は、次のようによりクリーンな構文を使用できます。

ALTER TABLE table_name
DROP FOREIGN KEY fk_name;Code language: SQL (Structured Query Language) (sql)

たとえば、fk_project外部キー制約を削除するには、次のステートメントを使用します。

ALTER TABLE project_milestones
DROP CONSTRAINT fk_project;Code language: SQL (Structured Query Language) (sql)

このチュートリアルでは、外部キーの概念を紹介し、SQL FOREIGN KEY制約を使用して外部キーを作成する方法を示しました。