실행 가능한 SQLite 문서: 기본키

Q: SQLite에서 기본키는 어떻게 정의하나요?

CREATE TABLE 문에서 컬럼 뒤에 PRIMARY KEY를 붙이면 됩니다. 예를 들어 id INTEGER PRIMARY KEY처럼요. 여러 컬럼을 묶어서 키로 만들고 싶다면 테이블 레벨 제약조건으로 PRIMARY KEY (cola, colb)라고 적습니다. 단일 컬럼이든 조합이든, 행 전체에서 유일한 값이어야 한다는 점은 동일합니다.

Q: INTEGER PRIMARY KEY는 다른 기본키와 뭐가 다른가요?

INTEGER PRIMARY KEY는 좀 특별합니다. 테이블에 기본 내장된 rowid의 별칭(alias)이 되어서, 별도 인덱스 없이 B-tree에 곧바로 저장돼요. 반면 다른 타입을 쓰거나 복합키로 만들면 별도의 unique 인덱스가 추가로 생성됩니다. 단일 컬럼 숫자 ID라면 INTEGER PRIMARY KEY가 속도도 빠르고 용량도 작습니다.

Q: SQLite 기본키에 AUTOINCREMENT를 꼭 붙여야 하나요?

대부분은 안 붙여도 됩니다. INTEGER PRIMARY KEY 컬럼은 NULL을 넣어 INSERT하면 알아서 유일한 rowid를 할당해줘요. AUTOINCREMENT는 "삭제된 ID를 절대 재사용하지 않는다"는 보장만 추가로 주는 옵션이고, 대신 sqlite_sequence라는 내부 테이블을 하나 더 관리해야 합니다. 단조 증가 ID가 꼭 필요한 게 아니라면 굳이 쓰지 않아도 됩니다.

Q: 기본키인데 왜 NULL 값이 들어가나요?

역사적인 버그인데 호환성 때문에 그대로 남아 있는 동작입니다. 일반 테이블에서 INTEGER가 아닌 기본키 컬럼은, 명시적으로 NOT NULL을 붙이지 않으면 NULL을 허용해버려요. 예외는 INTEGER PRIMARY KEY 컬럼뿐인데 이건 절대 NULL을 받지 않습니다. 안전하게 가려면 모든 기본키 컬럼에 NOT NULL을 명시하거나, 규칙이 제대로 적용되는 STRICT 테이블을 쓰는 걸 권장합니다.

기본키(Primary Key)는 어떤 역할을 할까

SQLite 기본키는 테이블의 각 행을 고유하게 식별해 주는 컬럼(또는 컬럼 조합)입니다. 같은 기본키 값을 가진 행이 두 개 존재할 수는 없습니다. 이 규칙은 SQLite가 직접 강제로 지켜 주며, 행을 빠르게 찾을 때도 이 키를 활용합니다.

가장 간단한 형태는 컬럼 정의에 인라인으로 붙이는 방식입니다:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');

SELECT * FROM users;

id를 직접 넣지 않았는데도 SQLite가 알아서 채워줬다. 마법이 아니라, INTEGER PRIMARY KEY에만 적용되는 특별한 동작이다. 본격적인 코드를 작성하기 전에 이 부분을 짚고 넘어가자.

INTEGER PRIMARY KEY는 뭐가 특별할까

대부분의 DB에서 sqlite 기본키는 그냥 유니크 인덱스에 불과하다. 하지만 SQLite는 다르다. 일반 테이블이라면 내부적으로 행을 식별하는 64비트 정수 rowid가 이미 숨어 있다. 여기서 컬럼을 정확히 INTEGER PRIMARY KEY로 선언하면, 그 컬럼이 곧 rowid가 된다. 별도의 인덱스도, 추가 저장 공간도 필요 없다. 즉, id 값과 행의 물리적 위치가 동일한 것이다.

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('첫 번째'), ('두 번째'), ('세 번째');

SELECT id, rowid, title FROM posts;

id와 rowid는 사실상 같은 컬럼을 두 이름으로 부르는 것뿐입니다. id로 조회하면 곧바로 해당 행으로 가기 때문에, 별도의 인덱스 트리를 한 번 더 타고 갈 필요가 없습니다. 그래서 SQLite에서 숫자형 기본키를 쓸 때 권장되는 방식은 정해져 있습니다. INTEGER PRIMARY KEY라고 정확히 적는 것입니다. INT도 안 되고, BIGINT도 안 됩니다. INTEGER NOT NULL PRIMARY KEY는 동작하긴 하지만, 어쨌든 타입은 반드시 INTEGER여야 합니다.

다른 타입을 써도 동작은 합니다. 다만 별도의 유니크 인덱스가 따로 생성되기 때문에, 문제는 없지만 그만큼 컴팩트하지는 않습니다.

AUTOINCREMENT는 대부분 필요하지 않습니다

다른 DB를 쓰던 사람들이 습관처럼 id INTEGER PRIMARY KEY AUTOINCREMENT라고 적는 경우가 많습니다. 그런데 SQLite의 AUTOINCREMENT 키워드는 이름에서 풍기는 느낌보다 훨씬 좁은 일을 하고, 대부분의 상황에서는 굳이 쓸 필요가 없습니다.

AUTOINCREMENT 없이 INTEGER PRIMARY KEY 컬럼만 선언해 두면, 현재 존재하는 가장 큰 rowid에 1을 더한 값이 자동으로 채워집니다. 다만 마지막 행을 삭제했을 경우, 다음 insert에서 그 id가 재사용될 수 있다 는 점은 알아두어야 합니다.

AUTOINCREMENT를 붙이면, SQLite는 지금까지 사용된 가장 큰 id 값을 sqlite_sequence라는 별도 테이블에 기록해 두고, 행을 삭제해도 그 값을 절대 다시 쓰지 않습니다.

CREATE TABLE plain (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE strict_id (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

INSERT INTO plain (label) VALUES ('a'), ('b'), ('c');
INSERT INTO strict_id (label) VALUES ('a'), ('b'), ('c');

DELETE FROM plain WHERE id = 3;
DELETE FROM strict_id WHERE id = 3;

INSERT INTO plain (label) VALUES ('new');
INSERT INTO strict_id (label) VALUES ('new');

SELECT 'plain' AS table_name, id, label FROM plain
UNION ALL
SELECT 'strict_id', id, label FROM strict_id;

plain 테이블은 id 3을 재사용했지만, AUTOINCREMENT를 적용한 테이블은 4로 건너뛰었습니다. 감사 로그를 남겨야 한다거나 삭제 후에도 남아 있는 외부 참조가 있다거나 하는 명확한 이유가 없다면, AUTOINCREMENT는 굳이 쓰지 않는 편이 좋습니다. INSERT마다 쓰기가 한 번씩 더 발생하고, 별도의 관리 테이블까지 유지해야 하니까요.

SQLite 복합키 만들기

컬럼 하나만으로는 부족할 때가 있습니다. 예를 들어 사용자와 역할을 연결하는 조인 테이블이라면 (user_id, role_id) 쌍 으로 행을 유일하게 식별하게 되죠. 이런 경우에는 기본키를 테이블 레벨에서 선언합니다:

CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    granted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10), (1, 20), (2, 10);
-- 이 행은 복합 키를 위반합니다:
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);

한 쌍 자체가 테이블 전체에서 유일해야 한다는 뜻이에요. 즉 (1, 10)은 딱 한 번만 등장할 수 있습니다. 각 컬럼만 따로 보면 얼마든지 중복돼도 괜찮고요. 이게 핵심입니다. 한 사용자가 여러 역할을 가질 수 있고, 한 역할도 여러 사용자에게 부여될 수 있지만, 특정 사용자-역할 조합은 최대 한 번만 존재한다는 거죠.

sqlite 복합키를 정의하면 지정한 컬럼들을 묶은 별도의 인덱스가 만들어집니다. 단, 이 인덱스가 rowid를 대체하지는 않습니다. rowid 자리를 차지하는 건 오로지 INTEGER PRIMARY KEY 하나뿐이에요.

기본키에 NULL이 들어가는 함정

PostgreSQL이나 MySQL을 쓰다가 SQLite로 넘어온 분들이 깜짝 놀라는 부분이 있습니다. 일반 SQLite 테이블에서는 INTEGER PRIMARY KEY가 아닌 기본키 컬럼에 NULL 값이 들어갈 수 있다는 점이에요. 오래전부터 있던 버그인데, 하위 호환성 때문에 SQLite 개발진이 그대로 둔 동작입니다.

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', '사과');
INSERT INTO products (sku, name) VALUES (NULL, '미스터리 1');
INSERT INTO products (sku, name) VALUES (NULL, '미스터리 2');

SELECT * FROM products;

NULL 값을 가진 행 두 개가 기본키 제약을 그냥 통과해 버렸습니다. 해결 방법은 간단합니다. INTEGER가 아닌 기본키 컬럼에는 모두 NOT NULL을 명시적으로 붙여주면 됩니다:

CREATE TABLE products (
    sku   TEXT NOT NULL PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Apple');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystery');  -- 이제 거부됨

또 다른 방법은 STRICT 테이블을 사용하는 것입니다. STRICT 테이블에서는 기본키에 NULL이 들어가는 버그가 수정되어 있습니다. 그래도 모든 기본키 컬럼에 NOT NULL을 함께 적어두는 습관은 일종의 보험이라고 생각하면 됩니다.

sqlite 기본키 vs UNIQUE 제약

둘 다 중복을 막아주지만, 차이점이 분명히 있습니다.

한 테이블의 기본키(primary key)는 하나 뿐이지만, UNIQUE 제약은 여러 개 걸 수 있습니다.
기본키는 그 테이블의 "대표" 식별자입니다. 외래 키(foreign key)는 기본적으로 이 기본키를 참조합니다.
INTEGER PRIMARY KEY로 선언한 컬럼은 rowid가 되지만, UNIQUE만 걸린 정수 컬럼은 그렇지 않습니다.
UNIQUE 컬럼은 여러 개의 NULL을 아무렇지 않게 허용합니다(각 NULL을 서로 다른 값으로 취급하기 때문).

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    email    TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL UNIQUE
);

INSERT INTO accounts (email, username) VALUES ('a@x.com', 'ada');
INSERT INTO accounts (email, username) VALUES ('b@x.com', 'boris');
SELECT * FROM accounts;

id는 그 행의 정체성을 나타내는 값입니다. email이나 username도 고유하긴 하지만, 어디까지나 비즈니스 속성에 해당합니다. 이런 값들은 언제든 바뀔 수 있는 반면, id는 바뀌어선 안 됩니다.

나중에 기본키 추가하기 (가급적 하지 마세요)

SQLite의 ALTER TABLE은 기능이 제한적입니다. ALTER TABLE ... ADD PRIMARY KEY 같은 구문은 아예 존재하지 않아요. 만약 테이블을 만들 때 기본키를 깜빡했고 이미 데이터가 들어 있다면, 테이블을 새로 만드는 수밖에 없습니다:

CREATE TABLE events (
    name TEXT,
    occurred_at TEXT
);
INSERT INTO events VALUES ('login', '2026-01-01'), ('logout', '2026-01-02');

-- 기본 키를 포함하여 재구성:
CREATE TABLE events_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);
INSERT INTO events_new (name, occurred_at) SELECT name, occurred_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

SELECT * FROM events;

이게 바로 SQLite에서 흔히 쓰는 마이그레이션 패턴입니다. 실제 코드에서는 트랜잭션으로 감싸고, 다른 테이블이 이 테이블을 참조하고 있다면 잠깐 외래 키를 꺼두는 게 좋습니다. 핵심은 이거예요. CREATE TABLE을 작성하는 그 시점에 기본키를 제대로 정해두자.

체크리스트로 정리하기

새로운 테이블을 만들 때마다 이렇게 자문해 보세요.

이 행에 자연스럽게 식별자로 쓸 만한 값이 있는가? 그게 정수 하나라면 INTEGER PRIMARY KEY를 쓰세요.
사실은 여러 컬럼의 조합이 식별자 역할을 하는가(예: 조인 테이블)? 그렇다면 테이블 레벨에서 PRIMARY KEY (col_a, col_b) 형태의 sqlite 복합키를 사용하세요.
키가 문자열이나 정수가 아닌 다른 타입인가? NOT NULL을 빼먹지 말고 명시적으로 붙이세요.
정말로 AUTOINCREMENT가 필요한가? 십중팔구 필요 없습니다.
정수가 아닌 기본키를 쓰면서 작고 읽기 위주인 테이블인가? 그렇다면 WITHOUT ROWID를 한번 검토해 보세요(다음 rowid 문서에서 다룹니다).

다음 주제: rowid

지금까지 INTEGER PRIMARY KEY를 "rowid의 별칭"이라고 짧게 언급했지만, 사실 rowid는 SQLite의 모든 일반 테이블 아래에 깔려 있는 기반입니다. 한 번 제대로 짚고 넘어갈 가치가 있죠. 그 이야기를 다음 페이지에서 이어가겠습니다.

자주 묻는 질문

SQLite에서 기본키는 어떻게 정의하나요?

CREATE TABLE 문에서 컬럼 뒤에 PRIMARY KEY를 붙이면 됩니다. 예를 들어 id INTEGER PRIMARY KEY처럼요. 여러 컬럼을 묶어서 키로 만들고 싶다면 테이블 레벨 제약조건으로 PRIMARY KEY (col_a, col_b)라고 적습니다. 단일 컬럼이든 조합이든, 행 전체에서 유일한 값이어야 한다는 점은 동일합니다.

INTEGER PRIMARY KEY는 다른 기본키와 뭐가 다른가요?

INTEGER PRIMARY KEY는 좀 특별합니다. 테이블에 기본 내장된 rowid의 별칭(alias)이 되어서, 별도 인덱스 없이 B-tree에 곧바로 저장돼요. 반면 다른 타입을 쓰거나 복합키로 만들면 별도의 unique 인덱스가 추가로 생성됩니다. 단일 컬럼 숫자 ID라면 INTEGER PRIMARY KEY가 속도도 빠르고 용량도 작습니다.

SQLite 기본키에 AUTOINCREMENT를 꼭 붙여야 하나요?

대부분은 안 붙여도 됩니다. INTEGER PRIMARY KEY 컬럼은 NULL을 넣어 INSERT하면 알아서 유일한 rowid를 할당해줘요. AUTOINCREMENT는 "삭제된 ID를 절대 재사용하지 않는다"는 보장만 추가로 주는 옵션이고, 대신 sqlite_sequence라는 내부 테이블을 하나 더 관리해야 합니다. 단조 증가 ID가 꼭 필요한 게 아니라면 굳이 쓰지 않아도 됩니다.

기본키인데 왜 NULL 값이 들어가나요?

역사적인 버그인데 호환성 때문에 그대로 남아 있는 동작입니다. 일반 테이블에서 INTEGER가 아닌 기본키 컬럼은, 명시적으로 NOT NULL을 붙이지 않으면 NULL을 허용해버려요. 예외는 INTEGER PRIMARY KEY 컬럼뿐인데 이건 절대 NULL을 받지 않습니다. 안전하게 가려면 모든 기본키 컬럼에 NOT NULL을 명시하거나, 규칙이 제대로 적용되는 STRICT 테이블을 쓰는 걸 권장합니다.

SQLite 기본키(Primary Key) 완벽 정리: INTEGER, 복합키, AUTOINCREMENT