SQLite에서 PRAGMA foreign_keys가 연결마다 꺼지는 문제

2026년 7월 27일 | 개발 깨알 상식_Tips

---

같은 app.db를 쓰는데 API 테스트에서는 고아 레코드가 거절되고, 배치 worker에서는 그대로 들어갔다. 두 경로의 CREATE TABLE 문은 같았고 자식 테이블에도 REFERENCES parent(id)가 있었다. 차이는 스키마가 아니라 새 연결에서 PRAGMA foreign_keys가 0이었다는 점이었다.

SQLite 외래 키 정의가 테이블에 기록돼 있다는 사실과, 그 제약을 현재 쓰기에서 실제로 집행한다는 상태는 별개다. 나는 처음에 테스트 DB를 한 번 열며 옵션을 켰으니 파일 전체에 저장된 줄 알았다. 실제로는 연결마다 명시적으로 켜야 하는 실행 상태라서, 다른 worker나 connection pool이 만든 연결에는 자동으로 이어지지 않았다.

Figure 1. 같은 DB 파일과 같은 외래 키 정의를 사용해도, 연결 A만 foreign_keys를 켜면 연결 B의 고아 레코드 삽입은 막히지 않는다.

제약 정의와 집행 상태는 다른 층이다

부모 테이블과 자식 테이블을 만든 뒤 아무 설정 없이 Python sqlite3.connect()로 연 연결에서 상태를 조회하니 0이 나왔다. 존재하지 않는 부모 ID 999를 자식 테이블에 넣어도 commit이 성공했다. 반대로 연결 직후 PRAGMA foreign_keys = ON을 실행하고 상태가 1인지 확인한 경로에서는 같은 INSERT가 IntegrityError로 끝났다.

con = sqlite3.connect("app.db")
con.execute("PRAGMA foreign_keys = ON")
state = con.execute("PRAGMA foreign_keys").fetchone()[0]
if state != 1:
    raise RuntimeError("foreign key enforcement is off")

여기서 중요한 검증은 PRAGMA 문을 호출했다는 로그가 아니라, 바로 뒤의 조회값이다. 빌드 옵션이나 실행 순서가 예상과 다를 수 있고, 앞으로 SQLite 기본값이 달라져도 애플리케이션이 요구하는 상태를 명시하면 동작 계약이 흔들리지 않는다. 스키마에 외래 키가 보인다는 이유만으로 쓰기 경로가 보호된다고 판단하면 안 된다.

트랜잭션 안에서 켜면 조용히 무시된다

더 헷갈린 지점은 실행 위치였다. 연결을 연 뒤 먼저 BEGIN이 시작된 상태에서 PRAGMA foreign_keys = ON을 호출해도 예외가 나지 않았다. 그런데 다시 조회한 값은 계속 0이었다. SQLite 문서대로 이 설정은 진행 중인 트랜잭션이나 SAVEPOINT 안에서는 바꿀 수 없고, 시도는 오류 대신 no-op으로 끝난다.

ORM 초기화 코드가 첫 쿼리나 migration을 먼저 실행한 뒤 PRAGMA를 붙이면 이런 순서가 생길 수 있다. 호출 흔적만 보면 켠 것처럼 보이므로 발견도 늦다. 나는 연결 생성 직후, 어떤 업무 쿼리나 트랜잭션도 시작하기 전에 설정하고 즉시 1을 확인하도록 위치를 옮겼다. migration에서 일시적으로 끄고 켜는 코드도 transaction 경계 밖인지 따로 본다.

이 no-op은 재시도로 해결되지 않는다. 같은 transaction 안에서 문장을 여러 번 보내도 상태는 그대로다. 먼저 commit 또는 rollback으로 경계를 닫고, 애플리케이션 정책상 그 시점에 상태를 바꿔도 안전한지 확인한 뒤 설정해야 한다.

connection pool의 모든 진입점에 초기화를 건다

단일 프로세스 테스트는 보통 연결 하나를 오래 재사용한다. 그래서 fixture에서 한 번 켠 상태가 모든 테스트에 남고, 실제 서비스처럼 worker가 새 연결을 여는 경로를 놓치기 쉽다. CLI, 웹 요청, background job, migration 도구가 각각 연결을 만들면 한 경로만 빠져도 데이터 무결성이 실행 주체에 따라 달라진다.

해결 위치는 개별 repository 함수가 아니라 연결 생성 hook이다. pool이 물리 연결을 새로 만들 때마다 PRAGMA를 실행하고 조회 결과를 검사한다. pool에서 이미 초기화된 연결을 빌려오는 매 쿼리마다 반복할 필요는 없지만, reconnect와 worker 재시작으로 새 물리 연결이 생기는 순간은 반드시 같은 hook을 지나야 한다.

• 웹 서버, 배치 worker, 테스트 fixture, migration CLI가 같은 연결 factory를 쓰는지 확인한다.
• factory가 만든 새 연결마다 PRAGMA foreign_keys 조회값 1을 assertion으로 남긴다.
• 테스트는 이미 열린 연결이 아니라 새 연결을 만들어 고아 INSERT가 실패하는지 본다.
• 연결 pool을 재생성한 뒤에도 같은 검증을 한 번 더 실행한다.

지금 켜도 기존 고아 행은 사라지지 않는다

foreign_keys를 1로 바꾸는 것은 이후 제약 집행을 켜는 일이지, 이미 들어간 행을 자동으로 청소하는 migration이 아니다. 문제를 발견한 뒤 옵션만 추가하고 끝내면 과거의 고아 레코드는 그대로 남는다. 이 상태에서는 새 쓰기는 막혀도 기존 데이터 조회와 집계에서 계속 이상한 결과가 나온다.

PRAGMA foreign_key_check;

foreign_key_check 결과에는 위반이 발견된 테이블, rowid, 참조 대상, 제약 인덱스가 나온다. 나는 결과가 0건인지 배포 전 확인하고, 위반이 있으면 삭제·부모 복원·값 교정 중 어떤 조치를 할지 별도 migration으로 남긴다. 백업 복원이나 파일 교체 뒤에도 이 검사를 붙이면 “DB가 열린다”보다 한 단계 강한 무결성 확인이 된다.

회귀 테스트는 새 연결에서 시작한다

최종 테스트는 짧다. 임시 DB 파일을 만들고 production과 같은 factory로 새 연결을 연다. 부모가 없는 자식 INSERT가 실패하는지, 정상 부모를 만든 뒤에는 성공하는지, 삭제 정책이 RESTRICT인지 CASCADE인지 기대값과 맞는지 확인한다. 핵심은 이전 테스트가 켜 둔 연결을 재사용하지 않는 것이다.

로그에는 DB 경로만 남기지 않고 connection 생성 지점, foreign_keys 조회값, transaction 시작 전후를 같이 적는다. 같은 파일에서 결과가 갈리면 데이터 파일 차이보다 연결 초기화 경로를 먼저 비교할 수 있다. mock 저장소나 한 연결짜리 단위 테스트가 통과해도 실제 pool의 새 연결이 같은 상태라는 보장은 없다.

SQLite의 외래 키 문제는 제약 문법을 잘못 쓴 사례처럼 보였지만, 실제 수정 대상은 연결 수명주기였다. 연결을 만들 때 켜고, 값이 1인지 확인하고, 기존 데이터는 별도로 검사한다. 이 세 단계를 나누고 나서야 테스트와 worker가 같은 무결성 규칙을 쓰게 됐다. 세부 동작은 SQLite PRAGMA 문서와 외래 키 지원 문서에서 다시 확인했다.