DDL 과 제약 조건 - 무결성을 스키마로 지키기

이 장에서 배우는 것

앞 장에서는 COMMIT과 ROLLBACK으로 트랜잭션이 데이터를 어떻게 바꾸는지 살펴봤다. 이번 장은 한 단계 앞으로 가서, 애초에 잘못된 데이터가 테이블에 들어오지 못하도록 스키마 자체가 무결성(integrity)을 지키게 만드는 방법을 다룬다. 온라인 서점의 회원·도서·주문·주문상세·리뷰 테이블을 그대로 쓰되, 이번에는 CREATE TABLE과 ALTER TABLE 문 안에 제약 조건(constraint)을 어떻게 배치하는지에 초점을 맞춘다.

• 기본 키(PK)·외래 키(FK)·고유 키(UNIQUE)·검사 제약(CHECK)·NOT NULL 다섯 가지가 각각 무엇을 막는지 구분한다
• 외래 키 삭제 옵션 CASCADE·RESTRICT·SET NULL의 동작 차이를 예측한다
• ALTER TABLE로 기존 컬럼을 바꿀 때 데이터가 깨지지 않는 순서를 안다
• 뷰(view)가 주는 이점과 한계를 구분해서 언제 뷰를 쓸지 판단한다

문제 상황

서점 서비스를 운영하다 보면 비슷한 사고가 반복된다. 어느 날 회원 탈퇴 처리 배치가 돈 뒤 리뷰 게시판이 텅 비어 있었다. 원인을 찾아보니 회원 테이블을 참조하는 외래 키에 ON DELETE CASCADE가 걸려 있었고, 회원을 지우면 그 회원이 쓴 리뷰까지 통째로 사라지도록 설계돼 있었다. 리뷰는 다른 독자에게도 의미 있는 콘텐츠인데, 작성자가 탈퇴했다는 이유만으로 함께 지워질 이유는 없었다.

비슷한 시기에 같은 이메일로 계정이 두 개 만들어지는 일도 있었다. 가입 로직은 "이메일 중복 체크 후 저장"이었지만, 이 검사와 저장 사이에 다른 요청이 끼어들면서 같은 이메일이 두 번 저장됐다. 애플리케이션 코드만으로는 동시성 상황을 완전히 막기 어렵다.

운영 데이터가 쌓인 뒤 도서 테이블에 컬럼을 하나 추가하고 바로 NOT NULL을 걸었다가 배포가 실패한 적도 있다. 기존 행에는 채워 넣을 값이 없었기 때문이다. 이 장에서는 이 세 가지 사고를 스키마 설계로 막는 방법, 즉 제약 조건과 삭제 옵션, 안전한 ALTER 순서를 다룬다.

제약 조건 다섯 가지: 무결성을 스키마로 고정하기

제약 조건은 "이 테이블에는 이런 값만 허용한다"는 규칙을 데이터베이스 엔진이 직접 검사하게 만드는 장치다. 애플리케이션 코드가 실수하거나 우회 경로(배치, 다른 서비스, 수동 쿼리)로 데이터가 들어와도 규칙은 그대로 지켜진다.

기본 키(PRIMARY KEY)는 한 행을 유일하게 식별하는 컬럼이다. 값이 중복되는 것과 NULL이 들어오는 것을 동시에 막는다. 외래 키(FOREIGN KEY, FK)는 다른 테이블에 실제로 존재하는 값만 참조하도록 강제한다. 예를 들어 orders.member_id는 member 테이블에 없는 회원 번호를 담을 수 없다. UNIQUE는 기본 키가 아닌 컬럼에도 "중복 금지"를 걸 수 있는 제약으로, member.email이나 book.isbn처럼 업무상 유일해야 하는 값에 쓴다. CHECK는 값의 범위나 목록을 제한한다. review.rating이 1에서 5 사이여야 한다는 규칙이 대표적이다. NOT NULL은 가장 단순하지만 가장 자주 빠뜨리는 제약으로, 반드시 값이 있어야 하는 컬럼에 건다.

다섯 가지는 서로 배타적이지 않다. 기본 키 컬럼은 이미 NOT NULL과 UNIQUE 성격을 함께 갖고, 외래 키 컬럼에 CHECK를 추가로 걸 수도 있다. 이 장의 예제 스키마는 다섯 가지를 모두 한 번씩 쓴다.

FK 삭제 옵션: CASCADE·RESTRICT·SET NULL 고르는 기준

외래 키를 정의할 때는 참조 대상뿐 아니라 "부모 행이 지워지면 자식 행을 어떻게 할지"도 함께 정해야 한다. 이 절에서는 세 가지 옵션의 동작을 비교한다.

FK ON DELETE 옵션별 동작

옵션	부모 삭제 시 자식 행	이 장에서 쓴 곳	주의점
CASCADE	자식 행도 함께 삭제된다	orders → order_detail, book → review	이력까지 함께 사라지므로 지워도 되는 관계에만 쓴다
RESTRICT	자식 행이 남아 있으면 삭제 자체가 실패한다	member → orders, book → order_detail	주문 이력처럼 보존해야 하는 관계에 쓴다
SET NULL	부모는 삭제되고 자식의 FK 컬럼만 NULL로 바뀐다	member → review	FK 컬럼이 NOT NULL이면 쓸 수 없다
NO ACTION	제약 검사 시점만 다를 뿐 RESTRICT와 사실상 같다	이 장 예제에서는 쓰지 않음	일부 제품은 트랜잭션이 끝날 때까지 검사를 미룬다

한 부모 테이블을 여러 자식 테이블이 참조할 때는 조심해야 한다. 예를 들어 member는 orders(RESTRICT)와 review(SET NULL) 양쪽에서 참조된다. 주문이 있는 회원을 지우려 하면 review 쪽 규칙과 상관없이 orders 쪽 RESTRICT가 먼저 걸려 삭제 자체가 실패한다. 즉 여러 FK 중 하나라도 RESTRICT면 그 관계가 남아 있는 한 부모는 지워지지 않는다. MySQL 공식 문서는 이런 옵션 조합을 CREATE TABLE 구문과 함께 설명한다.

ALTER로 컬럼을 바꿀 때와 뷰를 쓸 때 주의할 점

컬럼 타입·제약을 바꾸는 안전한 순서

운영 중인 테이블에 이미 행이 있는 상태에서 컬럼을 추가하거나 제약을 바꿀 때는 순서가 중요하다. NOT NULL 컬럼을 DEFAULT 없이 바로 추가하면, 기존 행에는 채워 넣을 값이 없어 실패한다. 안전한 순서는 세 단계다.

MySQL과 Oracle의 문법 차이

같은 개념이라도 두 제품은 문법과 기본 동작이 조금 다르다. 시험이나 실무에서 제품을 바꿔 쓸 때 헷갈리기 쉬운 부분을 표로 정리한다.

제약 조건과 ALTER 문법의 제품별 차이

항목	MySQL 8	Oracle
CHECK 제약 조건	8.0.16부터 저장 엔진이 직접 검사한다	초기 버전부터 지원한다
ON DELETE 옵션 표기	CASCADE, SET NULL, RESTRICT, NO ACTION을 모두 쓸 수 있다	CASCADE와 SET NULL만 있고 RESTRICT 키워드는 없다. 생략하면 제한 동작이 기본이다
컬럼 변경 문법	ALTER TABLE t MODIFY COLUMN col 타입	ALTER TABLE t MODIFY (col 타입) 처럼 괄호를 쓴다
뷰 재정의	CREATE OR REPLACE VIEW 지원	CREATE OR REPLACE VIEW 지원

Oracle의 ALTER TABLE 구문은 Oracle 공식 문서에서 괄호 표기까지 확인할 수 있다.

뷰가 주는 것과 잃는 것

뷰(view)는 SELECT 문에 이름을 붙여 저장해 둔 것이다. 실제 데이터를 갖지 않고, 조회할 때마다 원본 테이블을 다시 읽어 결과를 계산한다. 복잡한 조인이나 집계를 매번 다시 적지 않아도 되고, 특정 컬럼만 노출해 나머지를 감추는 용도로도 쓸 수 있다. 반면 뷰 안의 쿼리가 무겁다면 조회할 때마다 그 비용을 그대로 치르고, 원본 테이블 구조가 바뀌면 뷰도 함께 깨질 수 있다. 조인이나 집계가 섞인 뷰는 갱신(UPDATE·INSERT·DELETE)이 안 되는 경우가 많다는 점도 기억해야 한다.

완성 코드

-- 온라인 서점: DDL과 제약 조건 예제 (MySQL 8 기준)
DROP TABLE IF EXISTS review;
DROP TABLE IF EXISTS order_detail;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS member;

CREATE TABLE member (
    member_id  INT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(100) NOT NULL,
    name       VARCHAR(50)  NOT NULL,
    grade      VARCHAR(10)  NOT NULL DEFAULT 'BRONZE',
    joined_at  DATE         NOT NULL,
    CONSTRAINT uq_member_email UNIQUE (email),
    CONSTRAINT ck_member_grade CHECK (grade IN ('BRONZE', 'SILVER', 'GOLD'))
);

CREATE TABLE book (
    book_id    INT AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    isbn       VARCHAR(20),
    price      INT NOT NULL,
    stock_qty  INT NOT NULL DEFAULT 0,
    CONSTRAINT uq_book_isbn UNIQUE (isbn),
    CONSTRAINT ck_book_price CHECK (price >= 0),
    CONSTRAINT ck_book_stock CHECK (stock_qty >= 0)
);

CREATE TABLE orders (
    order_id    INT AUTO_INCREMENT PRIMARY KEY,
    member_id   INT NOT NULL,
    order_date  DATE NOT NULL,
    status      VARCHAR(10) NOT NULL DEFAULT 'READY',
    CONSTRAINT fk_orders_member FOREIGN KEY (member_id)
        REFERENCES member (member_id) ON DELETE RESTRICT,
    CONSTRAINT ck_orders_status CHECK (status IN ('READY', 'PAID', 'CANCELLED'))
);

CREATE TABLE order_detail (
    order_id  INT NOT NULL,
    book_id   INT NOT NULL,
    quantity  INT NOT NULL,
    PRIMARY KEY (order_id, book_id),
    CONSTRAINT fk_detail_order FOREIGN KEY (order_id)
        REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT fk_detail_book FOREIGN KEY (book_id)
        REFERENCES book (book_id) ON DELETE RESTRICT,
    CONSTRAINT ck_detail_qty CHECK (quantity > 0)
);

CREATE TABLE review (
    review_id  INT AUTO_INCREMENT PRIMARY KEY,
    member_id  INT,
    book_id    INT NOT NULL,
    rating     INT NOT NULL,
    content    VARCHAR(500),
    CONSTRAINT fk_review_member FOREIGN KEY (member_id)
        REFERENCES member (member_id) ON DELETE SET NULL,
    CONSTRAINT fk_review_book FOREIGN KEY (book_id)
        REFERENCES book (book_id) ON DELETE CASCADE,
    CONSTRAINT ck_review_rating CHECK (rating BETWEEN 1 AND 5)
);

INSERT INTO member (email, name, grade, joined_at) VALUES
    ('yuna@example.com',   '박유나', 'GOLD',   '2024-03-02'),
    ('minho@example.com',  '이민호', 'BRONZE', '2025-01-15'),
    ('haneul@example.com', '정하늘', 'SILVER', '2025-08-20');

INSERT INTO book (title, isbn, price, stock_qty) VALUES
    ('SQL 기초',          '979-11-0001', 22000, 5),
    ('데이터 모델링 실전', '979-11-0002', 27000, 0);

INSERT INTO orders (member_id, order_date, status) VALUES
    (1, '2026-05-01', 'PAID'),
    (2, '2026-05-03', 'READY');

INSERT INTO order_detail (order_id, book_id, quantity) VALUES
    (1, 1, 2),
    (1, 2, 1),
    (2, 1, 1);

INSERT INTO review (member_id, book_id, rating, content) VALUES
    (1, 1, 5, '설명이 친절하다'),
    (2, 1, 4, '예제가 실무에 가깝다'),
    (3, 2, 3, '표지가 예쁘다');

-- 주문 이력이 없는 회원은 탈퇴시켜도 리뷰가 남는다 (SET NULL)
DELETE FROM member WHERE member_id = 3;

-- 주문을 취소하면 주문상세도 함께 지운다 (CASCADE)
DELETE FROM orders WHERE order_id = 2;

-- 기존 행이 있는 테이블에 NOT NULL 컬럼을 안전하게 추가한다
ALTER TABLE book ADD COLUMN published_year INT;

UPDATE book SET published_year = 2024 WHERE book_id = 1;
UPDATE book SET published_year = 2025 WHERE book_id = 2;

ALTER TABLE book MODIFY COLUMN published_year INT NOT NULL;

-- 도서별 판매 수량을 보여주는 뷰
CREATE VIEW book_sales_summary AS
SELECT b.book_id, b.title, SUM(od.quantity) AS total_qty
FROM book b
JOIN order_detail od ON od.book_id = b.book_id
GROUP BY b.book_id, b.title;

줄별 해설

• member.email에 건 uq_member_email이 같은 이메일이 두 번 저장되는 것을 막는다. name·joined_at의 NOT NULL은 반드시 있어야 하는 값을 강제한다.
• book의 ck_book_price, ck_book_stock은 가격과 재고가 음수가 되는 것을 막는다. isbn은 UNIQUE이면서도 NOT NULL은 아니어서, 아직 ISBN이 없는 도서도 등록할 수 있다.
• orders.fk_orders_member는 ON DELETE RESTRICT다. 주문 이력이 있는 회원은 탈퇴 자체가 막힌다.
• order_detail은 (order_id, book_id) 복합 기본 키로 같은 주문에 같은 책이 두 번 들어가는 것을 막는다. fk_detail_order는 CASCADE, fk_detail_book은 RESTRICT로 서로 다른 옵션을 준 이유는 앞 절에서 다뤘다.
• review.member_id는 NULL을 허용하고 ON DELETE SET NULL을 걸었다. 회원이 탈퇴해도 리뷰 내용은 남기고 작성자 정보만 지운다. review.book_id는 NOT NULL이면서 ON DELETE CASCADE라, 책이 삭제되면 그 책에 달린 리뷰도 함께 지워진다.
• DELETE FROM member WHERE member_id = 3;은 정하늘 회원에게 주문이 없어 RESTRICT에 걸리지 않고 성공한다. 이때 review_id 3의 member_id가 SET NULL에 따라 NULL로 바뀐다.
• DELETE FROM orders WHERE order_id = 2;는 CASCADE에 따라 order_detail의 (2, 1) 행도 함께 지운다.
• ALTER TABLE book ADD COLUMN published_year INT;는 NULL을 허용하는 컬럼을 먼저 추가한다. 이어지는 두 UPDATE로 기존 행을 모두 채운 뒤에야 MODIFY COLUMN ... NOT NULL로 제약을 건다. 순서를 바꾸면 실패한다(뒤에서 다룬다).
• book_sales_summary 뷰는 book과 order_detail을 조인하고 수량을 합산한다. 뷰 자체는 데이터를 저장하지 않고, SELECT할 때마다 이 쿼리를 다시 실행한다.

실행 결과

스크립트를 실행한 뒤 아래 조회 결과로 CASCADE·SET NULL·NOT NULL 백필이 의도대로 적용됐는지 확인한다.

mysql> SELECT review_id, member_id, book_id, rating FROM review ORDER BY review_id;
review_id | member_id | book_id | rating
1         | 1         | 1       | 5
2         | 2         | 1       | 4
3         | NULL      | 2       | 3

mysql> SELECT order_id, book_id, quantity FROM order_detail ORDER BY order_id, book_id;
order_id | book_id | quantity
1        | 1       | 2
1        | 2       | 1

mysql> SELECT book_id, published_year FROM book ORDER BY book_id;
book_id | published_year
1       | 2024
2       | 2025

mysql> SELECT * FROM book_sales_summary ORDER BY book_id;
book_id | title                | total_qty
1       | SQL 기초             | 2
2       | 데이터 모델링 실전    | 1

실무에서 자주 틀리는 것

FK에 ON DELETE를 명시하지 않고 기본값을 막연히 믿기

틀린 코드는 삭제 옵션을 아예 적지 않는다.

CONSTRAINT fk_detail_book FOREIGN KEY (book_id)
    REFERENCES book (book_id)

생략하면 대부분 제한 동작(RESTRICT나 NO ACTION에 가까운 동작)이 되지만, 이 사실을 코드만 보고는 알 수 없다. "책을 지웠는데 왜 실패하는지" 원인을 찾느라 시간을 쓰게 된다. 의도를 코드에 남기도록 명시적으로 적는다.

CONSTRAINT fk_detail_book FOREIGN KEY (book_id)
    REFERENCES book (book_id) ON DELETE RESTRICT

데이터가 있는 컬럼에 NOT NULL을 곧바로 붙이기

틀린 코드는 DEFAULT 없이 NOT NULL 컬럼을 바로 추가한다.

ALTER TABLE book ADD COLUMN edition INT NOT NULL;

book에는 이미 두 행이 있고 edition에 채울 값이 없으므로 이 문장 자체가 실패한다. 앞서 본 3단계(추가 → 백필 → NOT NULL)로 고친다.

ALTER TABLE book ADD COLUMN edition INT;
UPDATE book SET edition = 1;
ALTER TABLE book MODIFY COLUMN edition INT NOT NULL;

애플리케이션 코드로만 중복을 막기

틀린 접근은 저장 전에 존재 여부만 확인한다.

SELECT COUNT(*) FROM member WHERE email = '입력값';
-- 0이면 INSERT 실행

두 요청이 거의 동시에 이 확인을 통과하면 같은 이메일이 두 번 저장된다. DB에 UNIQUE 제약을 걸고, 저장 시점의 중복 오류를 애플리케이션이 처리하는 방식으로 고친다.

CONSTRAINT uq_member_email UNIQUE (email)
-- INSERT 시 중복 오류가 나면 애플리케이션이 "이미 가입된 이메일"로 응답

집계 뷰를 원본 테이블처럼 갱신하려 하기

틀린 코드는 조인과 집계가 섞인 뷰에 직접 값을 쓰려고 한다.

UPDATE book_sales_summary SET total_qty = 10 WHERE book_id = 1;

book_sales_summary는 book과 order_detail을 조인하고 SUM으로 묶은 결과라 갱신 가능한 뷰의 조건을 벗어난다. 데이터를 바꾸려면 원본 테이블을 직접 갱신한다.

UPDATE order_detail SET quantity = 10 WHERE order_id = 1 AND book_id = 1;

한눈에 보기

제약 조건 다섯 가지가 막는 것

제약 조건	막는 것	이 장 예시
PRIMARY KEY	값 중복, NULL 유입	order_detail(order_id, book_id) 복합 키
FOREIGN KEY	부모에 없는 값을 자식이 참조하는 것	orders.member_id → member.member_id
UNIQUE	유일해야 하는 컬럼의 중복	member.email, book.isbn
CHECK	정해둔 범위·목록을 벗어난 값	review.rating BETWEEN 1 AND 5
NOT NULL	반드시 있어야 하는 값이 비는 것	member.name, book.title

연습 문제

1. 다음 설명에 맞는 FK 삭제 옵션을 쓰라. "부모 행이 삭제돼도 자식 행은 남기고, 참조하던 FK 컬럼 값만 NULL로 바꾼다."
2. 아래 문장을 순서대로 실행하면 어느 문장에서, 왜 오류가 나는지 설명하고 고쳐 쓰라.

   ALTER TABLE book ADD COLUMN edition INT NOT NULL;
   UPDATE book SET edition = 1;

3. order_detail.book_id의 FK에 RESTRICT 대신 ON DELETE CASCADE를 걸면 이 장의 스키마에서 어떤 문제가 생기는지 서술하라.
4. book_sales_summary 뷰에 UPDATE 문을 실행하면 어떤 문제가 있을 수 있는지, 그리고 이런 집계 뷰를 실무에서 안전하게 쓰는 방법을 서술하라.

정답과 해설

1. SET NULL이다. CASCADE는 자식 행까지 지우고, RESTRICT·NO ACTION은 삭제 자체를 막는다. review.member_id에 건 것과 같은 방식이다.
2. 첫 번째 문장 ALTER TABLE book ADD COLUMN edition INT NOT NULL;에서 오류가 난다. book에는 이미 행이 있는데 NOT NULL 컬럼을 DEFAULT 없이 추가하면 기존 행에 채울 값이 없기 때문이다. 컬럼을 먼저 NULL 허용으로 추가하고, UPDATE로 기존 행을 채운 다음, 마지막에 ALTER TABLE book MODIFY COLUMN edition INT NOT NULL;로 제약을 거는 순서로 고친다.
3. book_id를 참조하는 order_detail에 CASCADE를 걸면, 도서를 삭제할 때 그 책이 포함된 모든 주문상세 행이 함께 사라진다. 이미 결제까지 끝난 주문의 이력이 통째로 지워지는 셈이라, 이 장에서는 RESTRICT를 선택해 주문 이력에 남아 있는 책은 삭제 자체를 막았다.
4. book_sales_summary는 book과 order_detail을 조인하고 SUM으로 집계한 뷰라 갱신 가능한 뷰의 조건을 벗어난다. UPDATE를 실행하면 오류가 나거나 애초에 허용되지 않는다. 집계·조인이 섞인 뷰는 조회 전용으로만 쓰고, 데이터를 바꿔야 하면 order_detail 같은 원본 테이블을 직접 갱신해야 한다.