오라클 식별자 컬럼(Oracle Identity Columns), GENERATED AS IDENTITY

 

오라클 식별자 컬럼(Oracle Identity Columns), GENERATED AS IDENTITY

오라클 12C는 PK 값을 자동으로 증분하면서 생성할 수 있는 식별자컬럼identity column을 도입 했습니다. MySQL의 AUTO_INCREMENT 컬럼 또는 MS SQL Server의 IDENTITY 컬럼과 유사한 테이블의 식별자(ID)를 정의 할 수있는 새로운 방법을 도입한 것이죠.  많은 오라클 관련 개발자들이 아주 오랜기간 바라던 희망사항이 드디어 이루어 진것인데요.

식별자 컬럼은 기본키를 대신 하기에 유용하며 새 행을 삽입하면 오라클이 자동으로 식별자 컬럼의 값을 자동으로 생성하여 컬럼에 순차 값을 삽입합니다.

기존에는 이러한 방법을 지원하지 않아 수많은 개발자들이 오라클 시퀀스oracle sequence를 생성하여 다음과 같은 방법을 사용했습니다.

1. 오라클 시퀀스를 생성 합니다. CREATE SEQUENCE EMP_SEQ INCREMENT BY 1 START WITH 1; 2. 다음과 같은 EMP10 테이블이 있습니다. CREATE TABLE EMP10  (EMPNO NUMBER PRIMARY KEY ,   ENAME VARCHAR2(20)); 3. INSERT문에서는 EMPNO 키값을 자동으로 생성하기 위해 다음처럼 시퀀스를 사용했습니다. INSERT INTO EMP10 VALUES  (EMP_SEQ.NEXTVAL, ‘홍길동1’); INSERT INTO EMP10 VALUES  (EMP_SEQ.NEXTVAL, ‘홍길동2’); 4. 입력된 값을 확인 합니다. SELECT * FROM EMP10; 1 홍길동1 2 홍길동2 시퀀스는 숫자등을 자동 증분하기 위한 오라클의 스키마 객체 입니다. 시퀀스의 다음값을 읽을 때는 시퀀스이름.NEXTVAL, 현재 값을 읽을 때는 시퀀스.CURRVAL로  시퀀스 값을 읽어 내면 됩니다.

[기본형식]

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]  GENERATED ALWAYS : 항상 ID 컬럼의 값을 생성합니다. 그래서 개발자가 ID 컬럼에 값을 입력하면 오류가 발생합니다. GENERATED BY DEFAULT : ID 컬럼 값을 제공하지 않으면 자동 생성하며 값을 제공하면 해당 값을 ID 컬럼에 입력합니다. 이 옵션의 경우 ID 컬럼에 NULL 값을 삽입하면 오류가 발생합니다. GENERATED BY DEFAULT ON NULL : 사용자가 NULL 값을 제공하거나 값이 없는 경우 ID 컬럼에 대한 값을 생성합니다.       identity_option START WITH initial_value는 ID 컬럼에 사용할 초기 값을 지정합니다. 기본 초기 값은 1입니다. INCREMENT BY internval_value는 생성 된 값 사이의 간격을 정의합니다. 기본적으로 간격 값은 1입니다. CACHE는 Oracle이 성능 향상을 위해 미리 생성해야하는 값의 수를  정의합니다.

기본형식을 기본으로 식별자 컬럼을 이용한 테이블을 직접 생성해 보고 실제 데이터도 입력해 보면서 식별자 컬럼에 대해서 이해해 보겠습니다. 

위 박스 안의 오라클 시퀀스를 이용한 예문을 오라클 12C 이후의 식별자 컬럼을 사용하여 재정의 해보겠습니다.

실습

empno, ename 2개의 컬럼을 가지는 EMP10 테이블을 생성 합니다. 물론 empno 컬럼은 식별자 컬럼으로 정의 합니다. EMP10 테이블이 이미 있다면 삭제 후 만드세요.

다음과 같은 조건으로 EMP10 테이블을 생성하세요. 사번(empno) number, generated as identity, primary key 성명(ename) varchar2(50)

CREATE TABLE EMP10 (

     EMPNO NUMBER GENERATED AS IDENTITY 

                                           CONSTRAINTS PK_EMP10 PRIMARY KEY,

     ENAME VARCHAR2(50)

);

<실행결과>

Table EMP10이(가) 생성되었습니다.

empno 컬럼은 식별자 컬럼이므로 값을 입력하지 않습니다. GENERATED AS IDENTITY 옵션은 오라클이 무조건 식별자 컬럼의 값을 생성하므로 값을 입력하면 오류가 발생 합니다.

2건의 데이터를 입력 합니다.

INSERT INTO EMP10(ENAME) VALUES  ('홍길동1');

INSERT INTO EMP10(ENAME) VALUES  ('홍길동2');

입력된 데이터를 확인해 봅시다.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	1	홍길동1
2	2	홍길동2

식별자 컬럼에 값을 직접 입력해 보겠습니다.

INSERT INTO EMP10(empno, ename) VALUES  (3, '홍길동3');

<실행결과>

ORA-32795: generated always ID 열에 삽입할 수 없습니다.

GENERATED AS IDENTITY 옵션으로 생성한 식별자 컬럼은 오라클이 항상 값을 생성하므로 값을 입력하면 오류가 발생 합니다.

실습

식별자 컬럼에 값을 입력하기도 하고 어떨 때는 값을 입력하지 않는 상황들이 발생한다면 GENERATED BY DEFAULT AS IDENTITY 옵션을 사용하면 됩니다. 값을 입력 하지 않으면 오라클이 자동으로 값을 생성하고, 값을 입력하면 입력된 값을 사용합니다.

EMP10 테이블을 삭제하고 empno, ename 2개의 컬럼을 가지는 EMP10 테이블을 다시 생성 합니다. 

다음과 같은 조건으로 EMP10 테이블을 생성하세요. 사번(empno) number, generated by default as identity, primary key 성명(ename) varchar2(50)

CREATE TABLE EMP10 (

   EMPNO NUMBER GENERATED BY DEFAULT AS IDENTITY 

                                        CONSTRAINTS PK_EMP10 PRIMARY KEY,

   ENAME VARCHAR2(20)

);

<실행결과>

Table EMP10이(가) 생성되었습니다.

한건의 데이터는 식별자 컬럼에 값을 입력하지 않고 INSERT 합니다.

1건의 데이터를 입력 합니다.

INSERT INTO EMP10(ENAME) VALUES  ('홍길동1');

입력된 데이터를 확인해 봅시다.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	1	홍길동1

이번에는 식별자 컬럼에 값을 직접 입력 합니다.

1건의 데이터를 입력 합니다.

INSERT INTO EMP10(EMPNO, ENAME) VALUES  (2, '홍길동2');

입력된 데이터를 확인해 봅시다.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	1	홍길동1
2	2	홍길동2

실습

GENERATED BY DEFAULT ON NULL AS IDENTITY 옵션은 식별자 컬럼에 값이 없거나 NULL 값이 입력되는 경우에는 오라클이 식별자 컬럼의 값을 자동생성하고 NULL 아닌 값이 입력된다면 이를 입력값으로 받아주는 옵션 입니다.

EMP10 테이블을 삭제하고 empno, ename 2개의 컬럼을 가지는 EMP10 테이블을 다시 생성 합니다. 

다음과 같은 조건으로 EMP10 테이블을 생성하세요. 사번(empno) number, generated by default on null as identity, primary key 성명(ename) varchar2(50)

CREATE TABLE EMP10 (

   EMPNO NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 

                                   CONSTRAINTS PK_EMP10 PRIMARY KEY,

   ENAME VARCHAR2(20)

);

<실행결과>

Table EMP10이(가) 생성되었습니다.

한건의 데이터는 식별자 컬럼에 값을 입력하지 않고 INSERT 합니다.

1건의 데이터를 입력 합니다.

INSERT INTO EMP10(ENAME) VALUES  ('홍길동1');

입력된 데이터를 확인해 봅시다.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	1	홍길동1

이번에는 식별자 컬럼에 값을 직접 입력 합니다.

1건의 데이터를 입력 합니다.

INSERT INTO EMP10(EMPNO, ENAME) VALUES  (2, '홍길동2');

입력된 데이터를 확인해 봅시다.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	1	홍길동1
2	2	홍길동2

이번에는 식별자 컬럼에  NULL 값을 직접 입력 합니다.

1건의 데이터를 입력 합니다.

INSERT INTO EMP10(EMPNO, ENAME) VALUES  (NULL, '홍길동3');

입력된 데이터를 확인해 봅시다.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	1	홍길동1
2	2	홍길동2
3	3	홍길동3

실습

GENERATED AS IDENTITY를 사용하면서 START WITH, INCREMENT BY 옵션을 사용하는 예문을 실습하겠습니다.

EMP10 테이블을 삭제하고 empno, ename 2개의 컬럼을 가지는 EMP10 테이블을 다시 생성 합니다. empno 식별자컬럼의 값은 100부터시작해서 10씩 증가하도록 설계해 보겠습니다.

다음과 같은 조건으로 EMP10 테이블을 생성하세요. 사번(empno) number, generated as identity start with 100, increment bi 10, primary key 성명(ename) varchar2(50)

CREATE TABLE EMP10 (

   EMPNO NUMBER GENERATED AS IDENTITY START WITH 100 INCREMENT BY 10 

                       CONSTRAINTS PK_EMP10 PRIMARY KEY,

   ENAME VARCHAR2(20)

);

<실행결과>

Table EMP10이(가) 생성되었습니다.

식별자 컬럼에는 값을 입력하지 않고 데이터를 입력합니다.

2건의 데이터를 입력 합니다.

INSERT INTO EMP10(ENAME) VALUES  ('홍길동1');

INSERT INTO EMP10(ENAME) VALUES  ('홍길동2');

입력된 데이터를 확인해 봅시다.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	100	홍길동1
2	110	홍길동2

 

#식별자컬럼, #오라클식별자컬럼, #오라클, #Identity칼럼, #오라클교육, #오라클강좌