各DBで一意制約(UNIQUE)の挙動を確認してみた｜ITエンジニアとして経験・学習したこと

データベースの制約の1つに一意制約(UNIQUE)があり、一意制約(UNIQUE)を設定した列では、重複したデータを追加することができない。ただ、NULL値については、データベースの種類によっては、一意制約(UNIQUE)を設定した列でも複数のデータを追加できる。

今回は、Oracle・MySQL・PostgreSQL・SQL Serverの各DB上で、一意制約(UNIQUE)の挙動を確認してみたので、その結果を共有する。

前提条件

Oracle XEのインストールが完了していること。

また、下記記事に記載されている、MySQLとPostgreSQLでのユーザー作成とUSER_DATAテーブル作成が完了していること。

MySQLでユーザーを作成しテーブルを追加してみたMySQLの場合は、Workbenchを利用すると、スキーマ(データベース)やユーザーの作成をGUIベースで実施できる。今回は、Work...

Postgresqlでユーザーを作成しテーブルを追加してみたPostgresqlの場合は、pgAdminを利用すると、ユーザーやデータベースの作成をGUIベースで実施できる。今回は、pgAdmin...

さらに、下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

Oracleの場合の実行結果

Oracleの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。

1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。

create table employee (
   emp_id integer primary key not null
  , name varchar2(40)
  , constraint uq_name unique(name)
)

create table employee (

emp_id integer primary key not null

, name varchar2(40)

, constraint uq_name unique(name)

)

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

insert into employee values (1, 'テスト　プリン１');
insert into employee values (2, ' ');
insert into employee values (3, null);
commit;

insert into employee values (1, 'テスト　プリン１');

insert into employee values (2, ' ');

insert into employee values (3, null);

commit;

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

select * from employee order by emp_id

1	select * from employee order by emp_id

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

insert into employee values (4, 'テスト　プリン１')

1	insert into employee values (4, 'テスト　プリン１')

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

insert into employee values (4, ' ')

1	insert into employee values (4, ' ')

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

insert into employee values (4, null)

1	insert into employee values (4, null)

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

commit

commit

select * from employee order by emp_id

1	select * from employee order by emp_id

「FlexClip」はテンプレートとして利用できる動画・画像・音楽などが充実した動画編集ツールだったテンプレートとして利用できるテキスト・動画・画像・音楽など(いずれも著作権フリー)が充実している動画編集ツールの一つに、「FlexCli...

MySQLの場合の実行結果

MySQLの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。

1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。

create table employee (
   emp_id int primary key not null
  , name varchar(40)
  , constraint uq_name unique(name)
)

create table employee (

emp_id int primary key not null

, name varchar(40)

, constraint uq_name unique(name)

)

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

insert into employee values (1, 'テスト　プリン１');
insert into employee values (2, ' ');
insert into employee values (3, null);
commit;

insert into employee values (1, 'テスト　プリン１');

insert into employee values (2, ' ');

insert into employee values (3, null);

commit;

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

select * from employee order by emp_id

1	select * from employee order by emp_id

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

insert into employee values (4, 'テスト　プリン１')

1	insert into employee values (4, 'テスト　プリン１')

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

insert into employee values (4, ' ')

1	insert into employee values (4, ' ')

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

insert into employee values (4, null)

1	insert into employee values (4, null)

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

commit

commit

select * from employee order by emp_id

1	select * from employee order by emp_id

freelance hubを利用して10万件を超える案件情報からJava Spring案件を検索してみたfreelance hubは、レバテックフリーランスやフリエン(furien)を始めとした多くのフリーランスエージェントの案件をまとめて...

PostgreSQLの場合の実行結果

PostgreSQLの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。

1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。

create table employee (
   emp_id integer primary key not null
  , name varchar(40)
  , constraint uq_name unique(name)
)

create table employee (

emp_id integer primary key not null

, name varchar(40)

, constraint uq_name unique(name)

)

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

insert into employee values (1, 'テスト　プリン１');
insert into employee values (2, ' ');
insert into employee values (3, null);

insert into employee values (1, 'テスト　プリン１');

insert into employee values (2, ' ');

insert into employee values (3, null);

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

select * from employee order by emp_id

1	select * from employee order by emp_id

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

insert into employee values (4, 'テスト　プリン１')

1	insert into employee values (4, 'テスト　プリン１')

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

insert into employee values (4, ' ')

1	insert into employee values (4, ' ')

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

insert into employee values (4, null)

1	insert into employee values (4, null)

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

select * from employee order by emp_id

1	select * from employee order by emp_id

「MiniTool Partition Wizard」はパーティション分割・統合・バックアップ・チェックを直感的に行える便利ツールだったハードディスクの記憶領域を論理的に分割し、分割された個々の領域のことを、パーティションといいます。例えば、以下の図の場合、C/D...

SQL Serverの場合の実行結果

SQL Serverの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。

1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。

create table dbo.employee (
   emp_id int primary key not null
  , name nvarchar(40)
  , constraint uq_name unique(name)
)

create table dbo.employee (

emp_id int primary key not null

, name nvarchar(40)

, constraint uq_name unique(name)

)

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

insert into dbo.employee values (1, 'テスト　プリン１');
insert into dbo.employee values (2, ' ');
insert into dbo.employee values (3, null);

insert into dbo.employee values (1, 'テスト　プリン１');

insert into dbo.employee values (2, ' ');

insert into dbo.employee values (3, null);

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

select * from dbo.employee order by emp_id

1	select * from dbo.employee order by emp_id

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

insert into dbo.employee values (4, 'テスト　プリン１')

1	insert into dbo.employee values (4, 'テスト　プリン１')

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

insert into dbo.employee values (4, ' ')

1	insert into dbo.employee values (4, ' ')

6) 一意制約(UNIQUE)を設定した列「name」にNULL値の重複データを追加すると、他のDBとは違い、一意制約エラーになることが確認できる。

insert into dbo.employee values (4, null)

1	insert into dbo.employee values (4, null)

なお、NULL値の重複データを追加してもエラーにならないように一意制約を設定する方法については、以下の記事を参照のこと。

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

要点まとめ

データベースの制約の1つに一意制約(UNIQUE)があり、一意制約(UNIQUE)を設定した列では、重複したデータを追加することができない。
Oracle・MySQL・PostgreSQLの場合は、一意制約(UNIQUE)を設定した列でもNULL値の重複データは複数追加でき、SQL Serverの場合は、一意制約(UNIQUE)を設定した列にNULL値の重複データは複数追加できない違いがある。