【超解説】PostgreSQLの複合キー：利点、欠点、代替方法まで

PostgreSQLにおける複合キーの作成方法

PostgreSQLでは、複数の列を組み合わせた複合キーと呼ばれる主キーを設定することができます。これは、1つの列だけでは一意に識別できない場合に役立ちます。

作成方法

複合キーを作成するには、以下のいずれかの方法を使用できます。

テーブル作成時

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  PRIMARY KEY (column1, column2)
);

上記の例では、table_nameという名前の表を作成し、column1とcolumn2という2つの列を複合主キーとして定義しています。

既存のテーブルに複合キーを追加

ALTER TABLE table_name
ADD CONSTRAINT primary_key PRIMARY KEY (column1, column2);

複合キーの利点

• 複数の列に基づいてレコードを効率的に検索できます。
• データの整合性を保ちやすくなります。
• 関連するデータ同士を容易に結びつけることができます。

• すべての列にインデックスを作成する必要があります。
• 複数の列で構成されるため、クエリの記述が複雑になる場合があります。

例

社員情報管理システムを例に考えてみましょう。社員情報を管理するテーブルには、社員ID、氏名、部署など複数の列が含まれます。この場合、社員IDのみを主キーとして定義すると、同姓同名の社員が複数存在する可能性があります。そこで、氏名と部署を追加して複合主キーとすると、社員をより確実に識別することができます。

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  department_id INT NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

上記の例では、employeesという名前の表を作成し、employee_id、last_name、first_name、department_idという4つの列を定義しています。employee_idは主キーとして定義し、last_nameとfirst_nameを追加して複合主キーとしています。また、department_idはdepartmentsテーブルのdepartment_id列を参照する外部キーとして定義しています。

複合キーは、PostgreSQLでデータを効率的に管理するために役立つ機能です。複数の列を組み合わせることで、データの一意性を保ち、関連するデータ同士を容易に結びつけることができます。

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  phone_number VARCHAR(20) NOT NULL,
  address VARCHAR(255) NOT NULL
);

上記の例では、customersという名前の表を作成し、以下の列を定義しています。

• customer_id: 自動採番される主キー
• last_name: 顧客の苗字
• email: 顧客の電子メールアドレス (一意)
• phone_number: 顧客の電話番号
• address: 顧客の住所

この場合、customer_idのみを主キーとして定義すると、同姓同名で電話番号や住所が同じ顧客が複数存在する可能性があります。そこで、last_nameとfirst_nameを追加して複合主キーとすると、顧客をより確実に識別することができます。

ALTER TABLE orders
ADD CONSTRAINT order_details_pk PRIMARY KEY (order_id, product_id);

この例では、ordersという表にすでにorder_idとproduct_idという列が存在するものと仮定しています。これらの列は、注文と製品をそれぞれ一意に識別するために使用されます。複合主キーを設定することで、1つの注文に複数の製品が含まれる場合でも、各製品を確実に識別することができます。

外部キーとの組み合わせ

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

• order_date: 注文日
• total_amount: 注文合計金額

この場合、customer_idのみを主キーとして定義すると、注文と顧客の関係を明確に示すことができません。そこで、customer_id列に外部キー制約を設定することで、orders表のcustomer_id列がcustomers表のcustomer_id列を参照することを示すことができます。

インデックスの作成

CREATE INDEX idx_customers_last_name ON customers (last_name);

上記の例では、customers表のlast_name列にインデックスを作成しています。インデックスを作成することで、last_name列に基づいてデータを効率的に検索することができます。

上記は、PostgreSQLにおける複合キーのサンプルコードです。これらのコードを参考に、それぞれの状況に合った複合キーを設定してください。

PostgreSQLにおける複合キーの代替方法

PostgreSQLでは、複合キー以外にも、複数の列に基づいてデータを識別する方法があります。以下に、いくつかの代替方法とその利点と欠点をご紹介します。

サロゲートキー

概要

サロゲートキーとは、人工的に生成された一意の識別子です。通常は、主キーとして使用されます。サロゲートキーは、複合キーよりも管理が簡単で、クエリの記述もシンプルになります。

利点

• 管理が簡単
• クエリの記述がシンプル

欠点

• データの意味を反映しない
• 追加の処理が必要

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  phone_number VARCHAR(20) NOT NULL,
  address VARCHAR(255) NOT NULL
);

上記の例では、customer_idというサロゲートキー列を追加し、これを主キーとして定義しています。last_nameとfirst_name列は、サロゲートキーを補完する情報として使用されます。

自然結合キー

自然結合キーとは、複数の列を組み合わせたもので、データの意味を反映した一意の識別子となるものです。自然結合キーは、複合キーよりも意味的に分かりやすく、クエリの記述も比較的シンプルになります。

• すべての列にインデックスが必要
• クエリの記述が複雑になる場合がある

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_details (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

上記の例では、orders表とorder_details表のorder_idとproduct_id列を自然結合キーとして使用しています。これらの列は、注文と製品をそれぞれ一意に識別し、関連するデータを結びつけることができます。

B-treeインデックス

B-treeインデックスは、複数の列に基づいてデータを効率的に検索するために使用できるインデックスの一種です。B-treeインデックスは、複合キーよりも管理が簡単で、クエリの記述もシンプルになります。

CREATE INDEX idx_customers_last_name_first_name ON customers (last_name, first_name);

上記は、PostgreSQLにおける複合キーの代替方法です。それぞれの方法には利点と欠点があるため、状況に応じて適切な方法を選択してください。