SQL Serverで学ぶFOREIGN KEY制約：外部キーでデータの整合性を確保する方法

SQL ServerのFOREIGN KEY制約（外部キー）は、異なるテーブル間のリレーションを構築し、データの整合性を保つための重要なツールです。

本記事では、外部キーの定義方法や「ON DELETE CASCADE」などのオプション設定、外部キー制約の削除について解説します。また、学んだ内容を実践するための演習問題とその解答例も紹介します。

FOREIGN KEY制約とは？

FOREIGN KEY制約は、あるテーブル（子テーブル）の列が、別のテーブル（親テーブル）の特定の列を参照することを保証します。

この仕組みにより、テーブル間のリレーションが構築され、データの整合性が保たれます。

外部キーの特徴

• 親テーブルの主キーまたは一意制約を持つ列を参照します。
• 子テーブルに無効なデータ（親テーブルに存在しない値）が挿入されるのを防ぎます。
• リレーションを維持するための動作をカスタマイズ可能（例：ON DELETE CASCADE）。

FOREIGN KEYの定義方法

SQL ServerでFOREIGN KEY制約を定義するには、CREATE TABLEまたはALTER TABLEステートメントを使用します。

CREATE TABLEでの定義

以下は、Ordersテーブル（子テーブル）がCustomersテーブル（親テーブル）のCustomerID列を参照する外部キー制約を定義する例です。

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

• FOREIGN KEY (CustomerID)で外部キーを定義。
• REFERENCES Customers(CustomerID)で親テーブルの列を指定。

ON DELETE CASCADEの使用

外部キー制約にON DELETE CASCADEを追加すると、親テーブルのデータが削除された場合、関連する子テーブルのデータも自動的に削除されます。

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);

動作例

1. CustomersテーブルからあるCustomerIDを削除。
2. Ordersテーブル内で、そのCustomerIDに関連する行も自動的に削除される。

外部キー制約の追加

既存のテーブルの列に対して外部キーを設定するには、ALTER TABLE を使用します。以下のようなSQLクエリを実行すれば、外部キー制約を追加できます。

基本構文

ALTER TABLE 子テーブル名
ADD CONSTRAINT 外部キー名
FOREIGN KEY (子テーブルの列名)
REFERENCES 親テーブル名 (親テーブルの列名);

具体例

例えば、以下のような2つのテーブルがあるとします。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT -- 外部キーを後から設定
);

この場合、orders.user_id を users.id に対する外部キーにするには、次のクエリを実行します。

ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
REFERENCES users(id);

オプション

ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE  -- 親テーブルのデータ削除時に子テーブルも削除
ON UPDATE CASCADE; -- 親テーブルのデータ更新時に子テーブルも更新

外部キー制約の削除

一度定義した外部キー制約を削除する場合は、ALTER TABLEとDROP CONSTRAINTを使用します。

制約の削除方法

外部キー制約の名前を確認し、削除します。

-- 制約名を確認
EXEC sp_fkeys @pktable_name = 'Customers';

-- 制約を削除
ALTER TABLE Orders DROP CONSTRAINT FK_CustomerID;

演習問題と解答例

学んだ内容を深く理解するために、以下の演習問題に挑戦してください。

演習問題

以下の構造を持つテーブルを作成しなさい。

ON DELETE CASCADEを利用して、Categoriesの行が削除された場合、Productsテーブル内の関連する行が削除されるように設定してください。

以下のSQLを実行し、期待される結果を考えなさい。

INSERT INTO Categories (CategoryID, CategoryName) VALUES (1, 'Beverages');
INSERT INTO Products (ProductID, ProductName, CategoryID) VALUES (101, 'Tea', 1);
DELETE FROM Categories WHERE CategoryID = 1;
SELECT * FROM Products;

解答例

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName NVARCHAR(100) NOT NULL
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) ON DELETE CASCADE
);

SELECT * FROM Products;
-- 結果なし (全て削除される)

まとめ

本記事では、SQL ServerにおけるFOREIGN KEY制約について以下のポイントを解説しました。

• 外部キー制約の基本概念
• ON DELETE CASCADEを用いた動作のカスタマイズ
• 外部キー制約の削除方法

外部キーを正しく活用することで、リレーショナルデータベースにおけるデータの整合性を確保できます。実際のシナリオでも活用してみてください！