SQLite AUTOINCREMENT

SQLite AUTOINCREMENT

자동 증가하는 정수 기본 키를 만들고 싶을 때 AUTOINCREMENT 키워드를 떠올리기 쉬운데요, SQLite에서는 이 키워드가 사실 생각보다 자주 쓰일 필요가 없는 친구예요. 정리하면, INTEGER PRIMARY KEY 컬럼은 AUTOINCREMENT 없이도 자동으로 고유 정수를 부여받아요. AUTOINCREMENT는 그 알고리즘을 바꿔서 "행 ID 재사용 방지"라는 다른 보장을 추가하는 역할을 해요. 뭐가 다른지 중심으로 설명할게요.

출처: https://www.sqlite.org/autoinc.html

핵심 요약

  1. AUTOINCREMENT 키워드는 CPU·메모리·디스크 공간·디스크 I/O 오버헤드를 추가로 요구하므로, 꼭 필요하지 않으면 피하는 게 좋아요. 보통은 필요하지 않아요.
  2. SQLite에서 INTEGER PRIMARY KEY 타입 컬럼은 ROWID의 별칭이에요(WITHOUT ROWID 테이블 제외). ROWID는 항상 64비트 부호 있는 정수예요.
  3. INSERT 시 ROWID 또는 INTEGER PRIMARY KEY 컬럼에 값을 명시하지 않으면, 사용되지 않는 정수(보통 현재 최대 ROWID보다 1 큰 값)가 자동으로 채워져요. 이는 AUTOINCREMENT를 쓰든 안 쓰든 동일해요.
  4. INTEGER PRIMARY KEY 뒤에 AUTOINCREMENT가 붙으면, 자동 ROWID 할당 알고리즘이 바뀌어 데이터베이스 수명 동안 ROWID 재사용을 방지해요. 즉 AUTOINCREMENT의 목적은 이전에 삭제된 행의 ROWID 재사용을 막는 거예요.

ROWID와 INTEGER PRIMARY KEY

SQLite 테이블의 행은 보통 64비트 부호 있는 정수 ROWID를 가지며, 같은 테이블 안에서 고유해요(WITHOUT ROWID 제외). ROWID는 ROWID, _ROWID_, OID 같은 특별한 컬럼 이름으로 접근할 수 있어요.

테이블에 INTEGER PRIMARY KEY 타입 컬럼이 있으면 그 컬럼이 ROWID의 별칭이 돼요. ROWID를 INTEGER PRIMARY KEY 컬럼 이름으로도 접근할 수 있고, 네 가지 이름 모두 호환돼요.

INSERT 시 ROWID를 명시하지 않거나 NULL이면 데이터베이스 엔진이 ROWID를 자동 생성해요. 보통 알고리즘은 삽입 전 테이블에서 가장 큰 ROWID보다 1 큰 값을 사용해요. 빈 테이블이면 1을 쓰고요. 최대 ROWID가 최대 정수(9223372036854775807)에 도달하면 사용되지 않은 양수 후보를 무작위로 찾아요. 남는 ROWID를 찾지 못하면 SQLITE_FULL 오류로 실패해요.

이 기본 알고리즘은 최대 ROWID를 쓰지 않고, 가장 큰 ROWID를 가진 행을 삭제하지 않는 한 단조 증가하는 고유 ROWID를 만들어요. 행을 삭제하거나 최대 ROWID를 만들면 이전에 삭제된 행의 ROWID가 재사용될 수 있고, 새 ROWID가 엄격히 오름차순이 아닐 수 있어요.

AUTOINCREMENT 키워드

컬럼 타입이 INTEGER PRIMARY KEY AUTOINCREMENT면 조금 다른 ROWID 선택 알고리즘이 사용돼요. 새 행에 고르는 ROWID는 같은 테이블에 이제까지 존재했던 가장 큰 ROWID보다 최소 1 크다는 보장이 있어요. 테이블에 데이터가 없었으면 1을 써요.

SQLite는 가장 큰 ROWID를 sqlite_sequence라는 내부 테이블로 추적해요. AUTOINCREMENT 컬럼이 있는 일반 테이블이 만들어질 때 이 테이블이 없으면 자동 생성되고, AUTOINCREMENT 테이블을 처음 쓸 때 행이 만들어져 이후 쓰기마다 갱신돼요. 이 테이블은 일반 UPDATE·INSERT·DELETE로 수정할 수 있지만, 그러면 AUTOINCREMENT 키 생성이 흐트러질 수 있으니 정말 알고 쓰지 않는 한 건드리지 말아야 해요.

중요한 차이: "단조 증가"가 "항상 정확히 1씩 증가"를 의미하지는 않아요. 예를 들어 유니크 제약으로 INSERT가 실패하면 실패한 시도의 ROWID가 이후 삽입에서 재사용되지 않아 간극이 생길 수 있어요. AUTOINCREMENT는 자동 선택 ROWID가 증가하도록 보장하지만, 연속적(sequential)이라고는 보장하지 않아요.

AUTOINCREMENT는 ROWID 선택 알고리즘을 바꾸는 키워드이므로, WITHOUT ROWID 테이블이나 INTEGER PRIMARY KEY가 아닌 컬럼에는 사용할 수 없어요. 이를 시도하면 오류가 나요.

더 알아보기