SQLite에 FOREIGN KEY를 걸었는데, 없는 고객의 주문이 왜 들어갈까

SQLite 테이블에 REFERENCES를 적어 두었는데, 없는 고객을 가리키는 주문이 오류 없이 저장될 때가 있습니다. 제약을 잘못 쓴 것이 아닙니다. SQLite의 외래 키 검사는 기본으로 꺼져 있고 연결마다 따로 켜야 합니다. 이 글을 읽고 나면 지금 연결에서 검사가 켜져 있는지 확인하고, 오류 없이 켜지지 않는 경우를 피하고, 이미 들어간 잘못된 행을 찾을 수 있습니다.

예제는 2026-11-01에 macOS 27.0.1에서 실행했습니다. sqlite3 셸은 macOS에 들어 있는 3.54.0, Python은 시스템의 3.9.6(SQLite 3.54.0)과 uv로 설치한 3.12.14(SQLite 3.53.1), Node.js는 v24.21.0(SQLite 3.53.4)입니다. 고객과 주문은 가상의 예입니다. 다른 운영체제와 ORM은 실행하지 않았습니다.

제약은 저장되지만 검사는 꺼져 있다

주문이 고객을 가리키는 흔한 두 테이블입니다.

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL
              REFERENCES customers(id) ON DELETE CASCADE,
  item        TEXT NOT NULL
);
INSERT INTO customers VALUES (1, 'Mina');
INSERT INTO orders VALUES (100, 1, 'keyboard');

아래 예제에서 sqlite3 shop.db "..." 한 줄은 새 연결 하나입니다. 새 연결에서 PRAGMA foreign_keys는 0이었고, 없는 고객 999를 가리키는 INSERT는 오류 없이 종료 코드 0으로 끝났습니다. SELECT에는 101|999|ghost가 나왔습니다.

SQLite 문서는 외래 키 제약이 하위 호환 때문에 기본으로 꺼져 있고 연결마다 켜야 한다고 적습니다. 3.6.19부터 기본값이 OFF이며, 앞으로 바뀔 수도 있으니 기본값에 기대지 말고 앱에서 직접 정하라고도 합니다.

꺼져 있어도 스키마의 제약은 남아 있습니다. PRAGMA foreign_key_check는 위반한 행을 알려 주고, 검사가 꺼진 연결에서도 orders|101|customers|0을 돌려주었습니다. 네 칸은 자식 테이블, 그 행의 rowid, 부모 테이블, 제약 번호입니다.

켜도 그 연결뿐이고, 이미 들어간 행은 그대로다

PRAGMA foreign_keys = ON을 먼저 실행한 연결에서는 같은 종류의 INSERT가 FOREIGN KEY constraint failed로 거부되고 종료 코드 1이 나왔습니다. 그런데 다음 명령은 새 연결이므로 값은 다시 0이었습니다. 설정이 데이터베이스 파일이 아니라 연결에 붙기 때문입니다. 연결 풀을 쓰는 앱이라면 새 연결이 만들어질 때마다 실행되는 자리에 이 문장을 두어야 합니다.

켠 연결에서도 앞서 들어간 101번 주문은 그대로 조회되었고 foreign_key_check에도 그대로 나왔습니다. 켜는 순간 지난 행을 다시 검사하지는 않았다는 뜻입니다. 운영 중인 파일에 검사를 켤 때는 이 명령으로 위반 행부터 찾아야 합니다. 지울지, 부모를 되살릴지는 데이터마다 다르므로 이 글에서는 정하지 않습니다.

오류 없이 켜지지 않는 두 경우와, 돌지 않는 CASCADE

첫째는 트랜잭션 안입니다. BEGIN 뒤에 PRAGMA foreign_keys = ON을 실행하고 값을 읽으면 0이었습니다. 문서도 트랜잭션 안에서 이 PRAGMA는 아무 일도 하지 않으며 오류도 내지 않는다고 적습니다.

둘째는 오타입니다. s가 빠진 PRAGMA foreign_key = ON도 오류 없이 끝났고 값은 0이었습니다. SQLite는 모르는 PRAGMA를 그냥 무시한다고 문서에 나옵니다. 두 경우 모두 켜는 문장만으로는 알 수 없고, 값을 다시 읽어야 알 수 있습니다.

꺼진 연결에서는 ON DELETE CASCADE도 돌지 않았습니다. 고객 1을 지운 뒤에도 주문 100이 남았고, foreign_key_check의 위반은 두 줄로 늘었습니다. 같은 일을 켠 연결에서 하자 고객 2를 지울 때 주문 103도 함께 지워져 개수가 0이었습니다.

같은 파일도 누가 여느냐에 따라 다르다

기본값은 SQLite를 감싸는 쪽에 따라 달랐습니다. Python의 sqlite3는 3.9.6과 3.12.14 모두 0이었습니다. Node.js의 node:sqlite는 1이었고 같은 INSERT를 거부했습니다. v24 문서에도 enableForeignKeyConstraints의 기본값이 true로 적혀 있고, 이 값을 false로 주자 0이 되어 INSERT가 들어갔습니다. 문서는 node:sqlite의 안정성을 1.2(release candidate)로 표시합니다.

그래서 같은 shop.db에 같은 종류의 문장을 넣어도 Python 기본 설정은 받아 주고 Node.js 기본 설정은 거부했습니다. 앱은 node:sqlite를 쓰면서 점검 스크립트는 Python이나 sqlite3 셸로 돌리면 바로 이 차이를 만납니다. SQLite에는 기본값을 정하는 컴파일 옵션 SQLITE_DEFAULT_FOREIGN_KEYS도 있고, npm 패키지 better-sqlite3의 문서는 이 값을 1로 빌드한다고 적습니다(실행하지 않음). 버전 번호만 보고는 알 수 없으니 읽어 보는 편이 빠릅니다.

Python에서는 autocommit 설정이 PRAGMA를 삼킨다

Python은 한 가지를 더 봐야 합니다. 기본(옛) 트랜잭션 제어에서는 INSERT가 트랜잭션을 암묵적으로 엽니다. 그래서 INSERT 뒤 commit() 전에 실행한 PRAGMA는 0으로 남았고, commit() 뒤에는 1이 되었습니다.

3.12에 생긴 autocommit=False는 더 분명합니다. Python 문서는 이 값이 트랜잭션을 항상 열어 둔다고 설명하며 권장값으로 표시합니다. 그래서 연결 직후에 실행해도 소용없었습니다. commit()도 소용없었습니다. 곧바로 다음 트랜잭션을 열기 때문입니다.

conn = sqlite3.connect(":memory:", autocommit=False)
conn.execute("PRAGMA foreign_keys = ON")
print(conn.execute("PRAGMA foreign_keys").fetchone()[0])  # 0

문서는 autocommit의 기본값이 앞으로 False로 바뀔 것이라고도 적습니다(3.12.16판과 3.15.2판 모두 아직은 옛 방식이 기본). 3.12.14에서 통한 방법은 두 가지였습니다. 둘 다 autocommit과 setconfig가 생긴 Python 3.12 이상에서만 쓸 수 있습니다. autocommit=True로 연결해 PRAGMA를 실행한 뒤 conn.autocommit = False로 바꾸거나, conn.setconfig(sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY, True)를 쓰면 값이 1이 되었습니다. 앞의 방법을 함수 하나로 묶으면 이렇습니다.

def connect(path):
    conn = sqlite3.connect(path, autocommit=True)
    conn.execute("PRAGMA foreign_keys = ON")
    if conn.execute("PRAGMA foreign_keys").fetchone()[0] != 1:
        raise RuntimeError("foreign keys are not enforced")
    conn.autocommit = False
    return conn

이 함수로 연 연결은 없는 고객의 주문을 IntegrityError로 거부했습니다.

개발 노트

외래 키 검사: 기본값은 여는 쪽이 정한다

PRAGMA foreign_keys를 읽어 1인지 보는 것이 가장 확실하다

• 새 연결의 기본값 (실행 결과)

  • sqlite3 셸 3.54.0: 0 (꺼짐)
  • Python sqlite3 3.9.6, 3.12.14: 0 (꺼짐)
  • Node.js node:sqlite v24.21.0: 1 (켜짐)

• 오류 없이 켜지지 않는 경우 (실행 결과)

  • BEGIN 뒤의 PRAGMA
  • s가 빠진 PRAGMA foreign_key
  • Python: INSERT 뒤 commit() 전
  • Python: autocommit=False 연결

• 연결을 여는 곳에서 할 일

  PRAGMA foreign_keys = ON;

  • 연결마다 켠다(파일이 아니라 연결의 설정)
  • 다시 읽어 1인지 확인한다
  • 쓰던 파일은 먼저 foreign_key_check로 위반 행을 찾는다(켜도 지난 행은 다시 검사하지 않음)

2026-11-01 macOS 27.0.1에서 실행한 결과다. 다른 운영체제, ORM, better-sqlite3는 실행하지 않았다.

앱에서 SQLite 연결을 여는 곳을 한 군데로 모으고, 거기서 PRAGMA foreign_keys를 켠 뒤 1이 읽히는지 확인하십시오. 이미 쓰던 파일이라면 그 전에 PRAGMA foreign_key_check를 한 번 돌려 위반 행이 몇 개인지부터 보십시오.