はじめに
この実験（Lab）では、PostgreSQL におけるリレーションシップと結合（join）について探求します。データの整合性を確保するために、外部キー制約を持つテーブルを作成する方法を学びます。
まず、customersテーブルとordersテーブルの 2 つのテーブルを作成し、それらの間に外部キーリレーションシップを確立します。次に、定義されたリレーションシップにデータが準拠するように、これらのテーブルにデータを挿入します。最後に、INNER JOINを使用してデータを取得する方法を学び、LEFT、RIGHT、およびFULL OUTER JOIN操作の結果を比較して、それらが異なるデータリレーションシップをどのように処理するかを理解します。
外部キー制約を持つテーブルの作成
このステップでは、customersテーブルとordersテーブルの 2 つのテーブルを作成し、それらの間に外部キー制約を確立します。この制約により、テーブル間のリレーションシップが維持され、無効なデータが入力されるのを防ぎます。
外部キーについて
外部キーとは、あるテーブルのカラムであり、別のテーブルの主キーを参照するものです。これは、2 つのテーブル間のリンクを確立します。外部キーを含むテーブルは「子」テーブルと呼ばれ、主キーを含むテーブルは「親」テーブルと呼ばれます。
ステップ 1: PostgreSQL への接続
LabEx VM でターミナルを開きます。psqlコマンドを使用して PostgreSQL データベースに接続します。
sudo -u postgres psql
PostgreSQL のプロンプト（postgres=#）が表示されるはずです。
ステップ 2: customersテーブルの作成
次のカラムを持つcustomersテーブルを作成します。
customer_id: 各顧客の一意の識別子（主キー）。first_name: 顧客のファーストネーム。last_name: 顧客のラストネーム。email: 顧客のメールアドレス（一意である必要があります）。
psqlシェルで次の SQL コマンドを実行します。
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);
このコマンドはcustomersテーブルを作成します。SERIALキーワードは、customer_idの数値シーケンスを自動的に生成し、自動インクリメントにします。PRIMARY KEYはcustomer_idを主キーとして指定します。NOT NULLはfirst_nameとlast_nameカラムを空にできないようにし、UNIQUEは各メールアドレスが一意であることを保証します。
ステップ 3: 外部キーを持つordersテーブルの作成
次のカラムを持つordersテーブルを作成します。
order_id: 各注文の一意の識別子（主キー）。customer_id: 注文した顧客の ID（customersを参照する外部キー）。order_date: 注文日。total_amount: 注文の合計金額。
psqlシェルで次の SQL コマンドを実行します。
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
このコマンドはordersテーブルを作成します。customer_idカラムは、REFERENCESキーワードを使用して外部キーとして定義されます。REFERENCES customers(customer_id)は、ordersテーブルのcustomer_idカラムがcustomersテーブルのcustomer_idカラムを参照することを指定します。これにより、外部キーリレーションシップが確立されます。
ステップ 4: テーブル作成の確認
データベース内のテーブルを一覧表示して、テーブルが正常に作成されたことを確認します。psqlシェルで次のコマンドを実行します。
\dt
customersテーブルとordersテーブルが表示されるはずです。
ステップ 5: テーブルの説明
テーブルの構造を表示するには、\dコマンドに続けてテーブル名を使用します。たとえば、customersテーブルを記述するには、次を実行します。
\d customers
これにより、customersテーブルに定義されているカラム、データ型、および制約が表示されます。同様に、ordersテーブルを記述できます。
\d orders
これにより、customer_idカラムの外部キー制約が表示されます。
これで、外部キー制約を持つ 2 つのテーブルが正常に作成されました。
データの挿入と参照整合性の実施
このステップでは、customersテーブルとordersテーブルにデータを挿入し、参照整合性が維持されるようにします。これは、customersテーブルに存在しない顧客の注文を追加できないことを意味します。
参照整合性について
参照整合性（Referential Integrity）は、テーブル間のリレーションシップが一貫性を保つことを保証します。この場合、ordersテーブルのcustomer_idはcustomersテーブルに存在する必要があります。
ステップ 1: customersテーブルへのデータの挿入
psqlシェルで次の SQL コマンドを使用して、customersテーブルにデータを挿入します。
INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com'),
('David', 'Lee', 'david.lee@example.com');
このコマンドは、customersテーブルに 3 人の顧客を追加します。customer_idは自動的に生成されます。
ステップ 2: customersテーブルへのデータ挿入の確認
customersテーブルをクエリして、データが正しく挿入されたことを確認します。
SELECT * FROM customers;
挿入した 3 人の顧客と、自動的に生成されたcustomer_idの値が表示されるはずです。出力は次のようになります。
customer_id | first_name | last_name | email
-------------+------------+-----------+------------------------
1 | John | Doe | john.doe@example.com
2 | Jane | Smith | jane.smith@example.com
3 | David | Lee | david.lee@example.com
(3 rows)
ステップ 3: ordersテーブルへのデータの挿入
customersテーブルからcustomer_idの値を参照して、ordersテーブルにデータを挿入します。
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-11-01', 100.00),
(2, '2023-11-05', 250.50),
(1, '2023-11-10', 75.25),
(3, '2023-11-15', 120.00);
このコマンドは、ordersテーブルに 4 つの注文を追加します。各注文は、customersテーブルのcustomer_idに関連付けられています。
ステップ 4: ordersテーブルへのデータ挿入の確認
ordersテーブルをクエリして、データが正しく挿入されたことを確認します。
SELECT * FROM orders;
挿入した 4 つの注文が表示されるはずです。出力は次のようになります。
order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
1 | 1 | 2023-11-01 | 100.00
2 | 2 | 2023-11-05 | 250.50
3 | 1 | 2023-11-10 | 75.25
4 | 3 | 2023-11-15 | 120.00
(4 rows)
ステップ 5: 無効なデータの挿入の試行（参照整合性のデモンストレーション）
参照整合性制約を示すために、customersテーブルに存在しないcustomer_idを持つ注文を挿入してみます。
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);
次のようなエラーメッセージが表示されるはずです。
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(4) is not present in table "customers".
このエラーメッセージは、外部キー制約が機能していることを確認します。データベースは、customer_id 4 がcustomersテーブルに存在しないため、注文の挿入を防止します。
これで、参照整合性を確保しながら、customersテーブルとordersテーブルにデータを正常に挿入できました。
INNER JOIN を使用したデータのクエリ
このステップでは、PostgreSQL でINNER JOIN句を使用して、複数のテーブルからデータを取得する方法を学びます。INNER JOINは、関連するカラムに基づいて、2 つ以上のテーブルから行を結合します。
INNER JOIN について
INNER JOINは、結合される両方のテーブルに一致がある行のみを返します。一致がない場合、その行は結果から除外されます。
ステップ 1: PostgreSQL への接続
psqlコマンドを使用して PostgreSQL データベースに接続していることを確認してください。
sudo -u postgres psql
ステップ 2: INNER JOIN クエリの実行
psqlシェルで次の SQL クエリを実行します。
SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
このクエリを分解してみましょう。
SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount: これは、ordersテーブルとcustomersテーブルから取得するカラムを指定します。テーブル名をプレフィックスとして使用する（例：orders.order_id）ことで、各カラムがどのテーブルから来ているかを明確にします。FROM orders: これは、クエリを実行する最初のテーブルを指定します。INNER JOIN customers ON orders.customer_id = customers.customer_id: これは、結合する 2 番目のテーブル（customers）と結合条件（orders.customer_id = customers.customer_id）を指定します。ON句は、行を含めるために、ordersテーブルのcustomer_idがcustomersテーブルのcustomer_idと一致する必要があることを指定します。
ステップ 3: 結果の分析
クエリは、各注文の注文 ID、顧客のファーストネーム、注文日、および合計金額を含む結果セットを返します。出力は次のようになります。
order_id | first_name | order_date | total_amount
----------+------------+------------+--------------
1 | John | 2023-11-01 | 100.00
3 | John | 2023-11-10 | 75.25
2 | Jane | 2023-11-05 | 250.50
4 | David | 2023-11-15 | 120.00
(4 rows)
クエリは、customer_idに基づいてordersテーブルとcustomersテーブルを正常に結合し、要求された情報を取得しました。customersテーブルに対応する顧客がいる注文のみが含まれます。
ステップ 4: エイリアスの使用（オプション）
より複雑なクエリの場合、エイリアスを使用してクエリを読みやすくすることができます。前のクエリは、エイリアスを使用して書き換えることができます。
SELECT o.order_id, c.first_name, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
このクエリでは、oはordersのエイリアスであり、cはcustomersのエイリアスです。結果は同じになりますが、クエリはより簡潔になります。
これで、INNER JOIN句を使用して複数のテーブルからデータを正常にクエリできました。
LEFT、RIGHT、および FULL OUTER JOIN の結果の比較
このステップでは、PostgreSQL でLEFT OUTER JOIN、RIGHT OUTER JOIN、およびFULL OUTER JOINの結果を調査し、比較します。これらの結合は、他のテーブルに一致する値がない場合でも、一方または両方のテーブルからすべての行を取得します。
OUTER JOIN について
- LEFT OUTER JOIN（または LEFT JOIN）: 左側のテーブルからすべての行と、右側のテーブルから一致する行を返します。右側のテーブルに一致がない場合、右側のテーブルのカラムには
NULL値が返されます。 - RIGHT OUTER JOIN（または RIGHT JOIN）: 右側のテーブルからすべての行と、左側のテーブルから一致する行を返します。左側のテーブルに一致がない場合、左側のテーブルのカラムには
NULL値が返されます。 - FULL OUTER JOIN（または FULL JOIN）: 両方のテーブルからすべての行を返します。一方のテーブルに一致がない場合、他方のテーブルのカラムには
NULL値が返されます。
ステップ 1: PostgreSQL への接続
psqlコマンドを使用して PostgreSQL データベースに接続していることを確認してください。
sudo -u postgres psql
ステップ 2: 注文のない新しい顧客の挿入
注文をしていない新しい顧客をcustomersテーブルに挿入します。
INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');
ステップ 3: 新しい顧客の確認
新しい顧客がcustomersテーブルに追加されたことを確認します。
SELECT * FROM customers;
アリス・ブラウンが結果にリストされ、新しいcustomer_id（おそらく 4）が表示されるはずです。
ステップ 4: LEFT OUTER JOIN の実行
次の SQL クエリを実行して、customersテーブルとordersテーブルの間でLEFT OUTER JOINを実行します。
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;
このクエリは、すべての顧客と、顧客が行った注文を返します。顧客が注文をしていない場合、order_idカラムとorder_dateカラムにはNULL値が含まれます。出力は次のようになります。
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
John | 3 | 2023-11-10
Jane | 2 | 2023-11-05
David | 4 | 2023-11-15
Alice | |
(5 rows)
アリス・ブラウンは、注文をしていないにもかかわらず含まれていることに注意してください。彼女のorder_idカラムとorder_dateカラムはNULLです。
ステップ 5: RIGHT OUTER JOIN の実行
次の SQL クエリを実行して、customersテーブルとordersテーブルの間でRIGHT OUTER JOINを実行します。
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id;
このクエリは、すべての注文と、各注文を行った顧客のファーストネームを返します。すべての注文に対応する顧客がいるため、この場合、結果はINNER JOINと同じになります。出力は次のようになります。
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
Jane | 2 | 2023-11-05
John | 3 | 2023-11-10
David | 4 | 2023-11-15
(4 rows)
ステップ 6: FULL OUTER JOIN の実行
次の SQL クエリを実行して、customersテーブルとordersテーブルの間でFULL OUTER JOINを実行します。
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
このクエリは、すべての顧客とすべての注文を返します。顧客が注文をしていない場合、order_idカラムとorder_dateカラムにはNULL値が含まれます。注文に対応する顧客がいない場合（外部キー制約により、現在の設定では不可能）、first_nameカラムにはNULL値が含まれます。出力は次のようになります。
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
John | 3 | 2023-11-10
Jane | 2 | 2023-11-05
David | 4 | 2023-11-15
Alice | |
(5 rows)
アリス・ブラウンがorder_idとorder_dateにNULL値を含めて含まれていることに注意してください。
ステップ 7: 違いの理解
LEFT OUTER JOINは、一致する注文がない場合でも、customersテーブルからすべての行を含めます。RIGHT OUTER JOINは、ordersテーブルからすべての行を含めます。この場合、すべての注文に対応する顧客がいるため、INNER JOINのように動作します。FULL OUTER JOINは、両方のテーブルからすべての行を含めます。
これで、PostgreSQL でLEFT OUTER JOIN、RIGHT OUTER JOIN、およびFULL OUTER JOINの結果を調査し、比較しました。
まとめ
この実験（Lab）では、データ整合性を維持するために、PostgreSQL で外部キー制約を持つテーブルを作成する方法を学びました。customersテーブルとordersテーブルを作成し、外部キーを使用してそれらの間の関係を確立しました。次に、これらのテーブルにデータを挿入し、外部キー制約が適用されるようにしました。最後に、関連テーブルからデータを取得し、さまざまなデータ関係をどのように処理するかを理解するために、さまざまなタイプの JOIN 操作（INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、およびFULL OUTER JOIN）を調査しました。
