【保存版】PostgreSQLでユニーク列にNULLを許可する際に絶対に知っておきたいポイント

PostgreSQLでユニーク列にNULLを許可する方法

PostgreSQLにおいて、ユニーク制約は列の値が重複することを防ぎます。デフォルトでは、NULL値も重複チェックの対象となりますが、状況によってはNULLを許可したい場合もあります。

本記事では、PostgreSQLでユニーク列にNULLを許可する方法について、2つの方法に分けて詳しく解説します。

方法1：UNIQUE INDEX WITH NULLS NOT DISTINCT

PostgreSQL 15では、UNIQUE INDEX に新しいオプション NULLS NOT DISTINCT が導入されました。このオプションを使用することで、NULL値を区別し、重複を許可しないユニーク制約を作成できます。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) UNIQUE INDEX WITH NULLS NOT DISTINCT
);

上記のように、name 列にユニーク制約を設定した場合、NULL以外の値が重複することは許可されません。一方、2つのNULL値は重複として扱われません。

方法2：CHECK制約と部分インデックスの組み合わせ

PostgreSQL 15以前のバージョンでは、CHECK 制約と部分インデックスを組み合わせることで、ユニーク列にNULLを許可する方法があります。

1. CHECK制約でNULL以外の値の重複を禁止

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

上記のように、CHECK 制約を使用して、name 列にNULL以外の値が重複することを禁止します。

1. 部分インデックスでNULL値を検索

CREATE INDEX idx_users_name_not_null ON users (name) WHERE name IS NOT NULL;

部分インデックスを作成することで、name 列のNULL以外の値を高速に検索できます。

• PostgreSQL 15以降を使用している場合:

補足事項

• 外部キー制約を参照する列には、NULLを許可しないことをお勧めします。
• NULL値の扱いについては、アプリケーションロジックで適切に処理する必要があります。

PostgreSQLでユニーク列にNULLを許可するサンプルコード

方法1：UNIQUE INDEX WITH NULLS NOT DISTINCT

-- テーブルの作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) UNIQUE INDEX WITH NULLS NOT DISTINCT
);

-- データの挿入
INSERT INTO users (name) VALUES ('Taro');
INSERT INTO users (name) VALUES (NULL);
INSERT INTO users (name) VALUES ('Jiro');
INSERT INTO users (name) VALUES (NULL);

-- 既存のデータの更新
UPDATE users
SET name = NULL
WHERE id = 2;

-- データの削除
DELETE FROM users
WHERE id = 3;

方法2：CHECK制約と部分インデックスの組み合わせ

-- テーブルの作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  CHECK (name IS UNIQUE OR name IS NULL)
);

-- 部分インデックスの作成
CREATE INDEX idx_users_name_not_null ON users (name) WHERE name IS NOT NULL;

-- データの挿入
INSERT INTO users (name) VALUES ('Taro');
INSERT INTO users (name) VALUES (NULL);
INSERT INTO users (name) VALUES ('Jiro');
INSERT INTO users (name) VALUES (NULL);

-- 既存のデータの更新
UPDATE users
SET name = NULL
WHERE id = 2;

-- データの削除
DELETE FROM users
WHERE id = 3;

上記のコード例では、以下の操作を行っています。

1. テーブルの作成:
   • users テーブルを作成します。
   • id 列を主キーとして定義します。
   • name 列を文字列型 (VARCHAR(255)) で定義します。
2. ユニーク制約の設定:
   • 方法1では、UNIQUE INDEX WITH NULLS NOT DISTINCT オプションを使用して、name 列にユニーク制約を設定します。
3. データの挿入:
   • Taro と NULL の2つのレコードを name 列に挿入します。
   • 方法1では、2つのNULL値が重複として扱われないことを確認します。
4. 既存のデータの更新:
   • id が2番目のレコードの name 列をNULLに更新します。
   • 方法1では、更新後も2つのNULL値が存在することを確認します。
5. データの削除:

PostgreSQLでユニーク列にNULLを許可するその他の方法

方法3：デフォルト値とUNIQUE制約の組み合わせ

この方法は、デフォルト値を使用してNULL以外の値を自動的に設定することで、ユニーク制約を満たすようにします。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) UNIQUE DEFAULT 'default_name'
);

上記のように、name 列にデフォルト値 'default_name' を設定した場合、明示的に値を挿入しない場合、すべてのレコードに 'default_name' が格納されます。

この方法の利点は、コードが簡潔になることです。一方で、デフォルト値が適切でない場合や、NULLを許容する必要がある場合に適していないという欠点があります。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) UNIQUE
);

CREATE OR REPLACE FUNCTION trigger_set_default_name()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.name IS NULL THEN
    UPDATE users
    SET name = 'default_name'
    WHERE id = NEW.id
    RETURNING NEW;
  ELSE
    RETURN NEW;
  END IF;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER set_default_name
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_default_name();

上記のように、トリガー set_default_name を作成し、users テーブルへの挿入前に実行するように設定します。このトリガーは、name 列の値がNULLの場合に、デフォルト値 'default_name' を設定します。

この方法の利点は、柔軟性が高いことです。一方で、トリガーのロジックが複雑になるという欠点があります。

この方法は、ビューを使用して、name 列からNULL値を除外した仮想テーブルを作成し、そのビューに対してユニーク制約を設定します。

CREATE VIEW view_users AS
SELECT id, name
FROM users
WHERE name IS NOT NULL;

CREATE UNIQUE INDEX idx_view_users_name ON view_users (name);

上記のように、view_users ビューを作成し、name 列からNULL値を除外します。その後、このビューに対してユニーク制約 idx_view_users_name を設定します。

この方法の利点は、既存のテーブルを変更せずに、ユニーク制約を適用できることです。一方で、ビューを使用するたびに元のテーブルを参照するため、パフォーマンスが低下する可能性があるという欠点があります。

最適な方法は、要件と状況によって異なります。

• デフォルト値で問題ない場合: 方法3 (デフォルト値とUNIQUE制約の組み合わせ)
• 既存のテーブルを変更したくない場合: 方法5 (ビューとUNIQUE制約の組み合わせ)