[개발시 에러] ORA-00001: 유일성(무결성) 제약조건에 위배됩니다.

■에러

ORA-00001: 유일성(무결성) 제약조건에 위배됩니다.

 

이 에러의 주된 가능성으로서는 INSERT ~ SELECT ~를 사용했을 경우에

기본키(PK)나 고유키(UK)와 같은 데이터를 INSERT 하려고 했을 때 발생한다.

 

■원인

단순한 일의 제약 위반이므로 INSERT ~ VALUES (~)의 경우에는 문제가 되지 않는다.

INSERT ~ SELECT ~를 사용했을 경우에 대상이 되는 데이터가 대량이 되는 일이 적지 않기 때문에

원인이 되는 레코드의 특정이 매우 번거롭다.

마찬가지로 MERGE에 의한 데이터의 조작도 원인 레코드의 특정이 매우 번거롭게 된다.

 

■(ver: 11gR2부터) 대응방법 1 : 오류 데이터를 무시하고 삽입하는 방법

IGNORE_ROW_ON_DUPKEY_INDEX 힌트 (11gR2)를 사용하여

오류 행을 건너뛰고 다른 레코드를 처리한다.

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;
두 줄이 만들어졌습니다.

 

■ (ver: 10gR2부터) 대응방법 2 : 원인 데이터를 식별하고 제외하는 접근법

DBMS_ERRLOG 패키지 10gR2와 LOG ERRORS 절을 사용하여 오류가 발생하는 원인 레코드 데이터를 로깅한다.

MS-ACCESS 데이터 복사 및 붙여 넣기에 실패했을 때와 같은 데이터 외에도 오류 메시지 정보가 전용 테이블에 저장된다.

그러나 ACCESS와 달리 DBMS_ERRLOG를 사용하여 테이블 단위로 수동으로 로그 테이블을 작성해야 한다.

 

- 테스트 데이터

SQL> CREATE TABLE  SRC (
  ID NUMBER,
  NUM NUMBER
  );
테이블이 작성되었습니다.
 
SQL> CREATE TABLE  DEST (
  ID NUMBER PRIMARY KEY, /* 기본키 */
  NUM NUMBER
 );
테이블이 작성되었습니다.
 
SQL> INSERT INTO SRC VALUES (1, 100);
한 개의 행이 작성되었습니다.
 
SQL> INSERT INTO SRC VALUES (1, 200); /* 위의 행 ID값과 동일 */
한 개의 행이 작성되었습니다.
 
SQL> INSERT INTO SRC VALUES (2, 300);
한 개의 행이 작성되었습니다.
 
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레코드는 성공 */
 
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;

 

■대응방법 3 :  기본키(PK)와 고유키(UK)를 일시적으로 사용하지 않도록 설정하는 방법 (추천은 안 함)

ALTER TABLE DEST MODIFY PRIMARY KEY disable;
… 작업 생략
ALTER TABLE DEST MODIFY PRIMARY KEY enable;

이 방법은 기본키를 비활성화할 때 테이블 정의 중에 생성되거나 연관된 인덱스의 정의 정보(인덱스 이름, 테이블 공간, 파티션, PCTFREE, INITRANS, etc)가 완전히 손실된다.

그 후 ENABLE 했을 때에 인덱스가 재구축되어 모두 디폴트치의 인덱스가 작성된다.

인덱스를 가지고 있지 않는 기본키(PK), 고유키(UK)는 존재하지 않는다.

인덱스가 없는 경우, 암묵적으로 제약명을 사용한 인덱스가 생성된다.

테이블스페이스나 파티셔닝 정보가 디폴트 치로 바뀌는 것으로 시스템 전체에 문제가 파급될 가능성도 있으므로

주의할 필요가 있다.

어차피 삭제된다면 DBMS_METADATA 등으로 재구축용 DDL을 준비하고

ALTER TABLE DEST DROP PRIMARY KEY DROM INDEX;

상기의 SQL문과 drop, 재작성을 하는 것이 깔끔하다.

 

Oracle Database SQL 언어 참조 「공통 SQL DDL절」

・DISABLE

  : 「유일한 인덱스를 사용하는 고유 제약 조건 또는 기본키 제약조건을 사용하지 않으면 고유 색인이 삭제됩니다.」

・ENABLE

  : 「일의 제약 또는 기본키 제약을 사용 가능하게 한 경우, 키에 인덱스가 존재하지 않으면 유일 인덱스가 작성됩니다. 」

※ 작성한다고는 했지만 DISABLE 이전의 원래 설정을 유지하여 재작성하는 것은 아님.

 

■대응방법 4 : 기본키 상태를 확인하는 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;