SQLite | 테이블 (Table) | AUTOINCREMENT을 설정했을 때 값의 할당 규칙
컬럼에 INTEGER PRIMARY KEY와 AUTOINCREMENT를 같이 설정되면 어떻게 자동으로 값이 설정되는 되는지에 대해 설명한다. 그리고 현재까지 할당된 최대 값을 확인하는 방법을 함께 알아 본다.
AUTOINCREMENT을 설정했을 때 값의 할당 규칙
컬럼에 INTEGER PRIMARY KEY를 설정되면 데이터를 추가될 때마다 컬럼 값을 따로 지정하지 않으면 자동으로 값이 할당되어 저장된다. 자동으로 저장되는 값은 대상의 컬럼에 저장된 최대 값에 1을 더한 값이다. 이 값이 이전에 할당된 적이 있었는지와는 관계 없이 데이터의 추가와 삭제를 반복되면 이전에 저장된 적이 있는 값이 다시 컬럼에 저장되기도 한다.
그 반해 컬럼에 INTEGER PRIMARY KEY와 AUTOINCREMENT를 같이 설정하면 자동으로 설정되는 값의 규칙이 달라진다. 대상의 컬럼에 현재 저장되어 있는 최대 값에 1이 추가되는 것이 아니라 해당 컬럼에 현재까지 할당된 최대 값에 1이 더해진 값이 저장되게 된다.
INTEGER PRIMARY KEY에 AUTOINCREMENT를 같이 설정하려면 다음과 같이 작성한다.
CREATE TABLE 테이블명 (컬럼명 INTEGER PRIMARY KEY AUTOINCREMENT ...);
데이터가 남아 있는지 여부는 관계가 없기 때문에, 자동으로 설정되는 값은 항상 현재까지의 할당된 값의 최대 값이다. 현재까지 저장되었던 적이 있는 값이 자동으로 저장되는 것은 아니다.
그럼 직접 해보도록 하겠다. 우선 AUTOINCREMENT을 지정하지 않으면 어떻게 되는지 확인해 본다. 다음과 같이 테이블을 만든다.
create table user (id integer primary key, name text);
sqlite>
sqlite> create table user (id integer primary key, name text);
sqlite>
3개의 데이터를 추가한다. INTEGER PRIMARY KEY를 설정한 컬럼에는 값을 지정하지 않았기에 값이 자동으로 저장된다.
insert into user (name) values ('devkuma');
insert into user (name) values ('kimkc');
insert into user (name) values ('araikuma');
sqlite>
sqlite> insert into user (name) values ('devkuma');
sqlite> insert into user (name) values ('kimkc');
sqlite> insert into user (name) values ('araikuma');
sqlite>
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select * from user;
id name
---------- ----------
1 devkuma
2 kimkc
3 araikuma
sqlite>
ID 컬럼에서 가장 큰 값이 들어있는 데이터(ID 컬럼의 값이 3 데이터)를 삭제한다.
delete from user where id = 3;
sqlite>
sqlite> delete from user where id = 3;
sqlite>
sqlite> select * from user;
id name
---------- ----------
1 devkuma
2 kimkc
sqlite>
그러고 다시 데이터를 추가해 본다. 이때 INTEGER PRIMARY KEY를 설정한 컬럼에 값을 지정하지 않으면 대상 컬럼에 저장되는 최대 값(현재는 2)에 1을 더한 값이 자동으로 설정되어 컬럼에 자동으로 설정되는 값은 3이다.
insert into user (name) values ('happykuma');
sqlite> insert into user (name) values ('happykuma');
sqlite>
sqlite> select * from user;
id name
---------- ----------
1 devkuma
2 kimkc
3 happykuma
sqlite>
위와 같이 이전에 한 번 다른 데이터에 할당된 값이어도 그와 관계없이 최대 값에 1을 더한 값을 컬럼에 설정된다.
그럼 다음은 AUTOINCREMENT을 같이 지정보자. 다음과 같이 테이블을 만든다.
create table user2 (id integer primary key autoincrement, name text);
sqlite>
sqlite> create table user2 (id integer primary key autoincrement, name text);
sqlite>
앞에서 살펴본 바와 같이 3개의 데이터를 추가한다. INTEGER PRIMARY KEY AUTOINCREMENT를 설정한 컬럼에 값을 지정하지 않으므로 값이 자동으로 저장된다.
insert into user2 (name) values ('devkuma');
insert into user2 (name) values ('kimkc');
insert into user2 (name) values ('araikuma');
sqlite> insert into user2 (name) values ('devkuma');
sqlite> insert into user2 (name) values ('kimkc');
sqlite> insert into user2 (name) values ('araikuma');
sqlite>
sqlite> select * from user2;
id name
---------- ----------
1 devkuma
2 kimkc
3 araikuma
sqlite>
ID 컬럼에서 가장 큰 값이 들어있는 데이터(ID 컬럼의 값이 3 데이터)를 삭제한다.
delete from user1 where id = 3;
sqlite>
sqlite> delete from user2 where id = 3;
sqlite>
sqlite> select * from user2;
id name
---------- ----------
1 devkuma
2 kimkc
sqlite>
그러고 다시 데이터를 추가해 본다. 이때 INTEGER PRIMARY KEY를 설정한 컬럼에 값을 지정하지 않으면 대상 컬럼에 저장되는 최대 값(현재는 2)에 1을 더한 값이 자동으로 설정되어 컬럼에 자동으로 설정되는 값은 3이다.
그러고 다시 데이터를 추가해 본다. 이때 INTEGER PRIMARY KEY AUTOINCREMENT를 설정한 컬럼에 값을 지정하지 않으면 대상 컬럼에 지금까지 저장된 수있는 최대 값(현재 3)에 1을 더한 값이 자동으로 설정되기 때문에 4가 저장된다.
insert into user2 (name) values ('happykuma');
sqlite> insert into user2 (name) values ('happykuma');
sqlite>
sqlite> select * from user2;
id name
---------- ----------
1 devkuma
2 kimkc
4 happykuma
이렇게 AUTOINCREMENT을 설정하면 이전에 설정된 적이 있는 값이 다시 사용되는 것을 방지 할 수 있다.
컬럼에 현재까지 할당된 최대 값 확인
AUTOINCREMENT이 컬럼에 설정되어 있는 경우, 삭제된 데이터를 포함하여 과거에 대상의 컬럼에 현재까지 할당된 최대 값을 참조하게 되는데, 이 값은 SQLite가 자동으로 생성하는 특별한 테이블인 sqlite_sequence 테이블에 저장되어 있다.
sqlite_sequence 테이블의 스키마를 확인해 보면 name과 seq 두 컬럼이 포함되어 있는 것을 알 수 있다.
.schema sqlite_sequence
sqlite> .schema sqlite_sequence
CREATE TABLE sqlite_sequence(name,seq);
sqlite>
name에는 테이블명, seq는 INTEGER PRIMARY KEY AUTOINCREMENT가 설정된 컬럼에 과거에 할당된 최대 값이 저장된다. 그럼 방금 사용했던 user2 테이블의 최대 값을 확인하여 보자.
select * from sqlite_sequence where name = 'user2';
sqlite> select * from sqlite_sequence where name = 'user2';
name seq
---------- ----------
user2 4
sqlite>
현재 user2 테이블에 INTEGER PRIMARY KEY AUTOINCREMENT가 설정된 컬럼에 지금까지 할당된 최대 값이 4 인 것을 확인할 수 있다.