以下の記事に記載したように、SQL Serverの場合、一意制約(UNIQUE)を設定した列にNULL値の重複データは複数追加できない。
ただ、ユニークインデックス(UNIQUE INDEX)を作成する際にNULL値を除外するという条件を追加することで、一意制約(UNIQUE)を設定した列に対しても、NULL値の重複データを複数追加できるようになる。
今回は、SQL ServerでNULL値以外に対するユニークインデックスを作成してみたので、その手順を共有する。
前提条件
下記記事のDB操作が完了していること。
NULL値を除外したユニークインデックスの作成
SQL ServerでNULL値以外に対するユニークインデックスを作成し検証する手順は、以下の通り。
1) 検証用のemployeeテーブルを削除する。
1 | drop table dbo.employee |
2) 検証用のemployeeテーブルを追加する。ただし、一意制約(UNIQUE)の設定は、ここでは実施しない。
1 2 3 4 | create table dbo.employee ( emp_id int primary key not null , name nvarchar(40) ) |
3) 検証用のemployeeテーブルの列「name」に、NULL値以外の値に対する一意制約(UNIQUE)を設定する。
1 2 3 | create unique index uq_name on dbo.employee(name) where name is not null |
4) 検証用のemployeeテーブルに、検証用のデータを追加する。
1 2 3 | insert into dbo.employee values (1, 'テスト プリン１'); insert into dbo.employee values (2, ' '); insert into dbo.employee values (3, null); |
5) 検証用のemployeeテーブルのデータの中身は、以下の通り。
1 | select * from dbo.employee order by emp_id |
6) NULL値以外の値に対する一意制約(UNIQUE)を設定した列「name」にNULL・空文字以外の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into dbo.employee values (4, 'テスト プリン１') |
7) NULL値以外の値に対する一意制約(UNIQUE)を設定した列「name」に空文字の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into dbo.employee values (4, ' ') |
8) NULL値以外の値に対する一意制約(UNIQUE)を設定した列「name」にNULL値の重複データを追加すると、正常にデータを追加できることが確認できる。
1 | insert into dbo.employee values (4, null) |
9) 検証用のemployeeテーブルのデータの中身を確認すると、以下のように、NULL値の重複データが含まれていることが確認できる。
1 | select * from employee order by emp_id |
要点まとめ
- SQL Serverの場合、ユニークインデックス(UNIQUE INDEX)を作成する際にNULL値を除外するという条件を追加することで、一意制約(UNIQUE)を設定した列に対しても、NULL値の重複データを複数追加できるようになる。
