2부 · 데이터를 담는 그릇 (2) 관계와 외래키

← 테이블과 데이터 타입 · 목차 · 다음: 3부 INSERT·UPDATE·DELETE →

1부에서 "한 시트에 다 욱여넣지 말고 책·고객·주문을 쪼개 연결하자"고 했습니다. 이 챕터에서 그 연결을 실제로 만듭니다. 핵심 도구는 외래키(foreign key) 입니다. 끝에서 서점 스키마 전체를 완성합니다.

flowchart LR
    A[테이블 구조 정하기] --> B["관계 정하기<br/>외래키 · 일대다 · 다대다"]
    B --> C[행 추가]
    C --> E[묻기]

    classDef here fill:#e8d5f2,stroke:#9b59b6,color:#000,stroke-width:3px
    classDef dim fill:#eee,stroke:#bbb,color:#888
    class B here
    class A,C,E dim

3.1 왜 표를 쪼개는가: 정규화 맛보기

1부의 거대한 시트를 다시 봅시다. 문제는 같은 정보가 반복된다는 것이었습니다. 김하나가 세 번 주문하면 김하나의 이름·이메일이 세 번 적힙니다. 이메일을 바꾸면 세 곳을 다 고쳐야 하고, 하나라도 놓치면 데이터가 어긋납니다.

해법은 "한 가지 사실은 한 곳에만 적는다" 입니다. 김하나의 정보는 customers 테이블에 한 줄만 두고, 주문에서는 그 줄을 가리키기만 합니다. 이렇게 중복을 제거하며 표를 나누는 설계 원칙을 정규화(normalization) 라고 합니다.

flowchart LR
    subgraph before["쪼개기 전 (나쁨)"]
        direction TB
        b1["주문1: 모비딕, 김하나, hana@mail.com"]
        b2["주문2: 데미안, 김하나, hana@mail.com"]
        b3["주문3: 1984, 김하나, hana@mail.com"]
    end
    subgraph after["쪼갠 후 (좋음)"]
        direction TB
        c["customers<br/>1: 김하나, hana@mail.com  ← 한 번만!"]
        o["orders<br/>주문1 → 고객1 → 책1<br/>주문2 → 고객1 → 책2<br/>주문3 → 고객1 → 책3"]
        o -.가리킴.-> c
    end
    before -->|정규화| after

    classDef bad fill:#f5c6cb,stroke:#c0392b,color:#000
    classDef good fill:#d5f2e0,stroke:#27ae60,color:#000
    class b1,b2,b3 bad
    class c,o good

정규화에는 1정규형·2정규형·3정규형… 같은 단계가 있지만, 입문 단계에서 외울 필요는 없습니다. 실용적인 한 줄 원칙으로 충분합니다: 하나의 사실은 한 테이블, 한 줄에만. 책 정보는 books에, 고객 정보는 customers에, "누가 무엇을 샀다"는 사건만 orders에.

솔직한 균형. 정규화는 중복과 불일치를 막지만, 데이터를 꺼낼 때 표 여러 개를 다시 합쳐야 합니다(그게 5부의 JOIN입니다). 너무 잘게 쪼개면 질의가 복잡해지고 느려질 수 있어, 실무에선 의도적으로 약간의 중복을 허용하기도 합니다(역정규화, denormalization). 입문에선 "적절히 쪼개 중복을 줄인다"를 기본으로 삼고, 트레이드오프가 있다는 것만 알아 두면 됩니다.

3.2 외래키(foreign key): 표와 표를 잇는 끈

표를 쪼갰으면 다시 이어야 합니다. orders의 한 행이 "어느 고객의, 어느 책 주문인지"를 어떻게 가리킬까요? 답은 단순합니다. 상대 테이블의 기본키 값을 적어 둡니다.

customers의 김하나가 id = 1, books의 모비딕이 id = 1이라면, "김하나가 모비딕을 샀다"는 주문은 이렇게 표현됩니다.

CODE

orders
┌────┬─────────────┬──────────┬────────────┐
│ id │ customer_id │ book_id  │ order_date │
├────┼─────────────┼──────────┼────────────┤
│  1 │      1      │    1     │ 2026-01-03 │   ← 고객1이 책1을 샀다
└────┴─────────────┴──────────┴────────────┘
        │              │
        │              └──→ books.id 를 가리킴
        └──→ customers.id 를 가리킴

customer_id처럼 다른 테이블의 기본키를 가리키는 열을 외래키(foreign key) 라고 합니다. "외부(다른 표)의 키를 담는 칸"이라는 뜻입니다. 이름은 자유지만 관례적으로 상대테이블단수_id 형태(customer_id, book_id)를 씁니다.

외래키를 선언하면 데이터베이스가 지켜 준다

외래키는 그냥 적어 두기만 해도 동작하지만, CREATE TABLE에서 명시적으로 선언하면 데이터베이스가 무결성을 지켜 줍니다.

SQL

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    book_id     INTEGER NOT NULL,
    order_date  TEXT NOT NULL,
    quantity    INTEGER NOT NULL DEFAULT 1,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id)     REFERENCES books(id)
);

FOREIGN KEY (customer_id) REFERENCES customers(id)는 "이 칸의 값은 반드시 customers 테이블에 실제로 존재하는 id여야 한다"는 약속입니다. 덕분에 존재하지 않는 고객(예: customer_id = 999)의 주문을 넣으려 하면 거부됩니다. 데이터가 어긋나는 걸 데이터베이스가 막아 주는 것입니다. 이런 보호를 참조 무결성(referential integrity) 이라고 합니다.

SQLite의 함정 하나. SQLite는 호환성 때문에 외래키 검사를 기본적으로 꺼 둡니다. 위 선언을 해도, 연결할 때마다 아래를 한 번 켜 줘야 실제로 검사합니다.
```sql
PRAGMA foreign_keys = ON;
```
DB Browser는 보통 켜져 있지만, 파이썬 sqlite3로 연결하면 매번 꺼져 있으니 연결 직후 이 PRAGMA를 실행하세요(7부에서 다시 강조합니다). 안 켜면 외래키는 "문서상의 약속"일 뿐 강제되지 않습니다.

3.3 관계의 모양: 일대다와 다대다

테이블 사이 관계는 몇 가지 전형적인 모양을 띱니다.

일대다 (one-to-many) — 가장 흔함

한 고객은 여러 주문을 할 수 있지만, 한 주문은 한 고객의 것입니다. 이런 "한쪽은 하나, 반대쪽은 여럿"을 일대다라고 합니다. 외래키는 항상 '여럿' 쪽에 둡니다(주문이 고객을 가리킴). books와 orders도 마찬가지입니다(한 책은 여러 주문에 등장).

flowchart LR
    c["customers (한 명)"] -->|1 : 다| o["orders (여러 건)"]
    b["books (한 권)"] -->|1 : 다| o

    classDef t fill:#e8d5f2,stroke:#9b59b6,color:#000
    class c,b,o t

다대다 (many-to-many) — 중간 표가 필요

한 주문에 여러 책을 담고(장바구니), 한 책이 여러 주문에 담긴다면 이는 다대다입니다. 다대다는 외래키 둘을 가진 중간 테이블(연결 테이블, junction table) 로 풉니다. 예를 들어 한 주문에 여러 권을 담으려면 orders와 books 사이에 order_items를 둡니다.

flowchart LR
    o["orders"] --> oi["order_items<br/>(order_id, book_id, quantity)"]
    b["books"] --> oi

    classDef t fill:#e8d5f2,stroke:#9b59b6,color:#000
    class o,b,oi t

이 안내서의 선택. 다대다(장바구니)는 개념상 더 정확하지만 입문 단계에선 질의가 복잡해집니다. 그래서 우리 서점은 "한 주문 = 한 책" 으로 단순화해, orders에 book_id를 직접 둡니다(위 3.2의 설계). 여러 권을 한 번에 담는 장바구니 구조는 7부에서 확장으로 다룹니다. 입문에선 단순함이 미덕입니다.

3.4 서점 스키마 완성

이제 누적 프로젝트의 뼈대를 완성합니다. 아래 전체를 DB Browser나 파이썬에서 실행하세요. (재실행해도 되도록 DROP ... IF EXISTS를 앞에 둡니다. 연습용이므로 데이터가 있으면 지워지니 실데이터엔 쓰지 마세요.)

SQL

-- 외래키 검사 켜기 (SQLite는 기본 꺼짐)
PRAGMA foreign_keys = ON;

-- 깨끗이 다시 만들기 위해 기존 표 제거 (자식 표부터 지운다)
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS customers;

-- 1) 고객
CREATE TABLE customers (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT NOT NULL UNIQUE,        -- 이메일은 중복 불가
    joined TEXT NOT NULL DEFAULT (date('now'))  -- 가입일, 안 주면 오늘
);

-- 2) 책
CREATE TABLE books (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author    TEXT,
    price     INTEGER NOT NULL,
    stock     INTEGER NOT NULL DEFAULT 0,
    published TEXT
);

-- 3) 주문 (고객과 책을 잇는다)
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    book_id     INTEGER NOT NULL,
    order_date  TEXT NOT NULL DEFAULT (date('now')),
    quantity    INTEGER NOT NULL DEFAULT 1,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id)     REFERENCES books(id)
);

새로 등장한 조각들:

email TEXT NOT NULL UNIQUE — UNIQUE는 "이 칸에 같은 값이 두 번 올 수 없다". 같은 이메일로 두 계정을 못 만들게 막습니다.
DEFAULT (date('now')) — 값을 안 주면 오늘 날짜를 자동으로 넣습니다. date('now')는 SQLite 내장 함수로 현재 날짜를 '2026-06-29' 형식으로 돌려줍니다.

스키마(테이블 구조 전체)를 그림으로 보면:

erDiagram
    customers ||--o{ orders : "주문한다"
    books     ||--o{ orders : "담긴다"

    customers {
        INTEGER id PK
        TEXT    name
        TEXT    email "UNIQUE"
        TEXT    joined
    }
    books {
        INTEGER id PK
        TEXT    title
        TEXT    author
        INTEGER price
        INTEGER stock
        TEXT    published
    }
    orders {
        INTEGER id PK
        INTEGER customer_id FK
        INTEGER book_id FK
        TEXT    order_date
        INTEGER quantity
    }

||--o{는 일대다 관계 표시입니다(왼쪽 하나, 오른쪽 여럿). PK는 기본키, FK는 외래키. 이 다이어그램을 ER 다이어그램(개체-관계도, Entity-Relationship diagram) 이라고 하며, 데이터베이스 설계를 한눈에 보여 주는 표준 그림입니다.

외래키가 실제로 막는지 확인

다음을 시도해 보세요(외래키가 켜져 있어야 합니다).

SQL

-- 아직 customers·books가 비어 있는 상태에서
INSERT INTO orders (customer_id, book_id, order_date) VALUES (1, 1, '2026-01-03');

customers에 id=1인 고객이 없으므로 다음과 비슷한 오류로 거부됩니다.

CODE

FOREIGN KEY constraint failed

데이터베이스가 "존재하지 않는 고객의 주문은 못 받는다"며 막아 준 것입니다. 다음 부에서 고객과 책을 먼저 채운 뒤 주문을 넣으면 정상 동작합니다.

3.5 정리

중복과 불일치를 막기 위해 정보를 여러 테이블로 쪼갠다(정규화). 실용 원칙: 하나의 사실은 한 테이블·한 줄에만.
쪼갠 표는 외래키(foreign key) 로 잇는다. 외래키는 상대 테이블의 기본키 값을 담는 칸이며, '여럿' 쪽 테이블에 둔다.
외래키를 선언하면 데이터베이스가 참조 무결성을 지켜, 존재하지 않는 대상을 가리키는 행을 막는다. (SQLite는 PRAGMA foreign_keys = ON; 필요.)
관계는 보통 일대다(고객-주문)이고, 다대다는 중간 테이블로 푼다. 입문 서점은 "한 주문 = 한 책"으로 단순화했다.
UNIQUE(중복 금지), DEFAULT (date('now'))(오늘 날짜 자동) 같은 제약으로 표를 더 튼튼하게 만든다.

이제 그릇이 완성됐습니다. 다음 부에서 이 그릇에 실제 데이터를 넣고, 고치고, 지우고, 잘못된 데이터가 못 들어오게 제약으로 지키는 법을 배웁니다.

직접 해 보기

위 서점 스키마 전체를 실행해 세 테이블을 만드세요. SELECT name FROM sqlite_master WHERE type='table';로 셋 다 생겼는지 확인하세요.
우리 서점은 "한 주문 = 한 책"으로 단순화했습니다. 만약 한 주문에 여러 책을 담아야 한다면 어떤 테이블을 추가로 둬야 할까요? 열 구성을 종이에 그려 보세요. (힌트: 3.3의 order_items)
customers에 같은 이메일로 두 고객을 넣어 보세요. 두 번째 INSERT에서 어떤 일이 일어나나요? 어떤 제약 때문인가요?
orders에서 FOREIGN KEY 두 줄을 뺀 버전과 넣은 버전의 차이를, "존재하지 않는 book_id를 넣을 때"를 기준으로 설명해 보세요.

← 테이블과 데이터 타입 · 목차 · 다음: 3부 INSERT·UPDATE·DELETE →

← 이전2부 · 데이터를 담는 그릇 (1) 테이블과 데이터 타입다음 →3부 · 데이터 넣고 고치고 지우기 (DML)