PostgreSQLにおける一意制約とインデックス: はじめてのチュートリアル

2024-04-02

PostgreSQLにおける一意制約とインデックスの違い

PostgreSQLでは、一意制約とユニークインデックスを使用して、テーブル内のデータの重複を防ぐことができます。

一意制約は、テーブル内の特定の列の値が一意であることを保証します。一方、ユニークインデックスは、データの検索を高速化するために使用されますが、一意制約も同時に暗黙的に設定されます。

主な違い

項目	一意制約	ユニークインデックス
目的	データの一意性を保証する	データ検索を高速化する
データ構造	B-木	B-木
制約	PRIMARY KEY、UNIQUE キー	UNIQUE 制約
インデックス	自動作成される	手動で作成できる
NULL 値	許可されない	許可される (1 つのみ)
複合列	使用可能	使用可能

詳細

一意制約

CREATE TABLE または ALTER TABLE を使用して作成
PRIMARY KEY 制約も一意制約
NULL 値は許可されない
インデックスは自動的に作成
参照整合性チェックに使用

ユニークインデックス

CREATE INDEX を使用して作成
NULL 値は1つのみ許可
データ検索を高速化

データの一意性を保証する必要がある場合は、一意制約を使用
データ検索を高速化したい場合は、ユニークインデックスを使用
両方の機能が必要場合は、ユニーク制約を使用

例

-- 一意制約
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE NOT NULL
);

-- ユニークインデックス
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  UNIQUE INDEX (title)
);

一意制約

-- テーブル作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

-- データ挿入
INSERT INTO users (username, email) VALUES ('alice', '[email protected]');
INSERT INTO users (username, email) VALUES ('bob', '[email protected]');

-- 重複データ挿入エラー
INSERT INTO users (username, email) VALUES ('alice', '[email protected]');
-- ERROR: duplicate key value violates unique constraint "users_username_key"

-- 重複データ挿入エラー (email)
INSERT INTO users (username, email) VALUES ('charlie', '[email protected]');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

ユニークインデックス

-- テーブル作成
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  UNIQUE INDEX (title)
);

-- データ挿入
INSERT INTO articles (title) VALUES ('Hello, world!');
INSERT INTO articles (title) VALUES ('This is a title');

-- 重複データ挿入 (許可)
INSERT INTO articles (title) VALUES ('Hello, world!');

-- データ検索 (高速化)
SELECT * FROM articles WHERE title = 'Hello, world!';

上記はあくまでサンプルコードです。実際のユースケースに合わせてコードを修正してください。
一意制約とユニークインデックスのどちらを使用するか迷った場合は、上記のどちらを使用すべきかを参照してください。

PostgreSQLでは、一意制約とインデックス以外にも、データの重複を防ぐ方法があります。以下にいくつかの例を紹介します。

PRIMARY KEY 制約は、テーブル内の各行を一意に識別するために使用されます。 PRIMARY KEY 制約は、一意制約とインデックスの両方の機能を兼ね備えています。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL
);

UNIQUE キー制約

UNIQUE キー制約は、テーブル内の特定の列の値が一意であることを保証します。 UNIQUE キー制約は、一意制約とインデックスの両方の機能を兼ね備えています。

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) UNIQUE NOT NULL
);

CHECK 制約

CHECK 制約は、テーブル内の列の値が特定の条件を満たしていることを保証します。 CHECK 制約を使用して、一意制約を定義することができます。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  CHECK (username IS UNIQUE)
);

複合キー

複合キーは、複数の列を組み合わせて一意な識別子を作成します。複合キーを使用して、一意制約を定義することができます。

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  UNIQUE (customer_id, product_id)
);

トリガー

トリガーは、特定のデータベース操作が発生したときに実行されるプログラムです。トリガーを使用して、データの重複を防ぐことができます。

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT 1 FROM users WHERE username = NEW.username) THEN
    RAISE EXCEPTION 'username already exists';
  END IF;
END;