ONE-TO-MANY 관계 모델링하기

이제 우리 실력을 좀 더 업그레이드해서, 관계형 데이터베이스에서 진짜 자주 나오는 ONE-TO-MANY 관계를 만들어보자.

작은 회사를 상상해봐. 한 직원은 한 부서에서만 일할 수 있지만, 한 부서에는 여러 명의 직원이 있을 수 있지. 현실 세계에선 직원과 부서라는 두 객체가 있고, 이 둘의 관계는 "한 부서에 여러 직원이 속할 수 있다" 또는 공식적으로 "일대다" (ONE-TO-MANY)라고 할 수 있어.

이런 ONE-TO-MANY 관계는 진짜 어디서나 볼 수 있어:

• 한 고객이 여러 주문을 할 수 있음;
• 한 작가가 여러 책을 쓸 수 있음;
• 한 선생님이 여러 학생을 가르칠 수 있음.

관계형 데이터베이스에서 ONE-TO-MANY 관계는 외래키 (FOREIGN KEY)를 써서 구현해. "다" (MANY) 테이블의 컬럼 중 하나가 "일" (ONE) 테이블의 기본키를 참조하는 거지.

ONE-TO-MANY 관계 만드는 방법

클래식한 예시로 가보자: 고객과 주문의 관계. 한 고객이 여러 주문을 할 수 있지만, 각 주문은 오직 한 고객에게만 속해. 우리는 두 개의 테이블을 만들 거야: customers (고객)와 orders (주문).

customers 테이블

이게 바로 "일" 테이블이야. 고객 정보를 저장하지.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- 고객의 고유 식별자
    name TEXT NOT NULL              -- 고객 이름
);

orders 테이블

이 테이블이 "다" 테이블이야. 주문을 저장하고, 각 주문마다 customer_id라는 외래키가 있어서 customers 테이블의 customer_id를 참조해.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- 주문의 고유 식별자
    order_date DATE NOT NULL,              -- 주문 날짜
    customer_id INT REFERENCES customers(customer_id) -- 외래키
);

실전에서 써보기

데이터 삽입하기

이제 테이블에 데이터를 좀 넣어서 관계가 잘 동작하는지 확인해보자.

customers 테이블에 고객을 추가해보자:

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

결과:

orders 테이블에 주문을 추가해보자:

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Ada의 주문
    ('2023-10-02', 2),  -- Grace의 주문
    ('2023-10-03', 1);  -- Ada의 또 다른 주문

orders 테이블:

주의! 주문을 추가할 때는 반드시 존재하는 customer_id를 써야 해. 없는 ID를 넣으려고 하면 DB가 에러를 뱉어. 이게 바로 데이터 무결성 보호야.

관계 확인하기

이제 테이블이 어떻게 연결되어 있는지 보자. 예를 들어, Ada Lovelace가 한 주문은 뭐가 있을까?

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

결과:

여기서 JOIN을 써서 두 테이블을 외래키로 연결했어. 깔끔하고, 중복 데이터도 없지!

이게 왜 필요할까?

ONE-TO-MANY 관계는 현실에서 엄청 자주 쓰이고 진짜 유용해. 예를 들어, 온라인 쇼핑몰에 수천 명의 고객과 수백만 개의 주문이 있다고 해보자. 주문마다 고객 정보를 계속 복붙하는 대신, 고객 정보는 한 테이블에만 저장하고 주문은 따로 관리하는 거지. 이렇게 하면 데이터도 줄고, DB도 훨씬 정돈돼.

그리고 데이터를 연결할 수 있으니까, 분석용 쿼리도 쉽게 만들 수 있어. 예를 들어, "각 고객이 몇 번 주문했지?" 또는 "지난달에 주문한 고객은 누구지?" 같은 것도 바로 뽑아낼 수 있지.

어려운 점과 함정

초보들이 자주 실수하는 부분이 있어:

외래키를 안 넣음. "다" 테이블에 외래키를 안 넣으면, 관계가 머릿속에만 있고 DB는 그걸 모름. 그러면 주문이 존재하지 않는 고객을 가리키는 "깨진" DB가 될 수도 있어.

"일" 테이블에서 레코드 삭제 시도. 예를 들어, customers에서 고객을 삭제하면, orders에 그 고객의 주문이 "고아"가 돼버려. 이걸 막으려면 ON DELETE CASCADE를 써서, 고객을 지울 때 그 고객의 주문도 같이 지워지게 할 수 있어.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

이제 고객을 삭제하면:

DELETE FROM customers WHERE customer_id = 1;

그 고객의 주문도 같이 삭제돼. DB가 완전 깔끔해지는 거지.

데이터 삽입 에러. 존재하지 않는 customer_id로 주문을 넣으려고 하면 이런 에러가 나와:

ERROR:  insert or update on table "orders" violates foreign key constraint