문제 상황

<insert id="insertRents" parameterType="list" useGeneratedKeys="true" keyProperty="rentId" keyColumn="rent_id">
    INSERT ALL
    <foreach collection="list" item="rent">
        INTO rent (book_id, rent_history_id, rent_due_date, rent_finish_date, rent_status)
        VALUES (#{rent.bookId}, #{rent.rentHistoryId}, SYSDATE+7, #{rent.rentFinishDate}, #{rent.rentStatus})
    </foreach>
    SELECT * FROM DUAL
</insert>

Mybatis의 XML에서 쿼리문을 작성하고 있었는데, 위와 같이 한꺼번에 데이터를 입력하려니 제약조건 위반(ORA-00001) 발생했다. 외래키가 2개가 있어서 둘 간의 제약조건이 생겨서 그런가 생각했다. 하지만 내가 생성한 SQL문은 이러한 제약조건을 한 적이 없었다. '자동으로 제약 조건이 생기는가?' 라고 생각했는데...

CREATE TABLE rent (
    rent_id NUMBER(38,0) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    book_id NUMBER(38,0) NOT NULL,
    rent_history_id NUMBER(38,0) NOT NULL,
    rent_due_date DATE NOT NULL,
    rent_finish_date DATE,
    rent_status VARCHAR2(10) NOT NULL,
    CONSTRAINT FK_book_TO_rent FOREIGN KEY (book_id) 
	    REFERENCES book (book_id),
    CONSTRAINT FK_rent_history_TO_rent FOREIGN KEY (rent_history_id) 
	    REFERENCES rent_history (rent_history_id)
);

해결방안

제약조건을 삭제해라서 해결했지만 삭제한 제약 조건은 rent_id, 즉 primary key의 제약조건을 삭제하니까 문제가 해결되었다. 즉 rent_id에서 문제가 있다고 판단했다.
자세히 찾아보니 INSERT ALL할 경우 IDENTITY가 자동 증가되지 않는 것을 확인했다. 그래서 INSERT INTO문과 SELECT문을 사용해서 문제를 해결했다.

<insert id="insertRents" parameterType="list" >
	INSERT INTO rent (book_id, rent_history_id, rent_due_date, rent_finish_date, rent_status)
        <foreach collection="list" item="rent" separator="UNION ALL">
            SELECT #{rent.bookId}, #{rent.rentHistoryId}, SYSDATE + 7, #{rent.rentFinishDate}, #{rent.rentStatus} 
            FROM DUAL
		</foreach>
</insert>

위 쿼리문을 실제로 실행하면 아래와 같이 실행된다.

INSERT INTO rent (book_id, rent_history_id, rent_due_date, rent_finish_date, rent_status) 
    SELECT ?, ?, SYSDATE + 7, ?, ? FROM DUAL UNION ALL 
    SELECT ?, ?, SYSDATE + 7, ?, ? FROM DUAL UNION ALL 
    SELECT ?, ?, SYSDATE + 7, ?, ? FROM DUAL

위의 쿼리 문은 rent 테이블에 여러 행을 한 번에 삽입하는 INSERT 문이다.

• 기본 구조

  • INSERT INTO rent: rent 테이블에 데이터 삽입
  • 5개의 컬럼에 데이터 입력 (book_id, rent_history_id, rent_due_date, rent_finish_date, rent_status)

• UNION ALL 사용

  • 3개의 SELECT 문을 UNION ALL로 연결하여 3개의 행을 동시에 삽입
  • UNION ALL은 중복된 행도 모두 포함

• FROM DUAL을 사용하여 단일 행 생성

왜 INSERT ALL을 사용하면 안되는가

• INSERT ALL의 경우
  • 하나의 INSERT 문에서 여러 행을 동시에 삽입
  • 최초 호출된 IDENTITY 값이 모든 행에 재사용됨
  • 결과적으로 같은 IDENTITY 값이 중복 삽입되어 무결성 제약조건 위배
• UNION ALL을 사용한 INSERT의 경우
  • 각각의 SELECT 문이 독립적인 INSERT로 처리됨
  • 각 행마다 새로운 IDENTITY 값이 순차적으로 생성됨
  • FROM DUAL UNION ALL로 연결된 각 SELECT 문이 개별 INSERT처럼 동작

따라서 INSERT ALL하면 IDENTITY가 자동 증가하지 않으므로, UNION ALL 방식을 사용하여 Oracle의 IDENTITY 자동 증가가 정상적으로 동작한다.