자동값 증가(Oracle, MySQL)(IDENTITY, AUTO_INCREMENT)

순서

1. Oracle

   1.1 IDENTITY

      1.1.1 실습

      1.1.2 IDENTITY 초기화

2. MySQL

   2.1 AUTO_INCREMENT

      2.1.1 실습

      2.1.2 AUTO_INCREMENT 초기화

 

1. Oracle

1.1 IDENTITY 사용

 기존 Oracle DB에서 자동 증가를 구현하기 위해서는 시퀀스(SEQUENCE), 트리거(TRIGGER)를 사용해야 했습니다. Oracle 12c 이후부터 제공된 기능입니다. 기본적인 형식은 다음과 같습니다.

 

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

 

IDENTITY COLUMN 기능은 다음과 같은 특징이 있습니다.

 

- 테이블에 하나의 IDENTITY COLUMN 만을 가집니다.

- NUMBER 형의 데이터 타입만을 정의 합니다.

- 암호화 알고리즘을 적용할 수 있습니다.

- CTAS(Create Table As Select) 명령으로 테이블을 생성한 컬럼은 IDENTITY COLUMN 속성을 상속받지 않으며, 단순한 NUMBER 데이터 타입으로 변환합니다. 

 

1.1.1 실습

 자동 증가 값 테스트를 위해서 2개의 컬럼(id, name)을 가진 테이블(test)을 만들겠습니다. id 컬럼에 IDENTITY를 적용할 것입니다.

 

 
create table test(
id int generated always as IDENTITY ,
name varchar(50)
);
 

 

그림 1

데이터를 name 컬럼(Column)만 다음과 같이 삽입합니다.

 

 
insert into test(name) values ('test1');
insert into test(name) values ('test2');
insert into test(name) values ('test3');
insert into test(name) values ('test4');
insert into test(name) values ('test5');
 

 

그림 2

 

select 문을 통해 테이블의 데이터를 보면 id값이 자동으로 증가한 것을 볼 수 있습니다.

 

 
select * from test;
 

 

 

1.1.2 IDENTITY 값 초기화

 IDENTITY값을 초기화하기 위한 명령어입니다.

 

 
ALTER TABLE [테이블명] MODIFY(컬럼명 GENERATED AS IDENTITY (START WITH 초기화값));
 

 

IDENTITY값 초기화 실습

 

 위에서 실습한 test 테이블로 IDENTITY값 초기화  테스트를 해보겠습니다.

 

 먼저 기존 행(row)들을 모두 삭제하고 삭제되었는지 확인합니다.

 

 
delete from test;
select * from test;
 

 

그림 4

 

 새로운 값을 삽입하고 어떠한 값이 들어왔는지 확인해봅니다. id 컬럼(Column)값이 지워지기 전 마지막 값 5에서 +1된 값이 들어오는 것을 확인할 수 있습니다.

 

 
insert into test(name) values ('test1');
insert into test(name) values ('test2');
insert into test(name) values ('test3');
 
select * from test;
 

 

그림 5

 

 삽입한 값을 지웁니다.

 

 
delete from test;
select * from test;
 

 

그림 6

 

 아래의 명령어를 통해서 증가 값을 1로 다시 초기화합니다.

 

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

 

그림 7

 

 데이터를 삽입하여 id 컬럼(Column)에 1부터 데이터가 삽입되는지 확인합니다. 

 

 
insert into test(name) values ('test1');
insert into test(name) values ('test2');
insert into test(name) values ('test3');
 
select * from test;
 

 

그림 8

 

 

 

 

2. MySQL

2.1 AUTO_INCREMENT

 

 삽입할 데이터 값을 자동으로 증가시켜주는 MySQL에서 제공해주는 기능입니다.  사용방법은 테이블을 생성할 때 값을 자동으로 증가시켜주고 싶은 컬럼(Column)의 속성으로 주면 됩니다.

 

 auto_increment의 주의할 점은 오직 기본키(primary key) 컬럼(Column)에만 해당 속성을 부여할 수 있다는 점입니다. 

 

create table 테이블명(
컬럼명 데이터타입 autu_increment
);

 

2.1.1 실습

 자동 증가 값 테스트를 위해서 2개의 컬럼(id, name)을 가진 테이블(test)을 만들겠습니다. id 컬럼에 auto_increment를 적용할 것입니다. 

 

 
create table test(
    id int auto_increment primary key,
    name varchar(50)
);
 

 

그림 9

 

 Insert 문을 사용하여 데이터를 삽입 후 정상적으로 증가된 값이 삽입되는지 확인합니다.

 

 
insert into test (name) values
('test1'),
('test2'),
('test3'),
('test4'),
('test5');
 
select * from test;
 

 

그림 10

 

2.1.2 AUTO_INCREMENT 초기화

 auto_increment 값을 초기화하기 위한 명령어입니다.

 

 
ALTER TABLE [TABLE명] AUTO_INCREMENT = [시작할 값];
 

 

AUTO_INCREMENT 초기화 실습

 

 앞서 실습하였던 test 테이블을 활용하여 auto_increment 초기화를 실습해보겠습니다.

 

먼저 기존에 삽입된 행(Row)을 지우고 정상적으로 값이 존재하지 않는지 확인합니다.

 

 
delete from test;
select * from test;
 

 

그림 11

 

 데이터를 삽입하여 id값이 어떠한 값이 삽입되는지 확인합니다. 확인을 해보면 기존에 있었던 마지막 값 +1이 지속적으로 삽입되는 것을 확인할 수 있습니다.

 

 
insert into test (name) values
('test1'),
('test2'),
('test3'),
('test4'),
('test5');
 
select * from test;
 

 

그림 12

 

 먼저 새로 삽입한 행(Row)을 다시 지웁니다.

 

 
delete from test;
select * from test;
 

 

그림 13

 

 auto_increment값을 초기화하는 명령어를 실행합니다.

 

 
alter table test auto_increment = 1;
 

 

 새로운 행들을 삽입하여 초기화된 값이 삽입되는지 확인합니다.

 

 
insert into test (name) values
('test1'),
('test2'),
('test3'),
('test4'),
('test5');
 
select * from test;
 

 

그림 14

 

REFERENCE

IDENTITY

• m.blog.naver.com/kang_sok/221840061270
• ojc.asia/bbs/board.php?bo_table=Oracle11&wr_id=12

 

AUTO_INCREMENT

• dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
• hellogk.tistory.com/32

 

IDENTITY초기화

• stackoverflow.com/questions/39295422/how-to-reset-identity-column-in-oracle

 

auto_increment 초기화

• amaze9001.tistory.com/28

 

DATE

• 2020.02.03