SQL Serverで外部キーを使って複数のテーブルを関連付ける方法

SQL Server で外部キーを使って複数のテーブルを関連付けるには、主に2つの方法があります。

1. 1つのテーブルに複数の外部キーを持つ
2. 中間テーブルを使用する

この方法は、1つのテーブルが他の複数のテーブルに関連する場合に使用します。例えば、注文テーブルと顧客テーブル、商品テーブルがあるとします。注文テーブルには、顧客テーブルと商品テーブルの両方への外部キーを持つことができます。

CREATE TABLE 注文 (
  注文ID INT PRIMARY KEY,
  顧客ID INT FOREIGN KEY REFERENCES 顧客(顧客ID),
  商品ID INT FOREIGN KEY REFERENCES 商品(商品ID),
  ...
);

この方法の利点は、シンプルで分かりやすいことです。しかし、テーブル構造が複雑になる場合があり、データの更新や削除が困難になる可能性があります。

CREATE TABLE 顧客 (
  顧客ID INT PRIMARY KEY,
  ...
);

CREATE TABLE 商品 (
  商品ID INT PRIMARY KEY,
  ...
);

CREATE TABLE 注文 (
  注文ID INT PRIMARY KEY,
  顧客ID INT,
  商品ID INT,
  ...
);

CREATE TABLE 注文詳細 (
  注文ID INT,
  商品ID INT,
  数量 INT,
  ...
);

中間テーブルには、注文テーブルと顧客テーブル、商品テーブルの主キーを組み合わせた複合主キーを設定します。

この方法の利点は、テーブル構造がシンプルになり、データの更新や削除が容易になることです。しかし、テーブル数が増えてしまうというデメリットがあります。

どちらの方法を選ぶべきかは、テーブル構造やデータの更新頻度などを考慮する必要があります。

1つのテーブルに複数の外部キーを持つ

CREATE TABLE 注文 (
  注文ID INT PRIMARY KEY,
  顧客ID INT FOREIGN KEY REFERENCES 顧客(顧客ID),
  商品ID INT FOREIGN KEY REFERENCES 商品(商品ID),
  ...
);

CREATE TABLE 顧客 (
  顧客ID INT PRIMARY KEY,
  ...
);

CREATE TABLE 商品 (
  商品ID INT PRIMARY KEY,
  ...
);

INSERT INTO 顧客 (顧客ID, ...) VALUES (1, ...), (2, ...), (3, ...);
INSERT INTO 商品 (商品ID, ...) VALUES (1, ...), (2, ...), (3, ...);

INSERT INTO 注文 (注文ID, 顧客ID, 商品ID, ...) VALUES (1, 1, 1, ...), (2, 2, 2, ...), (3, 3, 3, ...);

-- 顧客ID 1 の注文を取得
SELECT * FROM 注文 WHERE 顧客ID = 1;

-- 商品ID 2 の注文を取得
SELECT * FROM 注文 WHERE 商品ID = 2;

中間テーブルを使用する

CREATE TABLE 顧客 (
  顧客ID INT PRIMARY KEY,
  ...
);

CREATE TABLE 商品 (
  商品ID INT PRIMARY KEY,
  ...
);

CREATE TABLE 注文 (
  注文ID INT PRIMARY KEY,
  顧客ID INT,
  商品ID INT,
  ...
);

CREATE TABLE 注文詳細 (
  注文ID INT,
  商品ID INT,
  数量 INT,
  ...
);

INSERT INTO 顧客 (顧客ID, ...) VALUES (1, ...), (2, ...), (3, ...);
INSERT INTO 商品 (商品ID, ...) VALUES (1, ...), (2, ...), (3, ...);

INSERT INTO 注文 (注文ID, 顧客ID, 商品ID, ...) VALUES (1, 1, 1, ...), (2, 2, 2, ...), (3, 3, 3, ...);
INSERT INTO 注文詳細 (注文ID, 商品ID, 数量, ...) VALUES (1, 1, 1), (1, 2, 2), (2, 2, 3), ...;

-- 顧客ID 1 の注文を取得
SELECT * FROM 注文 o
INNER JOIN 注文詳細 od ON o.注文ID = od.注文ID
WHERE o.顧客ID = 1;

-- 商品ID 2 の注文を取得
SELECT * FROM 注文 o
INNER JOIN 注文詳細 od ON o.注文ID = od.注文ID
WHERE od.商品ID = 2;

外部キー以外で複数のテーブルを関連付ける方法

結合 (JOIN)

結合を使用すると、複数のテーブルからデータをまとめて取得することができます。

SELECT *
FROM 顧客 c
INNER JOIN 注文 o ON c.顧客ID = o.顧客ID;

この例では、顧客テーブルと注文テーブルを結合し、顧客と注文情報をまとめて取得しています。

ビュー (VIEW)

ビューは、複数のテーブルを結合した結果を仮想的なテーブルとして表示するものです。

CREATE VIEW 顧客注文 AS
SELECT *
FROM 顧客 c
INNER JOIN 注文 o ON c.顧客ID = o.顧客ID;

SELECT *
FROM 顧客注文;

ストアドプロシージャ (STORED PROCEDURE)

ストアドプロシージャは、SQL Server に保存されたプログラムです。複数のテーブルを操作する複雑な処理を、ストアドプロシージャとしてまとめることができます。

CREATE PROCEDURE 顧客注文取得
AS
BEGIN
  SELECT *
  FROM 顧客 c
  INNER JOIN 注文 o ON c.顧客ID = o.顧客ID;
END;

EXEC 顧客注文取得;

• 外部キー: シンプルで分かりやすい方法
• 結合: 柔軟なデータ取得が可能
• ビュー: 複雑な結合を簡潔に記述できる
• ストアドプロシージャ: 複雑な処理をまとめられる