[SQLite] 외래 키

SQLite에서 외래 키 제약 조건(Foreign Key Constraints)은 테이블 간의 "존재" 관계를 강제하는 데 사용됩니다. 예를 들어, 한 테이블의 열 값이 다른 테이블의 특정 열 값에 해당해야 한다는 관계를 정의하고 강제할 수 있습니다.

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);

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

위의 예시에서 track 테이블의 trackartist 열은 artist 테이블의 artistid 열을 참조하는 외래 키입니다. 이는 track 테이블에 삽입된 모든 행이 유효한 artist 테이블의 행을 참조하도록 강제합니다. trackartist 열에 NULL 값이 허용되는 경우, 해당 track 행은 아티스트 정보 없이도 존재할 수 있습니다.

외래 키 제약 조건을 사용함으로써 데이터베이스의 무결성을 유지하고, 관련된 테이블 간의 정확한 "존재" 관계를 보장할 수 있습니다.

 

외래 키 제약 조건을 위한 인덱스 생성

외래 키 제약 조건을 효율적으로 사용하기 위해서는 사용자가 인덱스를 생성해야 할 수 있습니다. 이는 외래 키 제약 조건의 성능을 최적화하고 데이터베이스의 검색 속도를 향상시키는 데 도움이 됩니다.

 

고급 외래 키 관련 기능

 

복합 외래 키 제약 조건

복합 외래 키 제약 조건(Composite Foreign Key Constraints)은 두 개 이상의 열로 구성된 외래 키가 다른 테이블의 기본 키 또는 유니크 키와 관계를 맺고 있음을 정의할 때 사용됩니다. 이러한 제약 조건은 관계형 데이터베이스에서 복잡한 관계를 모델링하는 데 유용하며, 관련된 테이블 간의 데이터 무결성을 유지하는 데 도움이 됩니다.

CREATE TABLE Artist (
    ArtistID INTEGER,
    Name TEXT,
    PRIMARY KEY (ArtistID)
);

CREATE TABLE Album (
    AlbumID INTEGER,
    Title TEXT,
    ArtistID INTEGER,
    ReleaseYear INTEGER,
    PRIMARY KEY (AlbumID),
    FOREIGN KEY (ArtistID, ReleaseYear)
        REFERENCES Artist(ArtistID, ReleaseYear)
);

이 예시에서는 Album 테이블의 ArtistID와 ReleaseYear 열이 Artist 테이블의 ArtistID와 ReleaseYear 열을 참조하는 복합 외래 키를 형성합니다. Album 테이블에 삽입된 모든 행은 Artist 테이블에 존재하는 해당 아티스트 ID와 발매 연도의 조합을 가져야 합니다.

 

연기된 외래 키 제약 조건

연기된 외래 키 제약 조건(Deferred Foreign Key Constraints)는 트랜잭션이 커밋되는 시점까지 외래 키 제약 조건의 검사를 연기할 수 있게 해줍니다. 기본적으로 SQLite에서 외래 키 제약 조건은 즉시(즉, 데이터가 삽입되거나 수정될 때) 검사됩니다. 그러나 연기된 외래 키 제약 조건을 사용하면, 트랜잭션 내에서 일시적으로 외래 키 제약 조건을 위반하는 상태를 허용하고, 트랜잭션이 성공적으로 커밋되기 전에 모든 외래 키 제약 조건이 만족되도록 할 수 있습니다.

BEGIN TRANSACTION;

CREATE TABLE Parent (
    ID INTEGER PRIMARY KEY
);

CREATE TABLE Child (
    ID INTEGER,
    ParentID INTEGER,
    FOREIGN KEY (ParentID)
        REFERENCES Parent(ID) DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO Child (ID, ParentID) VALUES (1, 1);
-- Parent 테이블에 아직 ID 1이 존재하지 않음
INSERT INTO Parent (ID) VALUES (1);

COMMIT; -- 트랜잭션 커밋 시 모든 외래 키 제약 조건이 검사됨

이 예시에서 Child 테이블의 ParentID 열은 연기된 외래 키 제약 조건을 가지며, Parent 테이블의 ID 열을 참조합니다. 트랜잭션 내에서 Parent 테이블에 해당 ID를 가진 행이 삽입되기 전에 Child 테이블에 먼저 데이터를 삽입할 수 있습니다. 트랜잭션이 커밋될 때 모든 외래 키 제약 조건이 만족되어야만 실제 커밋이 성공적으로 이루어집니다.

복합 외래 키 제약 조건과 연기된 외래 키 제약 조건은 SQLite에서 관계의 복잡성을 관리하고, 데이터 무결성을 유지하는 데 필수적인 기능입니다.

 

수정 및 삭제 시 동작

외래 키 ON DELETE와 ON UPDATE 절은 부모 테이블의 행을 삭제하거나(ON DELETE), 기존 행의 부모 키 값을 수정할 때(ON UPDATE) 발생하는 동작을 구성하는 데 사용됩니다. 단일 외래 키 제약 조건은 ON DELETE와 ON UPDATE에 대해 서로 다른 동작을 구성할 수 있습니다. 외래 키 동작은 많은 면에서 트리거와 유사합니다.

각 외래 키에 연결된 ON DELETE와 ON UPDATE 동작은 NO ACTION, RESTRICT, SET NULL, SET DEFAULT, 또는 CASCADE 중 하나입니다. 동작이 명시적으로 지정되지 않은 경우, 기본값은 NO ACTION입니다.

 

NO ACTION

NO ACTION을 구성하는 것은 그대로 해석됩니다: 부모 키가 데이터베이스에서 수정되거나 삭제될 때, 특별한 동작이 수행되지 않습니다.

 

RESTRICT

RESTRICT 동작은 애플리케이션이 하나 이상의 자식 키가 매핑된 부모 키를 삭제(ON DELETE RESTRICT의 경우)하거나 수정(ON UPDATE RESTRICT의 경우)하는 것을 금지한다는 것을 의미합니다. RESTRICT 동작 처리는 현재 문이 끝날 때가 아닌 필드가 업데이트되자마자 발생합니다.

 

SET NULL

SET NULL로 구성된 동작은 부모 키가 삭제되거나(ON DELETE SET NULL의 경우) 수정될 때(ON UPDATE SET NULL의 경우), 부모 키에 매핑된 자식 테이블의 모든 행의 자식 키 열이 NULL 값으로 설정되게 합니다.

 

SET DEFAULT

SET DEFAULT 동작은 SET NULL과 유사하지만, 각 자식 키 열이 NULL 대신 열의 기본값으로 설정됩니다. 열에 기본값이 어떻게 할당되는지에 대한 자세한 내용은 CREATE TABLE 문서를 참조하세요.

 

CASCADE

CASCADE 동작은 부모 키에 대한 삭제 또는 업데이트 작업을 각 종속 자식 키에 전파합니다. ON DELETE CASCADE 동작의 경우, 삭제된 부모 행과 연관된 자식 테이블의 각 행도 삭제됩니다. ON UPDATE CASCADE 동작의 경우, 종속 자식 키에 저장된 값이 새로운 부모 키 값과 일치하도록 수정됩니다.

CREATE TABLE Parent (
    ParentID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Child (
    ChildID INTEGER PRIMARY KEY,
    ParentID INTEGER,
    Name TEXT,
    FOREIGN KEY (ParentID)
        REFERENCES Parent(ParentID)
        ON DELETE CASCADE
        ON UPDATE SET NULL
);

이 예시에서는 Child 테이블의 ParentID가 Parent 테이블의 ParentID를 참조하는 외래 키로 설정되어 있습니다. ON DELETE CASCADE는 부모 테이블에서 행이 삭제될 때 해당 부모 키를 참조하는 자식 테이블의 행도 함께 삭제되도록 합니다. ON UPDATE SET NULL은 부모 키 값이 수정될 때 해당 값을 참조하는 자식 테이블의 ParentID 열이 NULL로 설정되도록 합니다. 이를 통해 데이터 무결성을 유지하고, 관계를 명확하게 정의할 수 있습니다.

 

외래 키 제약 조건 활성화하기

SQLite에서 외래 키 제약 조건은 기본적으로 비활성화되어 있습니다. 이를 활성화하려면, 데이터베이스 세션 시작 시 PRAGMA foreign_keys = ON;을 실행해야 합니다. 이 설정은 해당 데이터베이스 연결에 대해 유지되며, 다른 연결에서는 별도로 설정해야 합니다.