자동값 증가(Oracle, MSSQL, MySQL)

 

1. 자동값 증가가 필요한 경우

---

데이터베이스에 데이터가 저장될 때 자동으로 1부터 +1씩 증가하는 숫자가 자동으로 입력되는 상황을 말합니다. 

 

스키마를 작성하다보면 데이터의 수를 p.k로 설정해야 할 때가 있습니다. 이럴 경우 데이터를 insert 할 때마다 max함수로 기존에 추가되어있는 최대값을 알아야 p.k값이 겹치지 않게 추가를 할 수 있지만 insert마다 최댓값을 받아오는 sql문을 작성하는 것은 비효율적입니다. 테이블 생성 시 자동 값 증가 옵션을 사용하여 생성한다면, 이러한 비효율을 방지할 수 있습니다. 

 

예를 들어 회원가입을 할때마다 회원 명단 테이블에 순차적으로 회원 번호가 증가되는 경우입니다.

 

 

 

 

 

 

 

2. 데이터베이스 별 자동값 증가 방법

---

1. Oracle
   - SEQUENCE 사용
   1-1. SEQUENCE 생성하여 필요한 부분에서 불러 사용합니다.
   CREATE SEQUENCE [시퀸스 이름];
   

   //시퀸스 생성
   CREATE SEQUENCE TB_BOARD_SEQ ;
   
   //시퀸스 생성 시 옵션 사항
   CREATE SEQUENCE 시퀀스이름
     [INCREMENT BY number]				//INCREMENT : 시퀀스의 자동 증가값. 제외시 1씩 증가
     [START WITH number]				//START : 시퀀스의 시작 값. 제외시 1부터 시작
     [MAXVALUE number or NOMAXVALUE]		//MAXVALUE : 시퀀스 최대값, NOMAXVALUES : 최대값 제한 없음
     [MINVALUE number or NOMINVALUE]		//MINVALUE : 시퀀스 최소값, NOMINVALUE : 최소값 제한 없음
     [CYCLE or NOCYCLE]				//CYCLE : 최대값이 지나면 다시 시작, NOCYCLE : 최대값이 지나면 다시 시작 안함
     [CACHE or NOCACHE] ;				//CACHE : 시퀀스값을 메모리에 할당, NOCACHE : 메모리에 할당하지 않음
   
   
   //예시
   CREATE SEQUENCE TB_BOARD_SEQ
        INCREMENT BY 2
        START WITH 10001
        NOMAXVALUES ;
   ​

   
   1-2. 생성한 SEQUENCE를 호출하여 사용합니다.
   INSERT INTO [테이블명] ( [시퀸스 사용할 컬럼] ) VALUES ( [시퀸스 이름].NEXTVAL );
   

   //NEXTVAL : 현재 시퀸스에서 증가된 값
   //CURRVAL : 현재 시퀸스의 값
   
   INSERT INTO TB_BOARD(seq, name) VALUES(TB_BOARD_SEQ.NEXTVAL, 'SON');
   ​​

   
   
   1-3. DROP SEQUENCE를 사용하여 시퀸스를 삭제합니다.
   DROP SEQUENCE [시퀸스 이름]
   

   DROP SEQUENCE TB_BOARD_SEQ ;​

   
   
   
   
   - Oracle 12c 버전부터는IDENTITY 사용 가능
   기존 Oracle DB에서 자동 증가를 구현하기 위해서는 시퀀스(SEQUENCE), 트리거(TRIGGER)를 사용해야 했습니다.
   Oracle 12c에 새롭게 도입된 Identity Column은 대체키(Surrogate key)를 효과적으로 구성하기 위하여 도입된 것입니다.
   
   IDENTITY COLUMN 기능은 다음과 같은 특징이 있습니다.
   - 테이블에 하나의 IDENTITY COLUMN 만을 가집니다.
   - 암호화 알고리즘을 적용할 수 있습니다.
   - CTAS(Create Table As Select) 명령으로 테이블을 생성한 컬럼은 IDENTITY COLUMN 속성을 상속받지 않으며, 단순한 NUMBER 데이터 타입으로 변환합니다. 
   - NUMBER 형의 데이터 타입만을 정의합니다.
   

   //테이블 생성 시 id 컬럼에 IDENTITY 지정
   create table test(
   id int generated always as IDENTITY ,
   name varchar(50)
   );

   
   
   
   ALTER TABLE [테이블명] MODIFY ( [컬럼명] GENERATED AS IDENTITY (START WITH 초기화값) ); 의 형태로 초기화할 수 있습니다.
   

   ALTER TABLE test MODIFY(id GENERATED AS IDENTITY (START WITH 1));​

   
   
   
   
   
   
   
   
   
2. MSSQL
   - 테이블 생성 시
   CREATE TABLE [테이블명] ( [컬럼명] [숫자 데이터 형식] IDENTITY ([초기값], [증가값]) [제약 조건] )의 형식으로 지정합니다.
   

   //1. 테이블 생성
   
   CREATE TABLE EX_TABLE (
      ID INT IDENTITY (1, 1) NOT NULL,
      NAME VARCHAR(10) NOT NULL DEFAULT '',
      AGE INT NULL,
      SCORE INT NULL,
      PRIMARY KEY (ID)
   )
   
   //2. 데이터 삽입
   
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('SON', 29, 95)
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('KANE', 28, 97)
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('ROMERO', 23, 87)
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('JAN', 33, 91)
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('DEMBELE', 32, 100)

   
   - 테이블이 생성된 이후 설정하는 방법
   ALTER TABLE [테이블명] ADD [컬럼명] [숫자 타입] IDENTITY ([초기값], [증가값]) [제약조건]의 형식으로 지정합니다.
   

   //1. 테이블 생성 이후 설정하는 방법 
   ALTER TABLE EX_TABLE ADD ID INT IDENTITY(1, 1) NOT NULL 
   
   //2. 데이터 삽입 
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('SON', 29, 95) 
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('KANE', 28, 97) 
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('ROMERO', 23, 87) 
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('JAN', 33, 91) 
   INSERT INTO EX_TABLE ([NAME],[AGE],[SCORE]) VALUES('DEMBELE', 32, 100)​

   
   
   
   
   
   
   
   
   
3. MySQL
   - 테이블 생성 시 auto_increment 라는 속성을 지정해 줍니다.
   CREATE TABLE [테이블명] ( [컬럼명] [데이터 타입] auto_increment)의 형식으로 지정합니다.
   

   //1. 테이블 생성
   create table test(
       id int auto_increment primary key,
       name varchar(50)
   );​

   
   - auto_increment 값을 초기화 하기 위해서는 ALTER TABLE [테이블명] auto_increment = [시작할 값] 형태로 입력하면 됩니다.
   

   //1. auto_increment 초기화
   ALTER TABLE EX_TABLE auto_increment = 1;​