SQL Server - null以外の値でユニークにする
SQL Serverでnull以外の値でユニークにしたいときがあると思います。
先に方法を書くと「フィルター選択されたインデックス(filtered index)」を使うことでnull以外の値でユニークを実現できます。
ということで今回は「フィルター選択されたインデックス」を確認してみたいと思います。今まで知らなかったのですがSQL Server 2008から利用できます。
ちなみにですが検索すると以下の別の方法も見つかると思いますが、これらよりもフィルタ選択されたインデックスを使う方がお手軽でわかりやすいのでいいかなと。
[HOWTO] NULL 以外のすべての値に一意性を強制する方法 (ANSI UNIQUE 制約)
UNIQUE制約のおさらい
まずはおさらいで普通にUNIQUE制約を使った場合に、nullも1つの値として扱われて重複チェックされることを確認します。
-- テーブル作成時に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) です。 */