SQL Serverで複数カラム（NULL許可のカラムを含む）のユニーク制約を設定する方法。

• facebook

オフィス狛 技術部のJoeです。

データベースで複数カラムを組み合わせたユニーク制約の設定が可能ですが、データベースによって、NULL値を重複として扱うかどうかが違っています。

例えばMySQLは、NULL値を重複として扱いませんが、SQL Serverは、NULL値を重複した値として扱います。

今回は、SQL ServerでNULL値を含む複数カラムの組み合わせで、ユニーク制約を設定する方法をご紹介したいと思います。
※下記の内容はSQL Server 2017で確認しています

まず、下記のようなテーブルを作成、データを追加します。

【テーブル作成】

CREATE TABLE fish
(
  id bigint IDENTITY(1,1) NOT NULL,
  name nvarchar(10) NOT NULL,
  code varchar(2) NULL,
  PRIMARY KEY CLUSTERED(id)
);

+--------+--------+------+
| id(PK) | name   | code |
+--------+--------+------+
|      1 | かつお |   11 |
|      2 | かつお | NULL |
|      3 | かつお | NULL |
|      4 | まぐろ |   11 |
|      5 | まぐろ |   22 |
+--------+--------+------+

このテーブルに「name」と「code」の組み合わせの複数のカラムに対して、ユニーク制約の設定しようとすると、エラーが発生します。

【ユニーク制約の設定SQL】

ALTER TABLE fish ADD CONSTRAINT uq_fish UNIQUE ( name, code );

【エラー内容】

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.fish' and the index name 'uq_fish'. The duplicate key value is (かつお, ).

エラー内容のとおり、「かつお, NULL」の組み合わせで、既に重複しているデータがあるので、ユニーク制約を設定することが出来ませんでした。
（SQL Serverは、NULL値を重複した値として扱うため）

もし、NULL値を重複した値として扱いたくない場合、下記の方法で設定が可能です。

【設定方法１】スカラー関数とチェック制約

スカラー関数とチェック制約を組み合わせて、NULL値を含む複数カラムでのユニーク制約を設定可能です。

-- スカラー関数作成
CREATE FUNCTION fishCheck (
  @id bigint,
  @name nvarchar(10),
  @code varchar(20)
)  
RETURNS BIT
WITH RETURNS NULL ON NULL INPUT -- ※1
AS
BEGIN
  RETURN
    CASE WHEN EXISTS
      (SELECT 1
      FROM fish
      WHERE id <> @id -- ※2
      AND name = @name
      AND code = @code)
    THEN 1 ELSE 0 END
END;

-- チェック制約の設定
ALTER TABLE fish ADD CONSTRAINT ck_fish
  CHECK (dbo.fishCheck(id, name, code) <> 1); -- ※3

※1 引数のいずれかが NULL の場合に関数の本体を呼び出すことなく NULL を返します
そのため、code（NULL許可のカラム）がNULLであれば、重複のチェックは行いません
※2 重複レコードを探すSQLでは、チェックする（挿入、更新しようとする）データは除きます
※3 スカラー関数の結果が「1（重複あり）」でなければ、チェックOKとします

と、上記のように設定することができますが、２つの定義が必要ですし、少し解り難いかなという印象です。
もう一つの下記方法ですと、１つの定義で設定が可能です。

【設定方法２】ユニークインデックス

ユニークインデックスでは、WHERE句で条件を指定できます。
「code（NULL許可のカラム）がNULL以外」の条件を指定することで、NULLを除外したユニーク制約の設定が可能です。

CREATE UNIQUE INDEX uq_idx_fish ON fish(name, code) WHERE code IS NOT NULL;

もしSQL Serverで、NULL許可のカラムにユニーク制約を設定する場合、上記を試してみてください。

ただ、MSDNに「一意制約の列を選択する場合は、NOT NULL と定義された列を選択します。」との記載があるので、
ユニーク制約に設定するカラムには、NULL値が入らない前提での設計が必要ですね。
一意インデックスの作成

SQL Server