SETテーブルとMULTISETテーブル

何が違うのか判らなかったんですが、INSERT時に一意制約エラーが出るか出ないかが違う模様。

前提

一意制約エラーはUNIQUEになることを制約で保証している列に対して、既存の値をインサートしようとすると発生します。
Teradataでは、UPI(Unique Primary Index), USI(Unique Secondary Index), PK,
UNIQUE列制約, GENERATED ALWAYS 識別列は、全て上記の制約として扱われるようです。
ちなみに、Teradataだと、PK, UNIQUE制約はUSIとして実装されるようです。

以下のようなテーブルを作成して、PKが重複するデータをインサートした時の挙動を調査してみる。

create set table hoge_set (
col integer not null primary key
);
create multiset table hoge_multiset (
col integer not null primary key
);

insert into hoge_set values (1);
insert into hoge_set values (2);
insert into hoge_set values (3);

insert into hoge_multiset values (1);
insert into hoge_multiset values (2);
insert into hoge_multiset values (3);

INSERT ... VALUESの場合

SET, MULTISETとも一意制約エラー。

insert into hoge_set values (1);
> 2801:  Duplicate unique prime key error in hoge_db.hoge_set.

insert into hoge_multiset values (1);
> 2801:  Duplicate unique prime key error in hoge_db.hoge_multiset.

INSERT ... SELECTの場合

2.1 SETテーブルの場合

セッション・モードがTeradataモードだと、下記のようになる。

insert into hoge_set select col from hoge_set;
> 完了。 0 行処理されました

insert into hoge_set select 1;
> 完了。 0 行処理されました

セッション・モードがANSIモードだと、問答無用でエラー。

insert into hoge_set select col from hoge_set;
> 2801:  Duplicate unique prime key error in hoge_db.hoge_set.

2.2 MULTISETテーブルの場合

一意制約が設定されていたら、問答無用でエラー。

insert into hoge_multiset select col from hoge_multiset;
> 2801:  Duplicate unique prime key error in hoge_db.hoge_multiset.

insert into hoge_multiset select 1;
> 2801:  Duplicate unique prime key error in hoge_db.hoge_multiset.

結論

エラー処理のハンドリング次第？
素直に一意制約エラーにしてくれた方が混乱しないように思うので、MULTISET+UNIQUE制約にするのが良さげ。

追記

念のため、PKでなく、UPIにしてみても結果同じでした。

create set table hoge_set_pi (
col integer not null
)
unique primary index(col);

create multiset table hoge_multiset_pi (
col integer not null
)
unique primary index(col);

insert into hoge_set_pi values (1);
insert into hoge_set_pi values (2);
insert into hoge_set_pi values (3);

insert into hoge_multiset_pi values (1);
insert into hoge_multiset_pi values (2);
insert into hoge_multiset_pi values (3);

insert into hoge_set_pi values (1);
> 2801:  Duplicate unique prime key error in hoge.hoge_set_pi.

insert into hoge_set_pi select col from hoge_set_pi;
> 完了。 0 行処理されました

insert into hoge_multiset_pi values (1);
> 2801:  Duplicate unique prime key error in hoge.hoge_multiset_pi.

insert into hoge_multiset_pi select col from hoge_multiset_pi;
> 2801:  Duplicate unique prime key error in hoge.hoge_multiset_pi.