DB

SQL ServerのUNIQUE制約をNULL以外の値に限定してみた

 

以下の記事に記載したように、SQL Serverの場合、一意制約(UNIQUE)を設定した列にNULL値の重複データは複数追加できない。

各DBで一意制約(UNIQUE)の挙動を確認してみたデータベースの制約の1つに一意制約(UNIQUE)があり、一意制約(UNIQUE)を設定した列では、重複したデータを追加することができな...

ただ、ユニークインデックス(UNIQUE INDEX)を作成する際にNULL値を除外するという条件を追加することで、一意制約(UNIQUE)を設定した列に対しても、NULL値の重複データを複数追加できるようになる。

今回は、SQL ServerでNULL値以外に対するユニークインデックスを作成してみたので、その手順を共有する。

前提条件

下記記事のDB操作が完了していること。

各DBで一意制約(UNIQUE)の挙動を確認してみたデータベースの制約の1つに一意制約(UNIQUE)があり、一意制約(UNIQUE)を設定した列では、重複したデータを追加することができな...

NULL値を除外したユニークインデックスの作成

SQL ServerでNULL値以外に対するユニークインデックスを作成し検証する手順は、以下の通り。

1) 検証用のemployeeテーブルを削除する。

drop table dbo.employee
NULL値を除外したユニークインデックスの作成_1

2) 検証用のemployeeテーブルを追加する。ただし、一意制約(UNIQUE)の設定は、ここでは実施しない。

create table dbo.employee (
   emp_id int primary key not null
  , name nvarchar(40)
)
NULL値を除外したユニークインデックスの作成_2

3) 検証用のemployeeテーブルの列「name」に、NULL値以外の値に対する一意制約(UNIQUE)を設定する。

create unique index uq_name
    on dbo.employee(name)
    where name is not null
NULL値を除外したユニークインデックスの作成_3

4) 検証用のemployeeテーブルに、検証用のデータを追加する。

insert into dbo.employee values (1, 'テスト　プリン１');
insert into dbo.employee values (2, ' ');
insert into dbo.employee values (3, null);
NULL値を除外したユニークインデックスの作成_4

5) 検証用のemployeeテーブルのデータの中身は、以下の通り。

select * from dbo.employee order by emp_id
NULL値を除外したユニークインデックスの作成_5

6) NULL値以外の値に対する一意制約(UNIQUE)を設定した列「name」にNULL・空文字以外の重複データを追加すると、一意制約エラーになることが確認できる。

insert into dbo.employee values (4, 'テスト　プリン１')
NULL値を除外したユニークインデックスの作成_6

7) NULL値以外の値に対する一意制約(UNIQUE)を設定した列「name」に空文字の重複データを追加すると、一意制約エラーになることが確認できる。

insert into dbo.employee values (4, ' ')
NULL値を除外したユニークインデックスの作成_7

8) NULL値以外の値に対する一意制約(UNIQUE)を設定した列「name」にNULL値の重複データを追加すると、正常にデータを追加できることが確認できる。

insert into dbo.employee values (4, null)
NULL値を除外したユニークインデックスの作成_8

9) 検証用のemployeeテーブルのデータの中身を確認すると、以下のように、NULL値の重複データが含まれていることが確認できる。

select * from employee order by emp_id
NULL値を除外したユニークインデックスの作成_9

要点まとめ

  • SQL Serverの場合、ユニークインデックス(UNIQUE INDEX)を作成する際にNULL値を除外するという条件を追加することで、一意制約(UNIQUE)を設定した列に対しても、NULL値の重複データを複数追加できるようになる。
RELATED POST