主キーを自動採番にしたときに、ORA-0001一意制約違反が発生する

 Oracle APEXのクイックSQLを使って表を作成すると、特別な設定をしない限り列IDが表に追加され、自動採番（generated by default on null as identityの設定）になります。

このように構成された表に以下の操作を行うと、ORA-0001 一意制約違反が発生することがあります。

1. CSVなど、データ・ワークショップを使ってデータをインポートしたとき。
2. クイックSQLの表ディレクティブ/insertを指定して、サンプル・データを生成したとき。

このような操作が実行された後に、以下のALTER文を実行すると主キーとなっている列の最大値まで進めることができます。

alter table 表名 modify 主キー列名 generated by default on null as identity (start with limit value);

以下より、どのような状況か説明します。

クイックSQLの以下のモデルを使って、表SAMPLE_NOTESを作成します。

sample_notes
    note

以下のDDLが生成されます。モデルでは定義していませんが、主キー列としてIDが追加され、自動採番の設定になっています。

-- create tables
create table sample_notes (
    id                             number generated by default on null as identity 
                                   constraint sample_notes_id_pk primary key,
    note                           varchar2(4000 char)
)
;

生成した表SAMPLE_NOTESを元にアプリケーションの生成までを実施します。作成するアプリケーションの名前は一意制約違反とし、対話モード・レポートとフォームのページを含みます。

アプリケーションといっても、表SAMPLE_NOTESにフォームを使ってデータを投入できれば良いだけなので、以上で十分です。

表SAMPLE_NOTESは作成したばかりでデータが投入されたことはないため、シーケンスは１から始まり、列IDには１から連番が振られます。

ここで、データ・ワークショップを使って、以下のCSVを表SAMPLE_NOTESにロードします。

ID, NOTE
5, A
6, B
7, C

SQLワークショップのユーティリティから、データ・ワークショップを開き、データのロードを実行します。

コピー・アンド・ペーストを選択し、ロードするデータをウィンドウに貼り付けます。

次へ進みます。

ロード先は既存の表で、表としてSAMPLE_NOTESを選択します。

ここで、列IDをそのままロードしますが、列IDが自動採番ということは、値自体は意味を持ちません（一般にサロゲート・キーと呼びます）。本来、列IDはロード対象から外すべきであり、このデータをロードしなければ一意制約違反は発生しません。

構成をクリックして、ソース列IDを未マップ列とすることで、ロードの対象から外すことができます。

IDも含めてデータを表SAMPLE_NOTESにロードしていると、フォームからデータを投入する際にORA-0001 - 一意制約違反が発生することがあります。

シーケンスは１から始まります。データ・ワークショップのデータ・ロードでは、ロード対象のデータが３行だったので、３つシーケンスを進めています。ロードしたデータのIDは

5、６、７

で、フォームから入力する新規データに割り振られるIDは４から始まります。（ロードする列IDのデータが１から連番になっている場合は、一意制約違反は発生しません。）

２回目のデータ入力で、IDは５となります。すでにIDが５のデータは存在するため、ORA-0001が発生します。

この状況に対応するため、以下のALTER文を実行します。

alter table sample_notes modify id generated by default on null as identity (start with limit value);

クイックSQLで表ディレクティブ/insert句を使ってサンプル・データを作成した場合も、同様の対応が必要になります。

以下のように/insert 3をつけて、表SAMPLE_NOTESを作成します。

sample_notes /insert 3
    note

サンプルとなるデータを挿入するINSERT文として、以下の文が生成されています。

insert into sample_notes (

    id,

    note

) values (

    1,

    'Suscipit tristique ac volutpat risus.Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt.'

);

自動採番は、列IDに値が指定されていないときに実施されます。このINSERT文では列IDにデータが指定されているため、自動採番は行われません。シーケンスの値も進まないため、新規に行を挿入する際に列IDの値として１が割り当てられます。結果として、フォームからデータを挿入する際にORA-0001の一意制約違反が発生します。

この場合も、データのロードと同様にALTER文を実行することで対応できます。

完