ORA-00001: 무결성 제약 조건에 위배됩니다. (ORA-00001: unique constraint violated.) 오류는 오라클에서 첫 번째로 지정한 오류입니다. 그만큼 오라클 데이터베이스에서 가장 중요하게 생각하는 조건 중 하나라고 할 수 있습니다. 오늘은 ORA-00001 오류의 원인과 해결에 대해 알아보는 시간입니다.
참고로 오라클에서 말하는 첫 번째 무결성 오류는 데이터베이스 일반 이론에서 말하는 무결성(integrity) 중에서 특히 유일성(unique)에 초점을 맞추고 있는 개념입니다. 엄밀히 말하면 무결성보다는 유일성(unique) 제약 조건 위배라고 할 수 있습니다. 이제부터 해당 오류의 원인과 해결 방법에 대해 안내해드리겠습니다.
무결성 제약 조건 위배의 발생 원인
데이터베이스에서 레코드를 유일하게 식별할 수 있게 하는 조합의 중복이 발생하여 유일성에 위배되어 발생합니다. 이를 해결하기 위해서는 당연히 무결성 제약 조건에 위배가 되지 않도록 데이터를 점검해야 합니다.
프라이머리 키와 유니크 인덱스
테이블에서 유일성을 보장하는 역할을 하여 ORA-00001 오류와 관련이 많은 객체 2가지를 소개해드리겠습니다.
프라이머리 키(PK:기본키)와 유니크 인덱스(UI:유일 색인)가 바로 그것입니다.
프라이머리 키 (기본키)
기본키에 의한 무결성 제약을 설정할 수 있습니다.
유니크 인덱스
유니크 인덱스는 디스크라이브 시 PK에 표시가 되지 않습니다.
하지만 고유성이 보장되어야 하기 때문에 유니크 인덱스로도 무결성 제약 위배가 발생하는 경우가 있습니다.
Insert, ORA-00001 (저장 시)
ORA-00001 오류가 발생하는 가장 대표적인 경우입니다.
1. 저장의 대상이 되는 테이블에 이미 값이 있는 경우(target table 문제)
2. 다수의 데이터를 한꺼번에 저장할 때, 소스 데이터 자체에서 중복이 발생한 경우(source table 문제)
3. 상기의 (1)문제와 (2)문제가 동시에 발생하여, 소스 테이블에서 타깃 테이블로 인서트 과정 동안 무결성 위배가 함께 발생하는 경우입니다(source, target table 둘 다 점검 필요)
Update, ORA-00001 (수정 시)
업데이트 결과 값에서 키의 중복이 발생한 경우입니다.
1. 업데이트의 대상이 되는 결과 집합 자체에서 발생하는 가능성
2. 결과 집합 및 업데이트의 영향을 받지 않은 집합 사이에서 교차하여 무결성 위배가 발생한 가능성
Delete, ORA-00001 (삭제 시)
소스 테이블이나 타겟 테이블보다는 삭제 시도 시 트리거에 의한 다른 테이블 개입 때 중복 발생으로 나타납니다.
삭세 시에 무결성 오류가 발생하는 경우는 흔하지는 않습니다. 이때까지 이러한 경우를 한 번 보았는데, 설계가 굉장히 특이하게 된 경우였답니다.😂
이 부분도 결국 insert, update 문제로 무결성 위배가 발생합니다.
무결성 제약 조건에 위배됩니다. 해결 방법
가장 흔히 자주 발생하는 insert 시 무결성 제약 조건 위배에 대해 정리를 해봅시다. 다음과 같은 예시 자료를 준비해 보았습니다.
- 예시 자료에서 가장 위에 있는 {TAB_PK} 테이블은 인서트 타깃 테이블이며, 기본키는 [K1, K2] 모든 컬럼입니다. 현재 [1, 2] 레코드가 존재합니다.
- {TAB_SRC} 테이블은 소스 테이블 자체에 중복이 있는 경우입니다. [1, 1] 조합이 중복으로 2개 있습니다. (상기 사진에서 빨간색으로 표시)
- {TAB_SRC2} 테이블은 타깃 테이블에 있는 [1, 2] 레코드가 소스에도 중복하여 있는 경우입니다. [1, 2] 레코드가 {TAB_PK} 테이블에도 있고, {TAB_SRC2} 테이블에도 존재합니다. (상기 사진에서 주황색으로 표시)
ALTER TABLE USER_NAME.TAB_PK DROP PRIMARY KEY CASCADE;
DROP TABLE USER_NAME.TAB_PK CASCADE CONSTRAINTS;
DROP TABLE USER_NAME.TAB_SRC CASCADE CONSTRAINTS;
DROP TABLE USER_NAME.TAB_SRC2 CASCADE CONSTRAINTS;
CREATE TABLE TAB_PK
(
K1 NUMBER,
K2 NUMBER
)
TABLESPACE TABLESPACE_NAME;
CREATE UNIQUE INDEX USER_NAME.PK_TAB_PK
ON USER_NAME.TAB_PK (K1, K2)
LOGGING
TABLESPACE TABLESPACE_NAME;
ALTER TABLE USER_NAME.TAB_PK ADD (
CONSTRAINT C_PK_TAB_PK
PRIMARY KEY
(K1, K2)
USING INDEX USER_NAME.PK_TAB_PK);
CREATE TABLE TAB_SRC
(
C1 NUMBER,
C2 NUMBER
)
TABLESPACE TABLESPACE_NAME;
CREATE TABLE TAB_SRC2
(
C1 NUMBER,
C2 NUMBER
)
TABLESPACE TABLESPACE_NAME;
INSERT INTO TAB_PK VALUES (1, 2);
INSERT INTO TAB_SRC VALUES (1, 1);
INSERT INTO TAB_SRC VALUES (1, 1);
INSERT INTO TAB_SRC VALUES (2, 3);
INSERT INTO TAB_SRC VALUES (3, 4);
INSERT INTO TAB_SRC2 VALUES (1, 2);
INSERT INTO TAB_SRC2 VALUES (2, 3);
COMMIT;
{TAB_SRC} 테이블은 소스 테이블 자체에서 레코드의 중복이 있습니다.
그대로 insert 시도하는 경우 [1, 1] 레코드가 2개가 인서트 되어 무결성 위배 오류가 발생합니다.
INSERT INTO USER_NAME.TAB_PK (K1, K2)
SELECT C1, C2 FROM TAB_SRC;
ERROR at line 1:
ORA-00001: unique constraint (USER_NAME.C_PK_TAB_PK) violated
{TAB_SRC2} 테이블은 [1, 2] 레코드가 이미 타깃 테이블에도 존재하는 경우입니다. 인서트 시도 시 오류가 발생할 것입니다.
INSERT INTO USER_NAME.TAB_PK (K1, K2)
SELECT C1, C2 FROM TAB_SRC2;
ERROR at line 1:
ORA-00001: unique constraint (USER_NAME.C_PK_TAB_PK) violated무결성 제약 조건에 위배되는 데이터 정리하여 해결하기
무결성 위배(ORA-00001)를 처리할 수 있는 방법 몇 가지를 소개해드립니다.
ROWID로 식별하기
소스 테이블 자체에 유일성이 보장되지 않는 경우, 각 키 그룹별로 최소 로우아이디(min rowid)로 입력하기
INSERT INTO USER_NAME.TAB_PK (K1, K2)
SELECT C1, C2
FROM TAB_SRC
WHERE ROWID IN ( SELECT MIN (ROWID)
FROM TAB_SRC
GROUP BY C1, C2);
3 rows created.EXISTS로 중복 제외하기
타겟 테이블과 소스 테이블의 데이터 중복이 발생한 경우, 있는 값은 제외하는 방법
(이 부분은 제외하고 인서트 하는 방향으로 진행했는데, 경우에 따라 업데이트로 하셔도 됩니다)
INSERT INTO USER_NAME.TAB_PK (K1, K2)
SELECT C1, C2
FROM TAB_SRC2 TS
WHERE NOT EXISTS
(SELECT NULL
FROM TAB_PK
WHERE K1 = TS.C1 AND K2 = TS.C2);
1 row created.무결성 제약 조건 인덱스를 삭제하여 해결하는 방법
테이블의 무결성 제약이 필요 없다고 판단하는 경우입니다.
실제로 사용하는 테이블에는 적용할 가능성이 거의 없고, 임시 또는 테스트 테이블인 경우 고려해볼 만합니다.
DROP INDEX USERNAME.INDEXNAME;무결성 제약 조건 다시보기
Unique Constraint (무결성 제약 조건)은 오라클뿐만 아니라 데이터베이스 일반에서 항상 중요하게 나오는 주제입니다. 해당 게시물은 인덱스에 의한 무결성 제약 조건을 중점적으로 다루고 있습니다.
Primary Key
예시 테이블 {TB_PK} 는 [C1] , [C2] 칼럼을 키값으로 가정합니다.
기본키는 해당 로우를 테이블에서 고유하게 식별할 수 있도록 하는 유일한 값입니다. 이를 무결성 제약이라고 합니다. 즉 해당 두 칼럼은 2개 이상의 중복된 조합을 가질 수 없습니다.
실제로 인서트를 하면서 살펴보겠습니다.
Insert Into Values
예제 테이블에 칼럼으로 ('1', '2', '3') 을 입력해 보았어요.
INSERT INTO TB_PK VALUES ('1', '2', '3');
ORA-00001: unique constraint violated
그렇다면 ('1', '2', '3') 데이터가 있는 상태에서 한 번 더 인서트를 한다면?
INSERT INTO TB_PK VALUES ('1', '2', '3');
해당과 같은 값을 한 번 더 입력하면 상기와 같은 오류가 발생합니다.
('1', '2', '3') 뿐만 아니라 키값을 이루는 다른 조합도 동일한 오류가 나타날 것입니다.
예를 들면 ('1', '2', '4'), ('1', '2', '5') 와 같은 조합이 모두 무결성 제약에 어긋납니다.
How To Insert Duplicate Data?
그렇다면 무수히 많은 중복된 데이터에서 고유한 조합을 식별하여 insert 하는 방법은 무엇일까요?
해당 예시 테이블은 [A, B, C] 가 ('1', '2', '3') 이 중복된 값이 있습니다. (1번 행, 4번 행)
당연히 모든 값을 insert 하면 무결성 제약 조건에 위배가 되겠죠?
Group By Key
고유한 조합 [C1, C2]를 얻기 위해 [A, B]로 그룹하여 최소 ROWID를 조회해 보았습니다.
INSERT INTO TB_PK (C1, C2, C3)
SELECT A, B, C
FROM TB_INSERTSELECT
WHERE ROWID IN ( SELECT MIN (ROWID)
FROM TB_INSERTSELECT
GROUP BY A, B);
인서트가 잘 되었네요. Min, Max 등 그룹함수를 이용하시면 유용합니다.
Unique Index ~ unique constraint violated
TB_UI 테이블은 기본키가 없습니다. 하지만 [C4, C5] 칼럼을 유니크인덱스로 지정해 놓았습니다.
그리하여 [C4, C5] 칼럼이 중복되는 경우 무결성 제약 조건에 위배가 될 것입니다.
프라이머리키가 없는데 무결성 오류 왜 날 수 있는지 이제 아시겠죠? 이것으로 인덱스를 중심하여 무결성 제약 조건에 대해 알아보았습니다.
기본키 제약조건 (PRIMARY KEY constraint)
오라클 데이터베이스에서 기본키 제약조건에 대해 알아보도록 합시다. 기본키 제약조건의 뜻과 함께 설정하는 방법에 대해 안내해드리려고 합니다.
예제 테이블 DEPT에 대한 스크립트는 다음과 같으며, 기본키는 'DEPTNO' 컬럼 1개로 설정한다고 가정하겠습니다.
CREATE UNIQUE INDEX USER_NAME.PK_DEPT ON USER_NAME.DEPT
(DEPTNO)
LOGGING
TABLESPACE TABLESPACE_NAME
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
--제약조건, 테이블에는 하나의 기본 키만 가질 수 있습니다.
ALTER TABLE USER_NAME.DEPT ADD (
CONSTRAINT PK_DEPT
PRIMARY KEY
(DEPTNO)
USING INDEX USER_NAME.PK_DEPT);
이제 기본키의 제약조건에 대한 특성을 알아보도록 하겠습니다.
하나의 기본 키
테이블에는 하나의 기본 키만 가질 수 있습니다. 기본키는 각 행을 고유하게 식별할 수 있도록 하는 유일한 컬럼 그룹이기 때문에, 오직 하나만 가질 수 있습니다. 특징은 Unique Index와 Not Null 제약 조건을 부여한 것과 유사하다고 할 수 있습니다.
DEPT 테이블에 이미 DEPTNO 가 '10' 인 로우(행, 튜플, 레코드)가 존재한다고 가정할 때, DEPTNO 컬럼이 '10'인 데이터를 인서트 하려고 시도하면, 무결성 제약조건에 의해 데이터 저장이 불가합니다. 예시를 한 번 보여드리겠습니다!
SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON유일성(무결성), Unique
이미 존재하는 키값에 대해 인서트를 다음과 같이 해보겠습니다. 제약조건으로 인하여 인서트가 불가한 것을 확인할 수 있답니다.
--기본키는 각 행을 고유하게 식별할 수 있도록 합니다 UNIQUE INDEX, NOT NULL
--중복불가
INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (10, 'DEV', 'BUSAN');
ERROR at line 1:
ORA-00001: unique constraint (USER_NAME.PK_DEPT) violatedNot Null
기본키에 해당 하는 컬럼(열, 어트리뷰트) 은 NULL 일 수 없습니다. 그리하여 NOT NULL 제약을 따로 명시하지 않아도 됩니다.
--NULL 불가
ALTER TABLE DEPT MODIFY DEPTNO NULL;
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL
키값인 DEPTNO 컬럼을 NULL로 저장하려고 시도하는 경우, 발생하는 오류는 'ORA-01400: cannot insert NULL into'입니다.
INSERT INTO DEPT (DNAME, LOC)
VALUES ('DEV', 'BUSAN');
ERROR at line 1:
ORA-01400: cannot insert NULL into ("USER_NAME"."DEPT"."DEPTNO")