データベース設計の罠を回避せよ！複合主キーの定義で陥りがちな5つの落とし穴
SQLにおける複合主キーの定義方法
- 複数の列に基づいてレコードを効率的に検索できます。
- データの整合性を保ちやすくなります。
- 複数の列でレコードを結合する場合に役立ちます。
複合主キーを定義するには、CREATE TABLE ステートメントの PRIMARY KEY 句を使用します。構文は以下の通りです。
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
...,
PRIMARY KEY (column1, column2, ...)
);
例
社員テーブル (employees) を作成し、社員ID (employee_id) と部署ID (department_id) を複合主キーとして定義する例は以下の通りです。
CREATE TABLE employees (
employee_id INT,
department_id INT,
name VARCHAR(255),
salary DECIMAL(10,2),
PRIMARY KEY (employee_id, department_id)
);
この例では、employee_id と department_id の組み合わせがそれぞれ一意である必要があります。つまり、同じ部署内に同じ社員IDを持つ社員が存在することはできません。
複合主キーの注意点
- 複合主キーの列は、NULL値であってはいけません。
- 複合主キーの列は、インデックスが張られている必要があります。
CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, customer_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
このテーブルには、以下の列があります。
order_id: 注文ID (主キーの一部)order_date: 注文日total_amount: 注文合計金額
order_id と customer_id の組み合わせが複合主キーとなり、各注文が一意に識別されます。また、customer_id 列は customers テーブルの customer_id 列を参照する外部キーでもあります。
このテーブルを使用すると、特定の顧客の注文を検索したり、特定の日付の注文を一覧表示したりすることができます。
追加の例
- 商品テーブル (products): 商品ID (product_id) と商品コード (product_code) を複合主キーとして定義できます。
- 学生成績テーブル (student_grades): 学生ID (student_id) と科目ID (course_id) を複合主キーとして定義できます。
- 住所録テーブル (addresses): 郵便番号 (postal_code) と住所 (address) を複合主キーとして定義できます。
これらの例は、複合主キーがさまざまなデータモデルで使用できることを示しています。複合主キーを適切に使用することで、データベースの整合性と効率性を向上させることができます。
SQLで複合主キーを定義するその他の方法
サロゲートキーを用いた方法
サロゲートキーとは、テーブル内に生成されるユニークな識別子列です。この列を主キーとして定義し、別途インデックスを使って自然キーとの関連性を保つという方法です。
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
UNIQUE KEY customer_order (customer_id, order_date)
);
この例では、order_id 列がサロゲートキーとして主キーに定義されています。customer_id と order_date 列の組み合わせにユニークインデックスを設定することで、これらの列と order_id 列との関連性を保っています。
利点
- サロゲートキーは、常にユニークな値を生成するため、主キーとして設定するのに適しています。
- 複合主キーよりもシンプルで分かりやすいデータモデルになります。
欠点
- サロゲートキーには、ビジネス上の意味がありません。
- 別途インデックスを作成する必要があるため、テーブル構造が複雑になります。
部分主キーとは、個別に主キーとして機能する複数の列を定義する方法です。この方法では、各列に主キー制約を定義し、論理的なAND演算で組み合わせることで、複合主キーとしての役割を果たします。
CREATE TABLE orders (
customer_id INT NOT NULL PRIMARY KEY,
order_date DATE NOT NULL PRIMARY KEY
);
この例では、customer_id 列と order_date 列がそれぞれ主キーとして定義されています。これらの列の組み合わせが、複合主キーとしての役割を果たします。
- 各列が個別に主キーとして機能するため、柔軟性があります。
- サロゲートキーを使用する必要がありません。
- 複数の主キー制約を定義する必要があるため、データモデルが冗長になります。
- インデックスの最適化が複雑になる可能性があります。
どの方法を選択するかは、テーブルの設計とデータの特性によって異なります。以下、それぞれの方法を選択する際の指針をご紹介します。
- 従来の PRIMARY KEY 句を用いた方法: シンプルで分かりやすいデータモデルに適しています。
- サロゲートキーを用いた方法: 常にユニークな主キーが必要で、データモデルをシンプルにしたい場合に適しています。
- 部分主キーを用いた方法: 柔軟性のあるデータモデルが必要で、サロゲートキーを使用したくない場合に適しています。
複合主キーの定義方法について、より詳しく理解したい場合は、以下のリソースを参照することをお勧めします。
sql primary-key composite-primary-key
