실행 가능한 SQLite 문서: 외래키

Q: SQLite에서 외래키는 어떻게 만드나요?

CREATE TABLE의 컬럼 정의 뒤에 REFERENCES 부모테이블(컬럼)을 붙여주면 됩니다. 예를 들어 authorid INTEGER REFERENCES authors(id)라고 쓰면 authorid가 authors 테이블의 행을 가리키게 되죠. 단, 참조되는 컬럼은 반드시 PRIMARY KEY이거나 UNIQUE 제약이 걸려 있어야 합니다.

외래키는 테이블끼리 이어주는 포인터다

sqlite 외래키(foreign key)는 한 테이블의 컬럼 값이 다른 테이블의 어떤 행과 반드시 일치해야 한다는 제약이다. 관계형 데이터베이스에서 "이 posts 행은 저 authors 행에 속한다"고 표현할 때, 작성자 이름과 이메일을 모든 게시글마다 복사해 넣지 않고도 그 관계를 표현하는 방법이다.

가장 단순한 예시를 보자. 부모 테이블과 자식 테이블이 외래키 하나로 연결된 형태다:

PRAGMA foreign_keys = ON;

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

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('에이다'), ('보리스');
INSERT INTO posts (title, author_id) VALUES ('안녕하세요', 1), ('노트', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id), 이 한 줄이 외래키(foreign key) 선언의 전부입니다. 의미는 이렇습니다. "이 컬럼에는 authors 테이블의 id 값이 들어간다." 이제 데이터베이스는 두 테이블이 서로 연결돼 있다는 사실을 알게 되고, 제약 조건이 켜져 있다면 존재하지 않는 author를 가리키는 INSERT를 거부합니다.

SQLite 외래키는 기본적으로 꺼져 있다

SQLite 외래키에 대해 가장 먼저 알아야 할 사실이자 거의 모든 사람이 처음 만나면 당황하는 부분입니다. SQLite는 REFERENCES 구문을 파싱은 하지만, 따로 켜주지 않으면 강제하지 않습니다. 이렇게 된 건 호환성 때문입니다. 외래키 기능이 도입되기 전부터 만들어진 데이터베이스들이 이미 존재했거든요.

제약을 켜지 않은 상태에서 어떤 일이 벌어지는지 보시죠.

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- author 999는 존재하지 않지만, SQLite는 이를 그대로 허용합니다:
INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

SELECT * FROM posts;

고아 레코드가 그대로 들어가 버렸습니다. 원하는 수준의 무결성 보호를 받으려면, 모든 커넥션을 열 때마다 가장 먼저 PRAGMA foreign_keys = ON;을 실행해야 합니다:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('고아', 999);

이제 INSERT가 FOREIGN KEY constraint failed 오류와 함께 실패합니다. 이 PRAGMA는 데이터베이스 단위가 아니라 커넥션 단위라서 파일에 저장되지 않는다는 점이 핵심입니다. 즉, 애플리케이션을 실행할 때마다, CLI 세션을 열 때마다, 테스트 픽스처를 띄울 때마다 매번 다시 설정해 줘야 합니다. 그래서 실제 운영 코드에서는 커넥션을 연 직후에 곧바로 PRAGMA foreign_keys = ON;을 실행하는 게 거의 공식처럼 굳어져 있습니다.

REFERENCES 절이 요구하는 조건

외래키로 참조하는 컬럼은 반드시 PRIMARY KEY이거나 UNIQUE 제약을 가지고 있어야 합니다. 이 조건이 있어야 SQLite가 "참조된 값이 정확히 하나의 행을 가리킨다"는 걸 보장할 수 있기 때문이죠. 또한 양쪽 컬럼의 타입도 호환되어야 합니다. SQLite가 타입에 관대한 편이긴 하지만, 그렇다고 타입을 섞어 쓰는 건 나중에 골치 아픈 문제를 자초하는 길입니다.

외래키를 정의하는 방법은 두 가지입니다. 먼저, 컬럼 정의에 인라인으로 붙이는 방식입니다:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

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

SELECT sql FROM sqlite_master WHERE name = 'posts';

또는 다음과 같이 테이블 레벨 제약 조건으로 따로 분리해서 정의할 수도 있습니다. 외래키가 여러 컬럼에 걸쳐 있을 때는 이 방식을 반드시 사용해야 합니다.

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

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

두 방식 모두 똑같은 제약 조건을 만들어 냅니다. 그러니 해당 테이블에서 더 읽기 좋은 쪽을 골라 쓰면 됩니다.

ON DELETE: 자식 행은 어떻게 처리할까

부모 행을 삭제하면, 그 부모를 참조하던 자식 행들을 어떻게 처리할지 SQLite가 결정해야 합니다. 이 동작 방식은 ON DELETE 옵션으로 지정합니다:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Ada를 삭제하니 그녀가 작성한 게시글 두 개도 함께 사라졌습니다. 선택할 수 있는 동작은 다음과 같습니다.

CASCADE — 자식 행도 같이 삭제합니다. 작성자에 딸린 게시글이나 주문에 딸린 항목처럼 "소유 관계"인 데이터에 적합합니다.
SET NULL — FK 컬럼을 NULL로 만듭니다. 부모가 사라져도 자식은 남아 있어야 할 때 유용합니다(예: 사용자가 탈퇴해도 댓글은 익명으로 남기는 경우).
SET DEFAULT — FK 컬럼을 선언된 기본값으로 설정합니다.
RESTRICT — 자식이 하나라도 있으면 삭제를 막습니다. 문장 실행 시점에 즉시 실패합니다.
NO ACTION — 기본 동작입니다. 대부분의 경우 RESTRICT와 사실상 동일하게 작동합니다(검사 시점이 커밋 시점으로 미뤄질 뿐, 결과적으로 고아 자식을 남길 수 없다는 점은 같습니다).

ON UPDATE도 부모 키 값이 바뀔 때 같은 방식으로 동작합니다. 다만 기본 키를 수정하는 일은 흔치 않습니다.

foreign key constraint failed 오류의 의미

이 오류는 크게 두 가지 상황에서 마주치게 됩니다. 첫 번째는 매칭되는 부모 행이 없는 값으로 자식 행을 INSERT 하거나 UPDATE 할 때입니다.

sqlite> INSERT INTO posts (title, author_id) VALUES ('길 잃은 글', 999);
Runtime error: FOREIGN KEY constraint failed

작성자 999가 존재하지 않거나, 컬럼 타입이 서로 어긋나 있는 상황입니다. 부모 행을 먼저 넣거나, 값을 바로잡으세요.

두 번째는 자식이 아직 남아 있는 부모 행을 삭제(또는 업데이트)하려는 경우인데, FK가 RESTRICT나 NO ACTION으로 설정되어 있을 때 발생합니다:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

자식 행을 먼저 지우거나, 정말로 연쇄 처리를 원한다면 외래키를 ON DELETE CASCADE 또는 SET NULL로 바꿔주면 됩니다.

조금 덜 알려진 친척 격으로 FOREIGN KEY mismatch라는 오류도 있습니다. 이건 참조 대상 컬럼이 기본키나 UNIQUE가 아니거나, 컬럼 개수가 맞지 않을 때 발생합니다. 데이터 문제가 아니라 스키마 문제죠.

기존 테이블에 외래키 추가하기

SQLite의 ALTER TABLE은 기능이 제한적입니다. 새로운 컬럼을 추가하면서 외래키를 거는 건 가능하지만, 이미 존재하는 컬럼에 외래키를 나중에 붙이는 건 불가능합니다. 그래서 보통은 테이블 이름을 바꾸고 다시 만드는 우회 방법을 씁니다:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('안녕하세요', 1), ('세계', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

패턴은 이렇습니다. 외래키 검사를 잠시 꺼두고, 원하는 제약 조건으로 새 테이블을 만든 뒤 데이터를 복사합니다. 그다음 기존 테이블을 삭제하고 이름을 바꾸면 됩니다. BEGIN/COMMIT으로 묶어두면 전체 작업이 원자적으로 처리됩니다. 마지막에 외래키 검사를 다시 켜면 SQLite가 새 제약 조건에 맞춰 기존 행을 모두 검증해 줍니다. 단, 잘못된 데이터가 있더라도 트랜잭션은 이미 커밋된 상태이니, 걱정된다면 먼저 확인해 두는 편이 좋습니다.

마이그레이션이 끝난 뒤에는 PRAGMA foreign_key_check;를 실행해 고아 행(orphan row)이 남아 있지 않은지 확인하세요.

실전 스키마 예제

지금까지 다룬 내용을 조합해 보겠습니다. 부모 테이블과 자식 테이블, 그리고 다대다 태그를 위한 조인 테이블까지 포함한 간단한 블로그 스키마입니다.

PRAGMA foreign_keys = ON;

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

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

여기서 짚고 갈 포인트가 세 가지 있습니다. 먼저 author_id는 NOT NULL이라서 모든 게시글에는 반드시 작성자가 있어야 합니다. posts → authors 외래키는 CASCADE이므로, 작성자를 삭제하면 그 사람이 쓴 게시글도 함께 삭제됩니다. post_tags 조인 테이블은 양쪽 모두 CASCADE라서, 게시글이나 태그 중 어느 한쪽을 지우면 연결 행도 알아서 정리됩니다.

나중에 고생하지 않으려면 들여야 할 습관

모든 커넥션에서 PRAGMA foreign_keys = ON;을 켜두세요. 매번 떠올리는 게 아니라, 데이터베이스를 여는 루틴에 아예 포함시켜야 합니다.
외래키 컬럼에는 인덱스를 추가하세요. SQLite는 부모 쪽 키는 자동으로 인덱싱하지만 자식 쪽은 그렇지 않습니다. ON DELETE CASCADE는 부모를 삭제할 때마다 자식 쪽을 조회하기 때문에 인덱스가 없으면 느려집니다.
ON DELETE 동작은 의도를 가지고 선택하세요. 기본값(NO ACTION)은 안전하긴 하지만, 데이터를 정리하려고 할 때마다 "constraint failed" 에러를 만나게 됩니다. 어떻게 동작해야 할지 정한 다음 명시적으로 선언해 두세요.
마이그레이션이나 대량 임포트 후에는 PRAGMA foreign_key_check;를 실행해서, 버그로 번지기 전에 고아 행을 잡아내세요.

다음: INNER JOIN

외래키가 관계를 정의해 준다면, 실제로 그 관계를 가로질러 조회하는 도구는 조인입니다. 다음 페이지에서는 INNER JOIN을 다룹니다. 관련된 테이블의 행을 결합해서, 각 테이블에서 원하는 컬럼만 골라 가져오는 방법을 살펴봅니다.

자주 묻는 질문

SQLite에서 외래키는 어떻게 만드나요?

CREATE TABLE의 컬럼 정의 뒤에 REFERENCES 부모테이블(컬럼)을 붙여주면 됩니다. 예를 들어 author_id INTEGER REFERENCES authors(id)라고 쓰면 author_id가 authors 테이블의 행을 가리키게 되죠. 단, 참조되는 컬럼은 반드시 PRIMARY KEY이거나 UNIQUE 제약이 걸려 있어야 합니다.

외래키를 선언했는데 왜 제약이 안 걸리나요?

SQLite는 외래키 선언을 파싱은 하지만, 명시적으로 켜지 않으면 실제로 검사하지 않습니다. 연결할 때마다 PRAGMA foreign_keys = ON;을 실행해 주세요. 이 설정은 데이터베이스 파일이 아니라 연결(connection) 단위로 유지되기 때문에, 라이브러리든 CLI든 새로 접속할 때마다 매번 켜줘야 합니다.

ON DELETE CASCADE는 정확히 어떤 동작인가요?

부모 행이 삭제되면 그에 딸린 자식 행들도 자동으로 같이 지워주는 옵션입니다. 그 외 선택지로는 삭제 자체를 막는 RESTRICT, FK 컬럼을 NULL로 바꿔주는 SET NULL, 기본값으로 되돌리는 SET DEFAULT, 그리고 디폴트인 NO ACTION(실질적으로 RESTRICT와 동일)이 있어요. 부모 없이 자식 데이터가 의미가 있는지를 기준으로 고르면 됩니다.

'foreign key constraint failed' 에러는 어떻게 해결하나요?

이 에러는 두 가지 상황에서 납니다. 첫째, 참조 테이블에 존재하지 않는 값으로 INSERT/UPDATE를 시도한 경우. 둘째, 자식 행이 남아 있는 부모 행을 DELETE 하려고 한 경우입니다. 부모 행이 실제로 있는지 먼저 확인하거나, 자식까지 같이 지워도 되는 구조라면 ON DELETE CASCADE를 걸어두는 게 깔끔합니다.

SQLite 외래키(Foreign Key) 완벽 정리: REFERENCES와 PRAGMA