SQL ServerのUNIQUE制約をNULL以外の値に限定してみた｜ITエンジニアとして経験・学習したこと

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

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

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

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

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

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

1	drop table dbo.employee

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

create table dbo.employee (

emp_id int primary key not null

, name nvarchar(40)

)

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

create unique index uq_name

on dbo.employee(name)

where name is not null

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

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値の重複データを複数追加できるようになる。

MySQLでユーザーを作成しテーブルを追加してみた