Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

開発時に発生するエラー

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

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

原因

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

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

対応 〜

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

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行が作成されました。

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

対応 〜

DBMS_ERRLOG パッケージ と 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;

プライマリキーやユニークキーを一時的に無効化して入れてしまうアプローチ

おすすめしないが、やらかしてしまうことへの注意喚起。

ALTER TABLE DEST MODIFY PRIMARY KEY disable;
… 作業省略
ALTER TABLE DEST MODIFY PRIMARY KEY enable;

この方法はプライマリキーを disable したとき、テーブル定義時に作成、または、関連付けした索引の定義情報（インデックス名、表領域、パーティション、PCTFREE、INITRANS、etc）が全損失する。
その後 enable したときにインデックスが再構築され、すべてデフォルト値のインデックスが作成される。

インデックスを伴わないプライマリキー、ユニークキーというのはない。
インデックスがない場合、暗黙的に制約名を使用したインデックスが生成される。

テーブルスペースやパーティショニング情報がデフォルト値に置き換わることでシステム全体に問題が波及する可能性もあるので要注意。

どうせ削除されるのであれば DBMS_METADATA などで再構築用の DDL を用意して

ALTER TABLE DEST DROP PRIMARY KEY DROP INDEX; 

と drop と再作成してしまったほうが清々しい。

Oracle Database SQL言語リファレンス 「共通のSQL DDL句 」

• DISABLE
  「一意索引を使用している一意制約または主キー制約を使用禁止にすると、一意索引は削除されます。」

• ENABLE
  「一意制約または主キー制約を使用可能にした場合、キーに索引が存在しないと、一意索引が作成されます」
  ※ 作成するとは言ったが DISABLE 前の元の設定を保持して再作成するとは言っていない。

プライマリキーの状態を確認をするための SQL

プライマリキー名、構成カラム、インデックス名、インデックスの表領域 などを確認する SQL

select 
  c.table_name, c.constraint_name, c.status cc_status,
  cc.position, cc.column_name, 
  ix.index_name, ix.uniqueness, ix.tablespace_name, ix.visibility, ix.status
from user_indexes ix,
  user_constraints c, user_cons_columns cc
where
  ix.table_name in ('MY_TABLE') and c.constraint_type = 'P'
  and ix.index_name = c.index_name and c.constraint_name = cc.constraint_name
order by cc.position;

デフォルト・テーブルスペース の現在値

select username, DEFAULT_TABLESPACE from user_users;

---

関連事項

• 主キー制約、一意制約
• 主キー・プライマリキーの追加、変更、削除
• 遅延制約

一覧ページへ戻る

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