SQL Server データベースの効率化：複合主キーと追加「ID」列によるパフォーマンス向上

2024-07-03

SQL Server における複合主キーと追加の「ID」列：詳細な比較

SQL Server において、テーブルデータを一意に識別するために主キーを定義することは重要です。主キーには単一の列を使用することも、複数の列を組み合わせた複合主キーを使用することもできます。一方、テーブルに「ID」列を追加するかどうかは、オプションです。

このガイドでは、複合主キーと追加の「ID」列のそれぞれの利点と欠点について詳しく比較検討し、それぞれの状況に適したオプションを選択するためのガイダンスを提供します。

複合主キーは、複数の列の値を組み合わせることで、レコードを一意に識別するキーです。この方法の利点は次のとおりです。

データ整合性の向上: 複数の列を組み合わせることで、個々の列よりもはるかに強力な一意制約を確立できます。これは、重複データの挿入を防ぎ、データの整合性を保証するのに役立ちます。
関連データの容易な参照: 複合主キーの列は、自然に関連するデータをグループ化するために使用できます。たとえば、顧客ID と 注文ID の列を組み合わせた複合主キーを使用すると、特定の顧客に関連するすべての注文を簡単に参照できます。
検索パフォーマンスの向上: 特定のレコードを効率的に検索するために、複合主キーの列をインデックス化することができます。

一方、複合主キーにはいくつかの欠点もあります。

複雑さの増加: 複合主キーは、単一の列の主キーよりも複雑で理解しにくいかもしれません。
更新の複雑さ: 複合主キーの一部を変更するには、関連するすべてのレコードを更新する必要があります。

追加の「ID」列

テーブルに「ID」列を追加することは、レコードを一意に識別するもう 1 つの方法です。「ID」列は通常、自動的に増分される整数値であり、プライマリキーとして使用できます。この方法の利点は次のとおりです。

単純さ: 追加の「ID」列は、複合主キーよりもシンプルで理解しやすいものです。
更新の容易さ: 「ID」列の値を変更しても、他のレコードに影響はありません。

データ冗長性の可能性: すでにテーブル内にレコードを識別するための十分な情報が含まれている場合、「ID」列を追加するとデータ冗長が発生する可能性があります。
照合の複雑さ: 複数のテーブル間のレコードを照合するには、「ID」列ではなく、関連する列を使用する必要があります。

複合主キーと追加の「ID」列のいずれを選択するかは、特定の状況によって異なります。

複数の列に基づいて強力な一意制約が必要で、関連データの参照が容易な場合は、複合主キーが適しています。
シンプルで使いやすいソリューションが必要で、データ冗長性を許容できる場合は、追加の「ID」列が適しています。

推奨事項

データベース設計のベストプラクティスとして、必要な場合は複合主キーを使用し、不要な場合は使用しないことをお勧めします。
複合主キーを使用する場合は、自然に関連する列を選択するようにしてください。
データベースに「ID」列を追加する前に、データ冗長性の潜在的な影響を考慮してください。

-- 顧客テーブル (複合主キーを使用)
CREATE TABLE Customers (
  customer_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  PRIMARY KEY (customer_id, last_name)
);

-- 注文テーブル (顧客IDと注文IDの複合主キーを使用)
CREATE TABLE Orders (
  order_id INT NOT NULL,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  order_amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (customer_id, order_id),
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- 商品テーブル (商品IDが主キー、追加の「ID」列)
CREATE TABLE Products (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(50) NOT NULL,
  product_price DECIMAL(10,2) NOT NULL,
  product_description TEXT,
  PRIMARY KEY (product_id),
  UNIQUE KEY product_name_idx (product_name)
);

-- 注文明細テーブル (注文ID、商品IDの複合主キー)
CREATE TABLE OrderDetails (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES Orders(order_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

顧客テーブル: このテーブルは、顧客ID、氏名、名、電子メールアドレスを格納します。customer_id と last_name の列の組み合わせが複合主キーとして使用されます。
注文テーブル: このテーブルは、注文ID、顧客ID、注文日、注文金額を格納します。customer_id と order_id の列の組み合わせが複合主キーとして使用されます。また、customer_id 列は Customers テーブルの customer_id 列を参照する外部キーでもあります。
商品テーブル: このテーブルは、商品ID、商品名、商品価格、商品説明を格納します。product_id 列は主キーとして使用され、自動的に増分されます。また、product_name 列にはユニークインデックスが作成されています。
注文明細テーブル: このテーブルは、注文ID、商品ID、数量、単価を格納します。order_id と product_id の列の組み合わせが複合主キーとして使用されます。また、order_id 列は Orders テーブルの order_id 列を参照する外部キーであり、product_id 列は Products テーブルの product_id 列を参照する外部キーでもあります。

この例は、複合主キーと追加の「ID」列の使用方法を示すほんの一例です。具体的な設計は、個々のニーズに合わせて調整する必要があります。

SQL Server における複合主キーと追加の「ID」列の代替方法

サロゲートキーは、テーブル内の他の列に関連を持たない、自動的に生成される単一の整数列です。主キーとして使用できます。

利点:

シンプルで理解しやすい
更新が簡単

データ冗長が発生する可能性がある
複数のテーブル間のレコードを照合する場合、関連する列ではなくサロゲートキーを使用する必要がある

例:

CREATE TABLE Customers (
  customer_id INT NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  PRIMARY KEY (customer_id)
);

グローバル一意識別子 (GUID)

GUID は、128 ビットの値で構成されるランダムな識別子です。主キーとして使用できます。

非常に一意性が高い
分散システムでデータを同期する場合に役立ちます

サロゲートキーよりも格納スペースが大きい
複合主キーほど人間にとってわかりやすいわけではない

CREATE TABLE Customers (
  customer_id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  PRIMARY KEY (customer_id)
);

クラスタ化インデックス付きの単一列

プライマリキーを定義する必要がない場合は、クラスタ化インデックス付きの単一列を使用できます。これは、パフォーマンスが重要な列である場合に役立ちます。

インデックス付きの列に基づいてデータの照合と検索が高速になる

厳格な一意制約を保証しない
主キーのすべての利点を提供しない

CREATE TABLE Customers (
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  PRIMARY KEY CLUSTERED (last_name)
);