DB

各DBで一意制約(UNIQUE)の挙動を確認してみた

 

データベースの制約の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に変更してみたので、そのサンプルプログラムを共...

やってみたこと

  1. Oracleの場合の実行結果
  2. MySQLの場合の実行結果
  3. PostgreSQLの場合の実行結果
  4. 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)
)
Oracleの場合_1

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

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

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

select * from employee order by emp_id
Oracleの場合_3

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

insert into employee values (4, 'テスト　プリン１')
Oracleの場合_4

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

insert into employee values (4, ' ')
Oracleの場合_5

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

insert into employee values (4, null)
Oracleの場合_6

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

commit
Oracleの場合_7_1
select * from employee order by emp_id
Oracleの場合_7_2



「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)
)
MySQLの場合_1

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

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

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

select * from employee order by emp_id
MySQLの場合_3

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

insert into employee values (4, 'テスト　プリン１')
MySQLの場合_4

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

insert into employee values (4, ' ')
MySQLの場合_5

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

insert into employee values (4, null)
MySQLの場合_6

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

commit
MySQLの場合_7_1
select * from employee order by emp_id
MySQLの場合_7_2



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)
)
PostgreSQLの場合_1

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

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

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

select * from employee order by emp_id
PostgreSQLの場合_3

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

insert into employee values (4, 'テスト　プリン１')
PostgreSQLの場合_4

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

insert into employee values (4, ' ')
PostgreSQLの場合_5

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

insert into employee values (4, null)
PostgreSQLの場合_6

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

select * from employee order by emp_id
PostgreSQLの場合_7



「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)
)
SQLServerの場合_1

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

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

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

select * from dbo.employee order by emp_id
SQLServerの場合_3

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

insert into dbo.employee values (4, 'テスト　プリン１')
SQLServerの場合_4

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

insert into dbo.employee values (4, ' ')
SQLServerの場合_5

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

insert into dbo.employee values (4, null)
SQLServerの場合_6

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

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

要点まとめ

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