主キーと外部キー： データの整合性とクエリのパフォーマンスを向上させる
プライマリキーと外部キー結合: なぜ必要なのか？
一見、結合に必要な情報はテーブル間で共有されているため、プライマリキーと外部キー関係なしで結合できると思えます。しかし、実際には、これらの関係は以下のような重要な役割を果たします。
データの整合性
プライマリキーと外部キー関係は、データの整合性を保証する重要な役割を果たします。
- プライマリキーは、各行を一意に識別する値です。重複するプライマリキーは、データの矛盾や誤解を招く可能性があります。
- 外部キーは、別のテーブルのプライマリキーを参照する値です。外部キー制約により、参照先のテーブルに存在しない値が外部キーとして挿入されるのを防ぎ、データの整合性を保ちます。
例:
- 顧客テーブル: 顧客ID (プライマリキー)、名前、住所
- 注文テーブル: 注文ID (プライマリキー)、顧客ID (外部キー)、商品ID
この例では、顧客IDは顧客テーブルのプライマリキーであり、注文テーブルの外部キーとして使用されます。外部キー制約により、注文テーブルに存在しない顧客IDが挿入されるのを防ぎ、注文が常に有効な顧客に関連付けられていることを保証します。
データの効率的な検索
プライマリキーと外部キー関係は、データの効率的な検索を可能にします。
- インデックスは、プライマリキーと外部キーに基づいて作成されます。インデックスは、クエリのパフォーマンスを大幅に向上させるための高速なデータ検索構造です。
- 結合操作は、プライマリキーと外部キーを使用して、関連するテーブル間のデータを効率的に結合します。
- 顧客の名前と注文した商品をすべて表示するクエリ
このクエリは、顧客テーブルと注文テーブルを顧客IDで結合して実行できます。プライマリキーと外部キー関係により、インデックスを使用して迅速に結合を実行し、必要なデータを効率的に取得することができます。
データの更新と削除
- 参照整合性は、外部キー制約によって保証されます。参照整合性により、親レコードの削除時に子レコードが自動的に削除されるか、または無効化されます。
- カスケード更新と削除は、プライマリキーと外部キー関係に基づいて設定できます。カスケード更新/削除により、親レコードの更新/削除時に子レコードも自動的に更新/削除されます。
- 顧客を削除すると、その顧客に関連するすべての注文も自動的に削除されます。
この例では、顧客IDがプライマリキーであり、注文テーブルの外部キーとして使用されます。参照整合性により、顧客を削除すると、その顧客に関連するすべての注文も自動的に削除されます。
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE
);
注文テーブル
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
product_id INT NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
データ挿入
INSERT INTO customers (name, email)
VALUES ('山田 太郎', '[email protected]');
INSERT INTO orders (customer_id, product_id, order_date)
VALUES (1, 123, '2024-06-04');
結合クエリ
SELECT customers.name, orders.order_date
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
結果
| name | order_date |
|-----------|------------|
| 山田 太郎 | 2024-06-04 |
プライマリキーと外部キー以外の結合方法
自然結合
自然結合は、共通の列に基づいてテーブルを結合する方法です。共通列は、プライマリキーである必要はありません。
SELECT customers.name, orders.order_date
FROM customers
NATURAL JOIN orders
ON customers.customer_id = orders.customer_id;
明示的な結合
明示的な結合は、JOIN キーワードを使用して、結合する列を明示的に指定する方法です。
SELECT customers.name, orders.order_date
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
サブクエリ
サブクエリは、クエリ内で別のクエリを実行する方法です。サブクエリを使用して、結合条件をより複雑にすることができます。
SELECT customers.name, orders.order_date
FROM customers
WHERE customers.customer_id IN (
SELECT customer_id
FROM orders
WHERE product_id = 123
);
結合演算子
結合演算子は、複数のテーブルを結合する方法です。
- INNER JOIN: 共通の列を持つ行のみを返します。
- LEFT JOIN: 左側のテーブルのすべての行と、右側のテーブルに一致する行を返します。
- FULL JOIN: 左側のテーブルと右側のテーブルのすべての行を返します。
SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
結合ビュー
結合ビューは、複数のテーブルを結合した結果を永続的に保存するオブジェクトです。結合ビューを使用すると、複雑な結合を簡単にクエリすることができます。
CREATE VIEW customer_orders AS
SELECT customers.name, orders.order_date
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
SELECT * FROM customer_orders;
sql database
