SQLite REPLACE 뒤 자식 행이 사라질 때: UPDATE와 삭제·삽입의 차이

SQLite에서 부모 행을 INSERT OR REPLACE로 바꿨더니 연결된 자식 행이 사라졌다면, 일반 UPDATE와 같은 작업으로 생각했는지 확인해야 합니다. 이번 예제처럼 기본 키 충돌이 발생하면 REPLACE는 충돌하는 기존 행을 삭제한 뒤 삽입하며, 활성화된 ON DELETE CASCADE가 그 삭제에 따라 자식 행을 제거할 수 있습니다.

부모의 같은 ID가 다시 생겨도 삭제된 자식이 자동 복원되지는 않습니다. 같은 초기 데이터에서 REPLACE, 이름 열만 바꾸는 UPDATE, ON CONFLICT DO UPDATE를 각각 별도 메모리 데이터베이스로 실행해 차이를 확인하겠습니다.

비교 조건을 먼저 고정합니다

부모는 ID 1과 이름 old를 갖고, 자식 ID 10은 부모 ID 1을 참조합니다. 외래 키를 켠 상태에서 자식 테이블에는 ON DELETE CASCADE를 둡니다. 세 작업 모두 마지막 부모 값은 ID 1·이름 new를 목표로 합니다.

각 비교는 새 :memory: 연결에서 시작합니다. 실제 업무 데이터베이스 파일을 열거나 수정하지 않습니다. 실행 환경은 SQLite 3.53.4이며, UPSERT 구문이 추가된 SQLite 3.24.0 이상을 확인합니다. Python의 sqlite3 모듈이 사용하는 SQLite 버전은 설치 환경마다 다를 수 있습니다.

부모와 자식의 결과를 함께 출력합니다

import sqlite3

SETUP = """
CREATE TABLE parent(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE child(
    id INTEGER PRIMARY KEY,
    parent_id INTEGER NOT NULL REFERENCES parent(id) ON DELETE CASCADE
);
INSERT INTO parent VALUES(1, 'old');
INSERT INTO child VALUES(10, 1);
"""

def compare(label, statement):
    con = sqlite3.connect(":memory:")
    try:
        con.execute("PRAGMA foreign_keys = ON")
        assert con.execute("PRAGMA foreign_keys").fetchone() == (1,)
        con.executescript(SETUP)
        con.execute(statement)
        con.commit()
        parents = con.execute("SELECT id,name FROM parent ORDER BY id").fetchall()
        children = con.execute("SELECT id,parent_id FROM child ORDER BY id").fetchall()
        print(label, "parent=", parents, "child=", children)
        return parents, children
    finally:
        con.close()

print("SQLite:", sqlite3.sqlite_version)
assert sqlite3.sqlite_version_info >= (3, 24, 0)
p1, c1 = compare("REPLACE",
    "INSERT OR REPLACE INTO parent(id,name) VALUES(1,'new')")
p2, c2 = compare("UPDATE",
    "UPDATE parent SET name='new' WHERE id=1")
p3, c3 = compare("UPSERT",
    "INSERT INTO parent(id,name) VALUES(1,'new') "
    "ON CONFLICT(id) DO UPDATE SET name=excluded.name")
assert p1 == p2 == p3 == [(1, "new")]
assert c1 == [] and c2 == c3 == [(10, 1)]

부모가 같아 보여도 자식 결과는 다릅니다

SQLite: 3.53.4
REPLACE parent= [(1, 'new')] child= []
UPDATE parent= [(1, 'new')] child= [(10, 1)]
UPSERT parent= [(1, 'new')] child= [(10, 1)]

작업	부모 결과	자식 결과
PK 충돌 REPLACE	(1, new)	빈 목록
name만 UPDATE	(1, new)	(10, 1) 유지
충돌 시 name만 UPSERT	(1, new)	(10, 1) 유지

REPLACE의 삭제 시점에 부모를 참조하는 자식이 CASCADE로 삭제됩니다. 이후 새 부모가 같은 ID로 삽입되어도 이전 자식 행은 이미 없습니다. 그래서 부모 테이블만 조회하면 문제를 놓칠 수 있습니다.

갱신할 열과 충돌 기준을 명시합니다

이번 UPDATE와 UPSERT는 부모 키를 유지하면서 name만 변경합니다. UPSERT의 ON CONFLICT(id)는 충돌을 판단할 기본 키를 정하고, excluded.name은 삽입하려던 입력의 이름을 가리킵니다. 이름을 바꾸는 목적이라면 삭제·재삽입을 포함한 정책이 정말 필요한지 먼저 비교하는 편이 좋습니다.

UPSERT를 사용했다는 사실만으로 모든 자식 관계가 안전해지는 것은 아닙니다. 부모 키를 직접 바꾸거나 트리거가 다른 테이블을 수정하는 경우에는 결과가 달라질 수 있습니다. 기존 행이 없을 때 새로 넣을지, 없으면 갱신하지 않고 끝낼지도 요구사항에 맞춰 선택합니다.

어디까지 같은 결과를 기대할 수 있을까요?

• 이번 삭제 결과는 PK 충돌·외래 키 활성·ON DELETE CASCADE 조합에서 확인했습니다. 충돌 없는 INSERT까지 자식을 삭제한다고 일반화하지 않습니다.
• 외래 키가 꺼져 있거나 다른 삭제 정책이면 다른 결과가 날 수 있습니다. 예제는 PRAGMA 조회값이 1인지 먼저 확인합니다.
• REPLACE의 모든 제약 위반이 같은 삭제·삽입 경로를 타는 것은 아닙니다. NOT NULL·CHECK·외래 키 오류까지 이 결과로 설명하지 않습니다.
• RESTRICT처럼 즉시 부모 삭제를 막는 제약이나 ON UPDATE 동작·트리거는 별도 검사 항목입니다.
• 원본 작업에 적용하기 전에는 복원 가능한 사본에서 부모·자식·트리거와 실패 시 롤백 결과를 함께 확인합니다.

참조 관계 자체가 헷갈리면 정규화와 참조 무결성 정리를 먼저 볼 수 있습니다. 다른 DBMS의 충돌 처리와 비교하려면 PostgreSQL ON CONFLICT와 RETURNING을 이어 읽되 SQLite의 삭제·외래 키 규칙과 같은 것으로 가정하지 않습니다.

핵심 정리

최종 부모 값만 같다고 같은 갱신은 아닙니다. 충돌 시 기존 행이 삭제되는지, 자식 정책이 그 삭제에 반응하는지, 실제로 바꿀 열은 무엇인지 나누어 확인해야 데이터 손실 원인을 재현할 수 있습니다.