SOFTMOA TECHNOLOGY
SQLite 외래 키 제약조건이 적용되지 않을 때 확인 방법
소프트모아가 정리한 기술 기록입니다.
Contents
SQLite 테이블에 REFERENCES를 넣었는데도 존재하지 않는 부서 번호가 저장된다면, 테이블을 다시 만들기 전에 그 INSERT를 실행한 연결의 PRAGMA foreign_keys 값을 확인해야 합니다. 외래 키 검사는 연결마다 설정하며, 트랜잭션이 시작된 뒤 켠 설정은 오류 없이 무시될 수 있습니다. 연결을 만든 직후, BEGIN 전에 켜고 반환값 1까지 확인하는 것이 수정의 출발점입니다.[1][2]
테이블의 선언과 쓰기 연결의 설정을 구분하기
department는 부서, staff는 직원 테이블입니다. department_id가 실제 부서를 가리켜야 한다고 가정합니다. 외래 키 선언과 연결의 검사 상태는 다릅니다. PRAGMA foreign_key_list(staff)는 선언을, PRAGMA foreign_keys는 해당 연결의 검사 상태를 보여 줍니다.[2]
운영 파일은 경로를 확인한 뒤 읽기 전용으로 먼저 살펴봅니다. 아래 app.db는 실제 파일 경로로 바꿔야 합니다. CLI의 -readonly는 파일을 읽기 전용으로 여는 옵션입니다.[3] 이 명령은 별도의 합성 DB 파일에서 실행해 동작을 확인했습니다.
sqlite3 -noinit -readonly app.db "PRAGMA foreign_keys; PRAGMA foreign_key_list(staff); PRAGMA foreign_key_check;"첫 결과가 0이면 이 조회 연결의 검사는 꺼져 있고, 1이면 켜져 있습니다. 값 자체가 나오지 않으면 오래된 버전이나 외래 키 지원이 빠진 빌드인지 확인해야 합니다.[1] 다만 CLI는 새 연결입니다. 여기서 나온 0으로 애플리케이션 연결의 상태를 단정하거나, CLI에서 1로 바꾼 뒤 앱도 고쳤다고 판단하면 안 됩니다.
- 실제 저장을 수행하는 연결에서 foreign_keys를 조회합니다. 관리자 화면용 연결이나 읽기 전용 연결로 대신하지 않습니다.
- 외래 키 선언이 없다면 ON 설정만으로 제약조건이 생기지는 않습니다. 선언 누락과 검사 비활성화를 다른 문제로 분류합니다.
- 여러 연결을 쓰는 앱이라면 새 쓰기 연결마다 초기화와 확인이 실행되도록 합니다. 기본값은 빌드에 따라 달라질 수 있으므로 가정하지 않습니다.[1][2]
BEGIN 뒤의 ON이 무시되는 상황 재현
다음은 운영 파일을 건드리지 않는 합성 예시입니다. 터미널에서 sqlite3 -noinit :memory:를 실행한 다음 SQL을 붙여 넣습니다. :memory:는 메모리 DB를 뜻하며 종료하면 사라집니다.[3] 확인 환경은 로컬 CLI SQLite 3.54.0입니다. 시작 상태를 OFF로 명시해 기본값에 의존하지 않았습니다.
.headers off
.mode list
PRAGMA foreign_keys = OFF;
CREATE TABLE department(id INTEGER PRIMARY KEY);
CREATE TABLE staff(
id INTEGER PRIMARY KEY,
department_id INTEGER NOT NULL REFERENCES department(id)
);
BEGIN;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
INSERT INTO staff VALUES(51, 404);
COMMIT;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
PRAGMA foreign_key_check;
PRAGMA integrity_check;
INSERT INTO staff VALUES(52, 404);
SELECT count(*) FROM staff;
INSERT INTO staff VALUES(53, NULL);
SELECT count(*) FROM staff;첫 foreign_keys 조회 결과는 0이었습니다. BEGIN 이후의 ON이 적용되지 않아, 부서 404가 없는데도 직원 51은 저장됐습니다. COMMIT 다음 ON은 적용됐고 두 번째 조회는 1을 반환했습니다. SQLite는 진행 중인 BEGIN이나 SAVEPOINT가 있을 때 이 설정을 변경하지 않습니다.[2] 설정문 실행 중 오류가 없었다는 것만으로 성공을 판단하지 말아야 하는 이유입니다.
검사를 켜도 직원 51은 그대로 남는다
이어지는 foreign_key_check의 실제 출력은 staff|51|department|0이었습니다. 순서대로 위반한 자식 테이블, 해당 행의 rowid, 참조 대상 부모 테이블, 실패한 외래 키 번호입니다. 이 예시에서는 INTEGER PRIMARY KEY인 id가 rowid에 대응하므로 51을 직원 번호로 찾을 수 있습니다. 모든 테이블에서 두 번째 열을 업무 키로 읽을 수 있는 것은 아닙니다. WITHOUT ROWID 테이블이라면 이 열은 NULL입니다.[2]
바로 뒤의 integrity_check는 ok를 반환했습니다. 이 검사는 외래 키 오류를 찾는 검사가 아니므로, ok만 보고 참조 관계까지 정상이라고 결론 내리면 안 됩니다.[2] 기존 외래 키 위반은 foreign_key_check로 따로 점검합니다. 이 조회는 위반을 보여 줄 뿐 데이터를 수정하지 않습니다.
검사를 켠 뒤 직원 52에 같은 부서 404를 넣으면 FOREIGN KEY constraint failed가 발생합니다. 다음 count(*)는 1입니다. 새 입력은 거절됐지만 앞서 저장된 직원 51을 자동으로 고치거나 삭제하지는 않았다는 뜻입니다. 운영 기록을 발견했다면 원래 입력과 부서 이력을 대조해 올바른 부서로 정정할지, 승인된 절차로 기록을 처리할지 결정해야 합니다. 위반 행을 일괄 삭제하는 SQL을 장애 대응 첫 단계로 실행하지 않습니다.
마지막 NULL 입력은 NOT NULL constraint failed: staff.department_id로 거절됐고 행 수는 여전히 1이었습니다. 외래 키만 선언한 열은 NULL을 허용할 수 있습니다.[1] 별도의 nullable 합성 테이블에서는 NULL 입력이 저장됐고 외래 키 점검 결과도 비어 있었습니다. 부서 입력이 필수라는 업무 규칙은 REFERENCES뿐 아니라 NOT NULL로 표현해야 합니다.
수정 위치는 연결 초기화 코드
앱에서는 연결 생성 → PRAGMA foreign_keys = ON → 같은 연결에서 1 확인 → 업무 트랜잭션 시작 순서로 초기화합니다. ORM이나 드라이버가 먼저 트랜잭션을 시작하는 구조라면 해당 제품의 연결 생성 훅과 초기화 순서를 확인해야 합니다. 제품별 기본값을 추측해 설정 코드를 중복 추가하지 않습니다.
이번 예시는 설정 시점과 기존·신규 위반 처리만 확인했습니다. 점검 결과가 비어 있어도 선언하지 않은 관계나 승인 권한까지 검증되지는 않습니다. 운영 수정 전에는 백업·검증용 복사본을 마련하고 모든 저장 경로의 초기화 규칙을 확인합니다.
설정·명령 확인 자료:
소프트모아의 개발 서비스는 softmoa.com에서 확인할 수 있습니다.
소프트모아는 해당 시스템을 구축합니다. 문의하기
