SQL Server - null以外の値でユニークにする

SQL Serverでnull以外の値でユニークにしたいときがあると思います。

先に方法を書くと「フィルター選択されたインデックス(filtered index)」を使うことでnull以外の値でユニークを実現できます。

フィルター選択されたインデックスの作成

ということで今回は「フィルター選択されたインデックス」を確認してみたいと思います。今まで知らなかったのですがSQL Server 2008から利用できます。

ちなみにですが検索すると以下の別の方法も見つかると思いますが、これらよりもフィルタ選択されたインデックスを使う方がお手軽でわかりやすいのでいいかなと。

[HOWTO] NULL 以外のすべての値に一意性を強制する方法 (ANSI UNIQUE 制約)

UNIQUE制約のおさらい

まずはおさらいで普通にUNIQUE制約を使った場合に、nullも1つの値として扱われて重複チェックされることを確認します。

UNIQUE 制約の作成

-- テーブル作成時にUNIQUE制約を設定
create table Sample1(
    Id int not null,
    Value int,
    constraint PK_Sample1 primary key(Id),
    constraint UQ_Sample1_Value unique(Value)
);

-- テスト用のデータ
insert into Sample1
output inserted.*
values(1, 1), (2, 2), (3, null);
/*
Id          Value
----------- -----------
1           1
2           2
3           NULL
*/

この状態でさらにValueカラムがnullのレコードを追加してもエラーになります。

insert into Sample1
values(4, null);
/*
制約 'UQ_Sample1_Value' の UNIQUE KEY 違反。オブジェクト 'dbo.Sample1' には重複するキーを挿入できません。重複するキーの値は (<NULL>) です。
*/

フィルター選択されたインデックスを使う

フィルター選択されたインデックスは、テーブルを作成したあとにUNIQUEインデックスを作成します。

-- テーブル
create table Sample2(
    Id int not null,
    Value int,
    constraint PK_Sample2 primary key(Id)
);

-- フィルタ選択されたUNIQUEインデックス
create unique index UQ_Sample2_Value
    on Sample2(Value)
    where Value is not null;  -- where句で条件を指定する

-- テスト用のデータ
insert into Sample2
output inserted.*
values(1, 1), (2, 2), (3, null);
/*
Id          Value
----------- -----------
1           1
2           2
3           NULL
*/

この状態でnullのレコードを追加できます。

insert into Sample2
values(4, null);
select * from Sample2;
/*
Id          Value
----------- -----------
1           1
2           2
3           NULL
4           NULL
*/

null以外の値のレコードは重複エラーになります。

insert into Sample2
values(5, 1);
/*
一意インデックス 'UQ_Sample2_Value' を含むオブジェクト 'dbo.Sample2' には重複するキー行を挿入できません。重複するキーの値は (1) です。
*/