SQLite 외래 키 지원

SQLite 외래 키 지원

외래 키(FOREIGN KEY)는 테이블 사이의 "존재" 관계를 강제하기 위한 SQL 제약이에요. 예를 들어 트랙(track)이 반드시 존재하는 아티스트(artist)를 가리켜야 한다는 규칙을 데이터베이스 자체에서 지키게 하려면 외래 키를 쓰죠. SQLite에서 외래 키 지원은 버전 3.6.19(2009-10-14)부터 도입됐는데, 핵심은 기본으로 꺼져 있다는 점이에요. 이 글에서는 외래 키의 개념과 활성화 방법, 그리고 동작 방식을 살펴볼게요.

출처: https://www.sqlite.org/foreignkeys.html

외래 키 개념

두 테이블을 예로 들어볼게요. 아티스트 테이블과 트랙 테이블이 있고, 트랙이 어떤 아티스트의 작품인지 기록하고 싶어요.

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER  -- 반드시 artist.artistid 를 가리켜야 함
);

이 상태로는 trackartist가 실제로 존재하는 아티스트를 가리키는지 보장할 수 없어요. 외래 키 제약을 붙이면 이 관계가 강제돼요.

CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

용어를 정리하면 이래요.

  • 부모 테이블(parent table) — 외래 키 제약이 가리키는 테이블. 위 예시의 artist예요(참조되는 테이블).
  • 자식 테이블(child table) — 외래 키 제약이 적용되는 테이블로, REFERENCES 절을 담고 있어요. 위 예시의 track이죠(참조하는 테이블).
  • 부모 키(parent key) — 부모 테이블에서 외래 키가 가리키는 컬럼(또는 컬럼 집합). 보통 부모의 기본 키지만 항상 그런 건 아니에요. 부모 키는 명명된 컬럼이어야 하고, rowid는 될 수 없어요.

외래 키 활성화하기

SQLite에서 외래 키는 기본적으로 비활성화돼 있어요. 외래 키 정의는 파싱되고 PRAGMA foreign_key_list로 조회할 수 있지만, 제약이 강제되지는 않아요. 활성화하려면 PRAGMA foreign_keys를 켜야 해요(연결마다 별도 설정).

PRAGMA foreign_keys = ON;

필요한 인덱스

외래 키가 선언되어 있어도, 참조 무결성을 효율적으로 지키려면 관련 컬럼에 인덱스가 있으면 좋아요. 자식 테이블의 외래 키 컬럼과 부모 키 컬럼이 인덱스로 뒷받침되면 삽입·삭제·갱신 검사가 빨라져요.

CREATE INDEX trackindex ON track(trackartist);

고급 특징

복합 외래 키

여러 컬럼으로 이뤄진 부모 키를 가리킬 수도 있어요.

CREATE TABLE album(
  albumartist TEXT,
  albumname TEXT,
  albumcover BINARY,
  PRIMARY KEY(albumartist, albumname)
);

CREATE TABLE song(
  songid     INTEGER,
  songartist TEXT,
  songalbum TEXT,
  songname   TEXT,
  FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);

지연(deferred) 외래 키

DEFERRABLE INITIALLY DEFERRED 같은 키워드로 검사 시점을 트랜잭션 커밋 시점까지 미룰 수 있어요. 즉각(immediate) 외래 키 제약과 달리, 지연 제약은 트랜잭션 전체를 마친 뒤 한꺼번에 검사하죠.

CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED
);

ON DELETE / ON UPDATE 동작

각 외래 키는 참조되는 행이 삭제·갱신될 때 어떻게 할지를 ON DELETE / ON UPDATE 절로 정해요. 값은 NO ACTION, RESTRICT, SET NULL, SET DEFAULT, CASCADE 중 하나이고, 명시하지 않으면 NO ACTION이 기본이에요.

-- 부모 행이 갱신되면 자식도 함께 갱신
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
);

-- 부모 행이 삭제되면 자식 값은 기본값 0 으로
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
);

더 알아보기