ということで、今回はDB2と、それに似ているMS SQL Serverの一意インデックスについて。
DB2(とMS SQL Server)ではNULLも一つの「ユニークな値」としてカウントされるため、重複登録はできません。
db2 => create table t1(i1 int)
db2 => create unique index idx_t1 on t1(i1)
db2 => insert into t1 values(1)
db2 => insert into t1 values(null)
db2 => insert into t1 values(null)
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL0803N INSERT ステートメント、UPDATE ステートメントの 1 つ以上の値、および
DELETE ステートメントが原因で発生した外部キーの更新は無効です。これは、"1"
で識別される主キー、ユニーク制約、またはユニーク索引が表 "MEIJI.T1"
が索引キーに対して重複する値を持つことを制限しているためです。 SQLSTATE=23505
db2 => select * from t1
I1
-----------
1
-
2 レコードが選択されました。
複合カラムにおいても同じようにNULLを一つの「ユニークな値」として扱います。そのため、
構成するすべてのカラムがNULLの場合でも重複登録できません。
db2 => create table t2(i1 int, i2 int)
db2 => create unique index idx_t2 on t2(i1, i2)
db2 => insert into t2 values(1,1)
db2 => insert into t2 values(1,2)
db2 => insert into t2 values(1,null)
db2 => insert into t2 values(1,null)
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL0803N INSERT ステートメント、UPDATE ステートメントの 1 つ以上の値、および
DELETE ステートメントが原因で発生した外部キーの更新は無効です。これは、"1"
で識別される主キー、ユニーク制約、またはユニーク索引が表 "MEIJI.T2"
が索引キーに対して重複する値を持つことを制限しているためです。 SQLSTATE=23505
db2 => insert into t2 values(null,null)
db2 => insert into t2 values(null,null)
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL0803N INSERT ステートメント、UPDATE ステートメントの 1 つ以上の値、および
DELETE ステートメントが原因で発生した外部キーの更新は無効です。これは、"1"
で識別される主キー、ユニーク制約、またはユニーク索引が表 "MEIJI.T2"
が索引キーに対して重複する値を持つことを制限しているためです。 SQLSTATE=23505
db2 => select * from t2
I1 I2
----------- -----------
1 1
1 2
1 -
- -
4 レコードが選択されました。
さて、今回一意インデックス定義をCREATE TABLEとは別に宣言していることにお気づきでしょうか?
実は一般的なデータベースでは一意制約(UNIQUE CONSTRAINT)と一意インデックス(UNIQUE INDEX)は、
仕様的には等価なので、前回、前々回と代用してきました。(一意制約を満たすために、内部的に一意インデックスを用います)
しかしながら、実はDB2だけ、一意制約と一意インデックスは仕様的に違うために、今回は厳密に一意インデックスで定義しています。DB2の一意制約はNULLを許しません。そのため、一意制約の構成列はすべてNOT NULLとして定義する必要があります。
db2 => create table t3(i1 int, unique(i1))
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL0542N "I1" という名前の列は、NULL
値を含む可能性があるので、主キーまたはユニーク・キー制約の列にすることができません。 SQLSTATE=42831
これは仕様としてはかなり厳しいものです。そのため、他のデータベースからDB2にテーブル定義を持ってくる場合は一意制約は一意インデックスに書き直した方が、スムーズにことが運びます。