データベースの制約の1つに一意制約(UNIQUE)があり、一意制約(UNIQUE)を設定した列では、重複したデータを追加することができない。ただ、NULL値については、データベースの種類によっては、一意制約(UNIQUE)を設定した列でも複数のデータを追加できる。
今回は、Oracle・MySQL・PostgreSQL・SQL Serverの各DB上で、一意制約(UNIQUE)の挙動を確認してみたので、その結果を共有する。
前提条件
Oracle XEのインストールが完了していること。
また、下記記事に記載されている、MySQLとPostgreSQLでのユーザー作成とUSER_DATAテーブル作成が完了していること。
![](https://www.purin-it.com/wp-content/uploads/2020/10/beach-1850059_640.jpg)
![](https://www.purin-it.com/wp-content/uploads/2020/10/sunset-690083_640-320x180.jpg)
さらに、下記記事の「前提条件」の内容が完了していること。
![](https://www.purin-it.com/wp-content/uploads/2020/10/munsterland-1494592_640-320x180.jpg)
やってみたこと
Oracleの場合の実行結果
Oracleの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。
1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。
1 2 3 4 5 | create table employee ( emp_id integer primary key not null , name varchar2(40) , constraint uq_name unique(name) ) |
![Oracleの場合_1](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_1-1.png)
2) 検証用のemployeeテーブルに、検証用のデータを追加する。
1 2 3 4 | insert into employee values (1, 'テスト プリン1'); insert into employee values (2, ' '); insert into employee values (3, null); commit; |
![Oracleの場合_2](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_2-1.png)
3) 検証用のemployeeテーブルのデータの中身は、以下の通り。
1 | select * from employee order by emp_id |
![Oracleの場合_3](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_3-1.png)
4) 一意制約(UNIQUE)を設定した列「name」にNULL・空文字以外の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into employee values (4, 'テスト プリン1') |
![Oracleの場合_4](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_4-1.png)
5) 一意制約(UNIQUE)を設定した列「name」に空文字の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into employee values (4, ' ') |
![Oracleの場合_5](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_5.png)
6) 一意制約(UNIQUE)を設定した列「name」にNULL値の重複データを追加すると、正常にデータを追加できることが確認できる。
1 | insert into employee values (4, null) |
![Oracleの場合_6](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_6.png)
7) コミットして検証用のemployeeテーブルのデータの中身を確認すると、以下のように、NULL値の重複データが含まれていることが確認できる。
1 | commit |
![Oracleの場合_7_1](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_7_1.png)
1 | select * from employee order by emp_id |
![Oracleの場合_7_2](https://www.purin-it.com/wp-content/uploads/2021/11/oracle_7_2.png)
![](https://www.purin-it.com/wp-content/uploads/2021/10/web-design-g8b3045b46_640-320x180.jpg)
MySQLの場合の実行結果
MySQLの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。
1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。
1 2 3 4 5 | create table employee ( emp_id int primary key not null , name varchar(40) , constraint uq_name unique(name) ) |
![MySQLの場合_1](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_1-1.png)
2) 検証用のemployeeテーブルに、検証用のデータを追加する。
1 2 3 4 | insert into employee values (1, 'テスト プリン1'); insert into employee values (2, ' '); insert into employee values (3, null); commit; |
![MySQLの場合_2](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_2-1.png)
3) 検証用のemployeeテーブルのデータの中身は、以下の通り。
1 | select * from employee order by emp_id |
![MySQLの場合_3](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_3-1.png)
4) 一意制約(UNIQUE)を設定した列「name」にNULL・空文字以外の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into employee values (4, 'テスト プリン1') |
![MySQLの場合_4](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_4-1.png)
5) 一意制約(UNIQUE)を設定した列「name」に空文字の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into employee values (4, ' ') |
![MySQLの場合_5](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_5.png)
6) 一意制約(UNIQUE)を設定した列「name」にNULL値の重複データを追加すると、正常にデータを追加できることが確認できる。
1 | insert into employee values (4, null) |
![MySQLの場合_6](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_6.png)
7) コミットして検証用のemployeeテーブルのデータの中身を確認すると、以下のように、NULL値の重複データが含まれていることが確認できる。
1 | commit |
![MySQLの場合_7_1](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_7_1.png)
1 | select * from employee order by emp_id |
![MySQLの場合_7_2](https://www.purin-it.com/wp-content/uploads/2021/11/mysql_7_2.png)
![](https://www.purin-it.com/wp-content/uploads/2021/11/entrepreneur-gde3cb21f7_640-320x180.jpg)
PostgreSQLの場合の実行結果
PostgreSQLの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。
1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。
1 2 3 4 5 | create table employee ( emp_id integer primary key not null , name varchar(40) , constraint uq_name unique(name) ) |
![PostgreSQLの場合_1](https://www.purin-it.com/wp-content/uploads/2021/11/postgresql_1-1.png)
2) 検証用のemployeeテーブルに、検証用のデータを追加する。
1 2 3 | insert into employee values (1, 'テスト プリン1'); insert into employee values (2, ' '); insert into employee values (3, null); |
![PostgreSQLの場合_2](https://www.purin-it.com/wp-content/uploads/2021/11/postgresql_2-1.png)
3) 検証用のemployeeテーブルのデータの中身は、以下の通り。
1 | select * from employee order by emp_id |
![PostgreSQLの場合_3](https://www.purin-it.com/wp-content/uploads/2021/11/postgresql_3-1.png)
4) 一意制約(UNIQUE)を設定した列「name」にNULL・空文字以外の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into employee values (4, 'テスト プリン1') |
![PostgreSQLの場合_4](https://www.purin-it.com/wp-content/uploads/2021/11/postgresql_4-1.png)
5) 一意制約(UNIQUE)を設定した列「name」に空文字の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into employee values (4, ' ') |
![PostgreSQLの場合_5](https://www.purin-it.com/wp-content/uploads/2021/11/postgresql_5.png)
6) 一意制約(UNIQUE)を設定した列「name」にNULL値の重複データを追加すると、正常にデータを追加できることが確認できる。
1 | insert into employee values (4, null) |
![PostgreSQLの場合_6](https://www.purin-it.com/wp-content/uploads/2021/11/postgresql_6.png)
7) 検証用のemployeeテーブルのデータの中身を確認すると、以下のように、NULL値の重複データが含まれていることが確認できる。
1 | select * from employee order by emp_id |
![PostgreSQLの場合_7](https://www.purin-it.com/wp-content/uploads/2021/11/postgresql_7.png)
![](https://www.purin-it.com/wp-content/uploads/2021/05/sunflowers-268015_640-320x180.jpg)
SQL Serverの場合の実行結果
SQL Serverの場合の実行結果は以下の通りで、一意制約(UNIQUE)を設定した列でも、NULL値のデータは複数追加できる。
1) 検証用のemployeeテーブルを追加する。列「name」に一意制約(UNIQUE)を設定している。
1 2 3 4 5 | create table dbo.employee ( emp_id int primary key not null , name nvarchar(40) , constraint uq_name unique(name) ) |
![SQLServerの場合_1](https://www.purin-it.com/wp-content/uploads/2021/11/sqlserver_1-1.png)
2) 検証用のemployeeテーブルに、検証用のデータを追加する。
1 2 3 | insert into dbo.employee values (1, 'テスト プリン1'); insert into dbo.employee values (2, ' '); insert into dbo.employee values (3, null); |
![SQLServerの場合_2](https://www.purin-it.com/wp-content/uploads/2021/11/sqlserver_2-1.png)
3) 検証用のemployeeテーブルのデータの中身は、以下の通り。
1 | select * from dbo.employee order by emp_id |
![SQLServerの場合_3](https://www.purin-it.com/wp-content/uploads/2021/11/sqlserver_3-1.png)
4) 一意制約(UNIQUE)を設定した列「name」にNULL・空文字以外の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into dbo.employee values (4, 'テスト プリン1') |
![SQLServerの場合_4](https://www.purin-it.com/wp-content/uploads/2021/11/sqlserver_4-1.png)
5) 一意制約(UNIQUE)を設定した列「name」に空文字の重複データを追加すると、一意制約エラーになることが確認できる。
1 | insert into dbo.employee values (4, ' ') |
![SQLServerの場合_5](https://www.purin-it.com/wp-content/uploads/2021/11/sqlserver_5.png)
6) 一意制約(UNIQUE)を設定した列「name」にNULL値の重複データを追加すると、他のDBとは違い、一意制約エラーになることが確認できる。
1 | insert into dbo.employee values (4, null) |
![SQLServerの場合_6](https://www.purin-it.com/wp-content/uploads/2021/11/sqlserver_6.png)
なお、NULL値の重複データを追加してもエラーにならないように一意制約を設定する方法については、以下の記事を参照のこと。
![](https://www.purin-it.com/wp-content/uploads/2021/06/santorini-ge775c149b_640-320x180.jpg)
要点まとめ
- データベースの制約の1つに一意制約(UNIQUE)があり、一意制約(UNIQUE)を設定した列では、重複したデータを追加することができない。
- Oracle・MySQL・PostgreSQLの場合は、一意制約(UNIQUE)を設定した列でもNULL値の重複データは複数追加でき、SQL Serverの場合は、一意制約(UNIQUE)を設定した列にNULL値の重複データは複数追加できない違いがある。