複合主キー vs UNIQUE 制約 vs UNIQUE インデックス vs CHECK 制約

2024-04-02

PostgreSQLで2つの列の組み合わせに一意制約を設定する方法

複合主キーを設定するには、以下の方法があります。

CREATE TABLE ステートメント

CREATE TABLE テーブル名 (
  列名1 型1,
  列名2 型2,
  PRIMARY KEY (列名1, 列名2)
);

ALTER TABLE テーブル名
ADD CONSTRAINT 主キー名 PRIMARY KEY (列名1, 列名2);

例

CREATE TABLE ユーザ (
  ユーザID INT NOT NULL,
  メールアドレス VARCHAR(255) NOT NULL,
  PRIMARY KEY (ユーザID, メールアドレス)
);

この例では、ユーザIDとメールアドレスの組み合わせが複合主キーとなります。つまり、同じユーザIDとメールアドレスを持つレコードは、テーブル内に2つ以上存在することはできません。

複合主キーのメリットとデメリット

メリット

データの重複を防ぐことができる

インデックスが大きくなる
複合主キーを構成する列の値を変更できない

PostgreSQLで2つの列の組み合わせに一意制約を設定するには、複合主キー機能を使うことができます。複合主キーは、データの重複を防ぎ、一貫性を保つために有効な手段です。

-- ユーザテーブル

CREATE TABLE ユーザ (
  ユーザID INT NOT NULL,
  名前 VARCHAR(255) NOT NULL,
  メールアドレス VARCHAR(255) NOT NULL,
  PRIMARY KEY (ユーザID, メールアドレス)
);

-- 注文テーブル

CREATE TABLE 注文 (
  注文ID INT NOT NULL,
  ユーザID INT NOT NULL,
  注文日 TIMESTAMP NOT NULL,
  商品名 VARCHAR(255) NOT NULL,
  PRIMARY KEY (注文ID),
  FOREIGN KEY (ユーザID) REFERENCES ユーザ (ユーザID)
);

このコードを実行すると、以下のテーブルが作成されます。

ユーザテーブル
- ユーザID (INT)
- 名前 (VARCHAR(255))
注文テーブル
- 注文日 (TIMESTAMP)

注文テーブルのユーザID列は、ユーザテーブルのユーザID列を参照する外部キー制約になっています。

このサンプルコードは、複合主キーと外部キー制約を組み合わせる方法を示しています。

PostgreSQLで2つの列の組み合わせに一意制約を設定する他の方法

UNIQUE 制約

CREATE TABLE テーブル名 (
  列名1 型1,
  列名2 型2,
  UNIQUE (列名1, 列名2)
);

CREATE TABLE ユーザ (
  ユーザID INT NOT NULL,
  メールアドレス VARCHAR(255) NOT NULL,
  UNIQUE (ユーザID, メールアドレス)
);

UNIQUE インデックス

CREATE TABLE テーブル名 (
  列名1 型1,
  列名2 型2,
);

CREATE UNIQUE INDEX インデックス名 ON テーブル名 (列名1, 列名2);

CREATE TABLE ユーザ (
  ユーザID INT NOT NULL,
  メールアドレス VARCHAR(255) NOT NULL,
);

CREATE UNIQUE INDEX idx_user_email ON ユーザ (ユーザID, メールアドレス);

この例では、ユーザIDとメールアドレスの組み合わせにユニークインデックスが作成されます。ユニークインデックスは、一意制約と同じように機能しますが、インデックスとして使用することもできます。

CHECK 制約

CREATE TABLE テーブル名 (
  列名1 型1,
  列名2 型2,
  CHECK (列名1 IS NOT NULL AND 列名2 IS NOT NULL AND (列名1, 列名2) UNIQUE)
);

CREATE TABLE ユーザ (
  ユーザID INT NOT NULL,
  メールアドレス VARCHAR(255) NOT NULL,
  CHECK (ユーザID IS NOT NULL AND メールアドレス IS NOT NULL AND (ユーザID, メールアドレス) UNIQUE)
);