開発時に発生するエラー

ORA-00001: 一意制約に反しています

ORA-00001 〜エラーの主な可能性としては INSERT 〜 SELECT 〜 を使用した場合にプライマリキーやユニークキーと同じデータをインサートしようとした場合に発生する。

原因

原因としては単純な一意制約違反なので INSERT 〜 VALUES (〜) の場合には問題にならない。
INSERT 〜 SELECT 〜 を使用した場合に対象となるデータが大量になることが少なくないので原因となるレコードの特定が非常に面倒である。同様に MERGE によるデータの操作も原因レコードの特定が非常に面倒となる。

エラーデータは無視してインサートするアプローチ

対応 Oracle 11g Release2

IGNORE_ROW_ON_DUPKEY_INDEX ヒント Oracle 11g Release2 を使用してエラー行をスキップして、その他のレコードを処理する。

IGNORE_ROW_ON_DUPKEY_INDEX ヒントの書式

  • IGNORE_ROW_ON_DUPKEY_INDEX(テーブル名(項目名,[項目名,...]))
  • IGNORE_ROW_ON_DUPKEY_INDEX(テーブル名,インデックス名]))
SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(dest(id)) */ into dest select * from src;
2行が作成されました。

原因データを特定し、それを除外するアプローチ

対応 Oracle 10g Release2

DBMS_ERRLOG パッケージ Oracle 10g Release2 と LOG ERRORS 句を使用することでエラーの発生する原因レコードデータをロギングする。
MS-ACCESS のデータのコピー&ペーストに失敗したときのようなデータに加えエラーメッセージ情報が専用テーブルに保存される。但し、ACCESS とは違い DBMS_ERRLOG を使い、テーブル単位に手動でログ用テーブルを作成する必要がある。

テストデータ

SQL> CREATE TABLE SRC (
  2    ID   NUMBER,
  3    NUM  NUMBER
  4  );
表が作成されました。
 
SQL> CREATE TABLE DEST (
  2    ID   NUMBER PRIMARY KEY, /* プライマリキー */
  3    NUM  NUMBER
  4  );
表が作成されました。
 
SQL> INSERT INTO SRC VALUES ( 1, 100);
1行が作成されました。
 
SQL> INSERT INTO SRC VALUES ( 1, 200);
1行が作成されました。
 
SQL> INSERT INTO SRC VALUES ( 2, 300);
1行が作成されました。
 
SQL> INSERT INTO DEST SELECT * FROM SRC;
INSERT INTO DEST SELECT * FROM SRC
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(RIVUS.SYS_C0012335)に反しています

DBMS_ERRLOG パッケージと LOG ERRORS 句を使用する

SQL> exec dbms_errlog.create_error_log('dest');
 
PL/SQLプロシージャが正常に完了しました。
SQL> desc err$_dest
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$                                    NUMBER
 ORA_ERR_MESG$                                      VARCHAR2(2000)
 ORA_ERR_ROWID$                                     ROWID
 ORA_ERR_OPTYP$                                     VARCHAR2(2)
 ORA_ERR_TAG$                                       VARCHAR2(2000)
 ID                                                 VARCHAR2(4000)
 NUM                                                VARCHAR2(4000)

LOG ERRORS REJECT LIMIT UNLIMITED エラー発生箇所をすべてログにする。

SQL> insert into dest select * from src LOG ERRORS REJECT LIMIT UNLIMITED;
 
2行が作成されました。  ← 2レコードは成功
 
SQL> select id, num, ora_err_mesg$ from err$_dest;
 
ID       NUM      ORA_ERR_MESG$
-------- -------- ------------------------------------------------------------
1        200      ORA-00001: 一意制約(RIVUS.SYS_C0012335)に反しています
 
-- 不要になったら DROP TABLE err$_dest;


関連事項

一覧ページへ戻る

OTN (Oracle Technology Network)によるエラーメッセージによる情報だけでは、対処に困ったエラーについてのプラクティスです。
ベスト・プラクティスというわけではないので、書いてあることに固執しないで広い視野でエラー対応してください。

ORA-00001の関連トピックス

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー