brunch

You can make anything
by writing

C.S.Lewis

by 천사마음 Oct 08. 2015

About Oracle Identity Column

지난 시간에 About Oracle Virtual Column을 통하여 Oracle ORDBMS(이하 오라클) 11g에서 소개되었던 Virtual Column에 대해 알아봤습니다. 오라클에서 제공하는 Virtual Column은 테이블에서 정의한 하나 이상의 컬럼 값을 활용하는 계산 식의 결과 또는 함수의 리턴 값 등을 활용할 때, 유용하게 활용할 수 있는 도구입니다.

이번 시간에는 오라클 12c에서 새롭게 도입된 Identity Column에 대해 알아보겠습니다. Oracle Identity Column은 대체 키(Surrogate Key)를 효과적으로 구성하기 위해 도입된 도구입니다.

대체 키는 실세계를 모델링한 엔터티 또는 데이터베이스의 객체에 대한 고유한 식별자로 활용하기에 적합한 단일 후보 키가 존재하지 않을 때(성능 문제가 발생할 경우에도 활용할 수 있습니다), 특정 응용 프로그램의 데이터로부터 얻을 수 없어 임의의 식별번호로 구성한 키를 의미합니다. 필자는 일반적으로 대체 키를 활용하지 않는 것을 추천합니다. 하지만 성능 문제가 발생하거나 특정 상황에서 대체 키를 활용할 경우 효과적일 때 대체 키를 활용할 수 있습니다. 이런 상황을 효과적으로 해결하는 방안으로 오라클 12c에서는 Identity Column을 제공합니다.

Identity Column

오라클 12c에서 도입된 Identity Column은 데이터 모델에서 대체 키를 활용하는 경우에 유용하게 활용할 수 있습니다. Identity Column은 각 행이 생성될 때, 각 행에 대해 Sequence Generator에서 일정 단위의 정숫값을 생성하여 지정할 수 있습니다.

오라클 12c 이전 버전에서는 대체 키를 활용해야 하는 상황에서 Sequence 기능을 활용했었습니다. 하지만 Identity Column과 Sequence 기능의 차이가 있으므로 대체 키를 활용해야 하는 상황일 때는 Identity Column을 활용하는 것을 추천합니다.

Syntax

Identity Column의 기본적인 구문은 다음과 같습니다.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

오라클은 기본값으로 ALWAYS 옵션을 적용하여 컬럼 값을 항상 생성합니다. 그러므로 Identity Column에 대해 INSERT 또는 UPDATE 명령을 수행하면 에러가 발생합니다. 만약 사용자가 특정 값을 입력해야 할 경우에는 어떻게 해야 할까요? 추천하지 않은 방법은 아니지만, BY DEFAULT 옵션으로 Identity Column을 생성하면 됩니다.

다음 구문은 identity_options의 상세 구문입니다. 기존 SEQUENCE 구문과 유사함을 알 수 있습니다.

{ START WITH ( integer | LIMIT VALUE )
   | INCREMENT BY integer
   | ( MAXVALUE integer | NOMAXVALUE )
   | ( MINVALUE integer | NOMINVALUE )
   | ( CYCLE | NOCYCLE )
   | ( CACHE integer | NOCACHE )
   | ( ORDER | NOORDER )
}...

Identity Column에 대한 활용법은 Example 단락에서 상세히 다루겠습니다.

Identity Column Features

이 절에서는 Identity Column의 특징에 대해 알아보겠습니다. Identity Column은 테이블마다 하나의 Identity Column을 가질 수 있습니다. 이뿐만 아니라, 반드시 NUMBER 형의 데이터 타입으로만 정의해야 합니다.

Identity Column으로 컬럼을 생성하면, 컬럼 생성 문장에서 DEFAULT 옵션을 지정할 수 없으며, 기본적으로 NOT NULL과 NOT DEFERRABLE 제약조건이 암묵적으로 정의됩니다. 이런 특징으로 말미암아 특정 제약조건과 충돌이 발생하여 에러가 나타날 수 있습니다.

재미있는 특징으로 Identity Column에 암호화 알고리즘을 적용할 수 있습니다. 마지막으로 CREATE TABLE AS SELECT(CTAS) 명령으로 테이블을 생성한 컬럼은 Identity Column 속성을 상속받지 않으며, 단순한 NUMBER 데이터 타입의 컬럼으로 변환됩니다.

Identity column에 대한 보다 상세한 제약 사항은 다음 링크의 Restrictions on Identity Columns 단락을 참조하시기 바랍니다.

Example

이 절에서는 Identity Column을 생성하고, 이를 활용하는 방법을 간단한 예제를 이용하여 살펴보겠습니다.

먼저 Identity Column으로 테이블을 생성하겠습니다. 주의해야 할 점은 Identity Column도 내부적으로 오라클의 SEQUENCE 객체를 사용하므로 Sequence를 생성할 수 있는 권한이 필요합니다. 만약 권한이 충분하지 않으면 오류가 발생할 수 있으므로 미리 확인하고, 권한이 없으면 'CREATE SEQUENCE' 권한을 부여하면 됩니다.

DROP TABLE IDENTITY_TAB_EX PURGE;

CREATE TABLE IDENTITY_TAB_EX (
SID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, DES NVARCHAR2(50)
);

새로운 테이블(IDENTITY_TAB_EX)를 생성하였습니다. 이 테이블은 Identity Column인 SID 컬럼과 DES 컬럼으로 구성됩니다. 이 테이블에서 SID 컬럼은 기본키로 정의했습니다.
그럼 다음 단계로 IDENTITY_TAB_EX 테이블에 새로운 데이터를 삽입해보겠습니다.

INSERT INTO IDENTITY_TAB_EX (DES) VALUES ('테스트01');
INSERT INTO IDENTITY_TAB_EX (DES) VALUES ('테스트02');
COMMIT;

INSERT INTO IDENTITY_TAB_EX (SID, DES) VALUES ('테스트10');

앞에서부터 2개의 구문은 정상적으로 수행되지만, 3번째 구문은 ORA-32795 에러가 발생합니다. 에러 메시지를 확인해보면, GENERATED ALWAYS 구문으로 생성된 Identity Column에는 데이터를 삽입할 수 없습니다.

만약 SID 컬럼의 값이 10이고, DES 컬럼에 '테스트10'을 입력하려면 어떻게 해야 할까요?

ALTER TABLE IDENTITY_TAB_EX MODIFY (SID NUMBER GENERATED BY DEFAULT AS IDENTITY);
INSERT INTO IDENTITY_TAB_EX (SID, DES) VALUES (10. '테스트10');
COMMIT;

원하는 데이터가 정상적으로 등록되는 것을 확인할 수 있습니다. 하지만 앞에서도 언급했듯이 필자는 이 방법을 사용하는 것을 추천하지 않습니다. 왜냐하면, 대체 키의 특성을 잃어버릴 가능성이 높기 때문입니다.

마지막으로 한 가지 궁금한 사항이 있습니다. 만약 다음에 입력할 SID 값이 무엇인지 궁금하면 어떻게 알아볼 수 있을까요? 어려운 문제인 듯 하지만 간단하게 알아볼 수 있습니다. Identity Column은 오라클의 SEQUENCE 객체를 사용하는 것에서 답을 얻을 수 있습니다.

SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS;

앞 단계에서 생성한 테이블과 오라클에서 생성한 SEQUENCE 객체(ISEQ$$_numbers)가 있음을 확인할 수 있습니다. 기존 SEQUENCE 객체와 마찬가지로 CURRVAL 명령을 이용하여 현재 값을 얻을 수 있습니다. 예를 들어, ISEQ$$_112367일 때 다음 질의로 조회하실 수 있습니다.