키와 무결성 제약 - 틀린 데이터를 DB 가 막게 하기

이 장에서 배우는 것

앞 장에서는 관계 모델이 세상을 릴레이션, 튜플, 속성으로 표현한다는 것을 배웠다. 그런데 릴레이션에 아무 값이나 자유롭게 넣을 수 있다면 표는 그냥 표일 뿐, 신뢰할 수 있는 데이터베이스가 되지 못한다. 같은 회원이 두 번 등록되거나, 존재하지 않는 출판사를 가진 책이 저장되거나, 가격이 음수인 책이 들어와도 아무 일도 일어나지 않는다면 그 데이터를 믿고 집계나 조인을 할 수 없다. 이번 장에서는 릴레이션 안에서 튜플을 구별하는 키와, 데이터베이스가 스스로 잘못된 값을 걸러내는 제약(constraint)을 SQL 연구소의 온라인 서점 데이터로 확인한다.

• 슈퍼키·후보키·기본키·대체키·외래키를 구별해서 말할 수 있다
• 개체 무결성·참조 무결성·도메인 무결성이 각각 무엇을 지키는지 설명할 수 있다
• NOT NULL, UNIQUE, CHECK, FOREIGN KEY 제약을 CREATE TABLE 문으로 선언할 수 있다
• 제약을 위반하는 INSERT 문이 왜, 어떤 메시지로 실패하는지 읽을 수 있다

문제 상황

SQL 연구소의 서점 담당자가 신간을 등록하는 작업을 하루에도 여러 번 반복한다고 하자. 엑셀 표였다면 잘못 입력한 셀을 발견하는 즉시 고쳐 쓰면 그만이다. 하지만 실제 서비스에서는 담당자가 입력을 끝내는 순간 그 행이 곧바로 다른 화면과 배치 작업에서 읽힌다. 담당자가 같은 isbn을 실수로 두 번 입력하면 재고 화면에 같은 책이 두 줄로 뜨고, 아직 등록도 안 된 publisher_id를 넣으면 출판사 이름을 조인해서 보여주는 화면에서 그 자리만 비게 된다. 가격 칸에 마이너스 부호가 잘못 눌려 들어가면 매출 집계가 슬쩍 줄어들지만 아무도 즉시 알아채지 못한다.

이런 문제의 공통점은 "값 자체는 저장할 수 있는 형태이지만 의미상 있어서는 안 되는 값"이라는 점이다. 데이터베이스는 이런 값을 애플리케이션 코드의 판단에만 맡기지 않고, 테이블을 만드는 시점에 규칙으로 선언해서 스스로 막을 수 있다. 그 규칙을 정의하는 도구가 키와 무결성 제약이다.

키의 종류

키(key)는 릴레이션에서 튜플 하나를 다른 튜플과 구별해 주는 속성, 또는 속성의 집합이다. 서점 데이터의 member 테이블을 예로 들면 id 하나만으로도 회원을 구별할 수 있고, email 하나만으로도 구별할 수 있다. 이렇게 후보가 여러 개일 때 이를 정리하는 용어가 슈퍼키, 후보키, 기본키, 대체키다.

슈퍼키와 후보키

슈퍼키(super key)는 튜플을 유일하게 구별할 수 있는 속성의 집합이다. member 테이블에서 {id}도 슈퍼키이고, {id, email}도 슈퍼키이며, 심지어 {id, email, name}도 슈퍼키다. id 하나만으로 이미 유일성이 보장되므로 email이나 name을 더 붙여도 여전히 유일하기 때문이다. 문제는 이 정의만으로는 불필요하게 큰 집합까지 전부 슈퍼키로 인정한다는 점이다.

후보키(candidate key)는 슈퍼키 중에서 최소성(minimality)까지 만족하는 것이다. 즉 어떤 속성을 하나라도 빼면 더 이상 유일성을 보장하지 못하는, 꼭 필요한 만큼만 담은 슈퍼키다. member 테이블에서는 {id}와 {email}이 각각 후보키가 될 수 있다. {id, email}은 유일하긴 하지만 email을 빼도 id만으로 유일하므로 최소가 아니어서 후보키가 아니다.

기본키와 대체키

후보키가 여러 개일 때 테이블을 대표해서 실제로 사용할 하나를 고른 것이 기본키(primary key)다. 서점 데이터에서는 대부분의 테이블이 id를 기본키로 쓴다. 기본키로 뽑히지 않은 나머지 후보키는 대체키(alternate key)라고 부른다. member.email은 기본키로 쓰이지 않지만 여전히 값이 유일해야 하므로 UNIQUE 제약으로 그 성질을 지킨다.

외래키

외래키(foreign key)는 다른 테이블(또는 같은 테이블)의 기본키나 후보키를 가리키는 속성이다. book.publisher_id는 publisher.id를 가리키는 외래키이고, book.category_id는 category.id를 가리킨다. category 테이블은 parent_id가 같은 테이블의 id를 가리키는 자기 참조 외래키로, 2단계 계층(대분류·소분류)을 표현한다. 외래키는 값이 존재해야 한다는 제약이 아니라 값이 존재한다면 반드시 참조 대상 테이블에도 있어야 한다는 제약이다. NULL을 허용하는 외래키라면 참조하지 않는 상태 자체는 허용된다.

키의 종류와 서점 데이터의 예

키 종류	정의	서점 데이터 예시	비고
슈퍼키	유일성만 만족하는 속성 집합	{id, email}, {id, name}	후보키를 포함하는 모든 상위 집합
후보키	유일성과 최소성을 모두 만족	{id}, {email}, {isbn}	테이블에 여러 개 있을 수 있다
기본키	후보키 중 대표로 선택한 것	member.id, book.id	테이블마다 하나만 지정
대체키	기본키로 뽑히지 않은 후보키	member.email, book.isbn	UNIQUE 로 유일성을 지킨다
외래키	다른 테이블의 기본키를 가리킴	book.publisher_id → publisher.id	NULL 허용 여부는 따로 정한다

무결성 제약의 세 가지

키가 "어느 속성이 튜플을 구별하는가"를 정하는 개념이라면, 무결성 제약(integrity constraint)은 "그 값이 실제로 지켜야 할 규칙"을 데이터베이스에 선언해 두는 장치다. 서점 데이터에서는 크게 세 가지로 나눈다.

개체 무결성

개체 무결성(entity integrity)은 기본키가 NULL이 될 수 없고 중복될 수도 없다는 규칙이다. book.id가 두 개의 서로 다른 책에 같은 값으로 들어간다면 어떤 행을 가리키는 조인인지 알 수 없게 되므로, 기본키는 이 규칙을 반드시 지켜야 한다. SQL에서는 PRIMARY KEY 선언 하나로 NOT NULL과 UNIQUE를 동시에 강제한다.

참조 무결성

참조 무결성(referential integrity)은 외래키 값이 있다면 그 값이 참조 대상 테이블에 실제로 존재해야 한다는 규칙이다. book.publisher_id에 99라는 값을 넣으려면 publisher 테이블에 id가 99인 행이 먼저 있어야 한다. 그렇지 않은 INSERT는 거부된다. SQL에서는 FOREIGN KEY … REFERENCES로 선언한다.

도메인 무결성

도메인 무결성(domain integrity)은 한 열에 들어갈 수 있는 값의 범위와 형식을 지키는 규칙이다. 여기에는 두 갈래가 있다. 하나는 값이 반드시 있어야 한다는 필수값 규칙으로 NOT NULL로 선언한다. 다른 하나는 값이 정해진 범위나 목록 안에 있어야 한다는 규칙으로 CHECK로 선언한다. member.grade가 'BASIC', 'SILVER', 'GOLD', 'VIP' 중 하나여야 한다거나 book.price가 0보다 커야 한다는 규칙이 여기에 해당한다.

완성 코드

다음 스크립트는 SQL 연구소의 서점 테이블 중 일부를 만들고, 정상 데이터를 한 줄씩 넣은 뒤 다섯 가지 제약 위반 INSERT를 순서대로 실행한다. chapter03_constraints.sql 로 저장하고 SQLite 셸에서 그대로 실행하면 된다.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS publisher;
DROP TABLE IF EXISTS member;

CREATE TABLE member (
    id        INTEGER PRIMARY KEY,
    email     TEXT NOT NULL UNIQUE,
    name      TEXT NOT NULL,
    grade     TEXT NOT NULL CHECK (grade IN ('BASIC','SILVER','GOLD','VIP')),
    region    TEXT,
    joined_on TEXT NOT NULL
);

CREATE TABLE publisher (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE category (
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    parent_id INTEGER REFERENCES category(id)
);

CREATE TABLE book (
    id           INTEGER PRIMARY KEY,
    isbn         TEXT NOT NULL UNIQUE,
    title        TEXT NOT NULL,
    publisher_id INTEGER NOT NULL REFERENCES publisher(id),
    category_id  INTEGER NOT NULL REFERENCES category(id),
    price        INTEGER NOT NULL CHECK (price > 0),
    published_on TEXT NOT NULL,
    pages        INTEGER
);

CREATE TABLE inventory (
    book_id    INTEGER PRIMARY KEY REFERENCES book(id),
    stock      INTEGER NOT NULL CHECK (stock >= 0),
    updated_at TEXT NOT NULL
);

-- 기준이 되는 정상 데이터
INSERT INTO member (id, email, name, grade, region, joined_on)
VALUES (1, 'yuna@example.com', '이유나', 'GOLD', '서울', '2024-03-02');

INSERT INTO publisher (id, name) VALUES (1, '가온출판');
INSERT INTO category (id, name, parent_id) VALUES (1, '프로그래밍', NULL);

INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages)
VALUES (1, '979-11-0000-001-1', 'SQL 연구소 안내서', 1, 1, 22000, '2025-01-10', 320);

INSERT INTO inventory (book_id, stock, updated_at) VALUES (1, 12, '2026-09-01 09:00');

-- (1) 개체 무결성 위반: 이미 있는 기본키를 또 넣는다
INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages)
VALUES (1, '979-11-0000-002-8', '중복된 id', 1, 1, 15000, '2025-02-01', 200);

-- (2) 도메인 무결성 위반: NOT NULL 열에 NULL
INSERT INTO member (id, email, name, grade, region, joined_on)
VALUES (2, 'minho@example.com', NULL, 'BASIC', '부산', '2026-01-05');

-- (3) 도메인 무결성 위반: CHECK 범위를 벗어난 값
INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages)
VALUES (2, '979-11-0000-003-5', '가격이 이상한 책', 1, 1, -5000, '2025-03-01', 180);

-- (4) 대체키(UNIQUE) 위반: 이미 있는 이메일
INSERT INTO member (id, email, name, grade, region, joined_on)
VALUES (3, 'yuna@example.com', '박서준', 'SILVER', '대전', '2026-02-10');

-- (5) 참조 무결성 위반: 존재하지 않는 publisher_id
INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages)
VALUES (3, '979-11-0000-004-2', '유령 출판사의 책', 99, 1, 18000, '2025-04-01', 250);

줄별 해설

맨 앞의 PRAGMA foreign_keys = ON은 SQLite에서만 필요한 설정이다. SQLite는 하위 호환을 위해 외래키 검사를 기본으로 꺼 둔다. 이 줄을 빼면 FOREIGN KEY REFERENCES를 아무리 적어도 참조 무결성이 검사되지 않는다.

member 테이블의 id INTEGER PRIMARY KEY는 개체 무결성을 강제한다. email TEXT NOT NULL UNIQUE는 이메일을 대체키로 다루겠다는 선언이며, 필수값(NOT NULL)이면서 동시에 유일(UNIQUE)해야 한다는 두 규칙을 함께 건다. grade의 CHECK (grade IN (…))은 도메인 무결성 중 값의 목록을 제한하는 규칙이다. region에는 아무 제약이 없어 NULL을 허용하는데, 이는 지역 정보를 아직 모르는 회원을 표현하기 위한 의도적인 설계다.

category.parent_id INTEGER REFERENCES category(id)는 같은 테이블을 가리키는 외래키다. 최상위 대분류는 parent_id를 NULL로 두어 "참조하지 않음"을 표현하고, 소분류는 대분류의 id를 넣어 2단계 계층을 만든다.

book 테이블의 publisher_id와 category_id는 NOT NULL과 REFERENCES를 함께 걸어, 값이 반드시 있어야 하면서(도메인 무결성) 그 값이 실제 출판사·분류를 가리켜야 한다(참조 무결성)는 두 규칙을 동시에 적용한다. price INTEGER NOT NULL CHECK (price > 0)은 가격이 반드시 있어야 하고 0보다 커야 한다는 규칙이다.

inventory.book_id INTEGER PRIMARY KEY REFERENCES book(id)는 기본키이면서 동시에 외래키인 열이다. 한 책당 재고 행이 정확히 하나만 존재하도록 강제하면서, 그 book_id가 실제 book 테이블에 있는 값이어야 한다는 조건도 함께 지킨다.

(1)~(5)의 INSERT는 각각 개체 무결성, 도메인 무결성(필수값), 도메인 무결성(범위), 대체키의 유일성, 참조 무결성을 하나씩 어긴다. 순서대로 실행하면서 어떤 제약이 어떤 메시지로 실패하는지 비교해 보면 다섯 가지 규칙의 역할이 뚜렷하게 구분된다.

실행 결과

아래 명령으로 스크립트를 실행하면 정상 INSERT 다섯 줄은 조용히 성공하고, 제약을 위반한 다섯 줄만 오류 메시지를 남긴다. SQLite 셸은 기본적으로 오류를 만나도 다음 문장을 계속 실행하므로 다섯 개의 오류가 모두 출력된다.

$ sqlite3 bookstore.db < chapter03_constraints.sql
Error: UNIQUE constraint failed: book.id
Error: NOT NULL constraint failed: member.name
Error: CHECK constraint failed: price > 0
Error: UNIQUE constraint failed: member.email
Error: FOREIGN KEY constraint failed

실무에서 자주 틀리는 것

SQLite에서 외래키가 조용히 무시된다

FOREIGN KEY를 선언해 놓고 PRAGMA foreign_keys = ON을 빼먹으면, 잘못된 publisher_id도 그냥 저장된다.

-- 틀린 코드: 연결할 때마다 이 설정을 켜지 않았다
-- (PRAGMA foreign_keys = ON; 없이 바로 INSERT)
INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages)
VALUES (4, '979-11-0000-005-9', '조용히 저장된 책', 99, 1, 20000, '2025-05-01', 210);
-- 오류 없이 성공해 버린다

-- 고친 코드: 새 연결마다 맨 앞에서 켠다
PRAGMA foreign_keys = ON;

INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages)
VALUES (4, '979-11-0000-005-9', '조용히 저장된 책', 99, 1, 20000, '2025-05-01', 210);
-- Error: FOREIGN KEY constraint failed

NOT NULL을 깜빡하고 선택 열로 남겨 둔다

title처럼 화면에 반드시 나와야 하는 값에 NOT NULL을 걸지 않으면, 제목 없는 책이 저장되어 목록 화면이 깨진다.

-- 틀린 코드
CREATE TABLE book (
    id    INTEGER PRIMARY KEY,
    isbn  TEXT NOT NULL UNIQUE,
    title TEXT
);

-- 고친 코드
CREATE TABLE book (
    id    INTEGER PRIMARY KEY,
    isbn  TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL
);

여러 열로 이루어진 후보키를 기본키로 지정하지 않는다

order_item은 한 주문(order_id) 안에서 line_no가 붙는 구조다. 기본키를 지정하지 않으면 같은 주문에 같은 줄 번호가 중복으로 들어갈 수 있다.

-- 틀린 코드: 기본키가 없다
CREATE TABLE order_item (
    order_id   INTEGER NOT NULL,
    line_no    INTEGER NOT NULL,
    book_id    INTEGER NOT NULL REFERENCES book(id),
    qty        INTEGER NOT NULL,
    unit_price INTEGER NOT NULL
);

-- 고친 코드: (order_id, line_no)를 복합 기본키로 지정
CREATE TABLE order_item (
    order_id   INTEGER NOT NULL,
    line_no    INTEGER NOT NULL,
    book_id    INTEGER NOT NULL REFERENCES book(id),
    qty        INTEGER NOT NULL,
    unit_price INTEGER NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

CHECK를 애플리케이션 코드에서만 검사한다

가격이 0보다 커야 한다는 규칙을 화면 입력 검증에서만 처리하면, 관리자 콘솔이나 배치 스크립트가 테이블에 직접 INSERT할 때는 그 검증을 거치지 않는다.

-- 틀린 코드: DB에는 규칙이 없고 애플리케이션 코드만 믿는다
CREATE TABLE book (
    id    INTEGER PRIMARY KEY,
    price INTEGER NOT NULL
);

-- 고친 코드: DB 자체에도 규칙을 건다
CREATE TABLE book (
    id    INTEGER PRIMARY KEY,
    price INTEGER NOT NULL CHECK (price > 0)
);

한눈에 보기

무결성 종류와 SQL 제약의 대응

무결성 종류	지키는 규칙	SQL 키워드	SQLite 오류 메시지 예
개체 무결성	기본키는 NULL이거나 중복될 수 없다	PRIMARY KEY	UNIQUE constraint failed: book.id
참조 무결성	외래키 값은 참조 대상에 실제로 있어야 한다	FOREIGN KEY … REFERENCES	FOREIGN KEY constraint failed
도메인 무결성(필수값)	열에 값이 반드시 있어야 한다	NOT NULL	NOT NULL constraint failed: member.name
도메인 무결성(범위·집합)	정해진 범위나 목록 안의 값만 허용한다	CHECK	CHECK constraint failed: price > 0

SQL 연구소에서 실습하기

다음 과제를 SQL 연구소의 서점 데이터베이스에서 직접 실행해 본다.

• member 테이블에 grade 열의 CHECK 제약을 위반하는 값(예: 'PLATINUM')으로 INSERT 문을 작성해 실행하고, 어떤 오류 메시지가 나오는지 확인한다.
• inventory 테이블에 존재하지 않는 book_id로 재고를 등록하는 INSERT 문을 작성해, 참조 무결성이 어떤 조건에서 삽입을 막는지 확인한다.
• book_author(book_id, author_id, role) 테이블에 알맞은 기본키를 정해 CREATE TABLE 문을 작성하고, 같은 책에 같은 저자를 AUTHOR 역할로 두 번 등록해 어떤 제약에 걸리는지 확인한다.

연습 문제

1. category 테이블에서 최상위 대분류(예: '컴퓨터/IT')를 등록할 때 parent_id 열에 어떤 값을 넣어야 하는지 쓰고, 참조 무결성 관점에서 그 이유를 설명하라.
2. order_item(order_id, line_no, book_id, qty, unit_price) 테이블의 기본키를 무엇으로 정해야 하는지 쓰고, 개체 무결성과 연결해 그 이유를 설명하라.
3. coupon.discount_rate 열에 0 이상 1 이하의 값만 허용하도록 CHECK 제약을 선언하는 SQL 문을 작성하라.
4. rating 열에 1 이상 5 이하만 허용하는 CHECK 제약이 걸려 있는 review 테이블에 rating을 7로 넣는 INSERT 문이 실패한다. 이때 위반한 무결성의 종류를 쓰고 이유를 설명하라.

정답과 해설

1. parent_id에 NULL을 넣는다. 외래키는 값이 있을 때만 참조 대상이 존재해야 한다는 규칙이므로, NULL은 "아무것도 참조하지 않음"을 뜻해 참조 무결성 위반이 아니다. 최상위 분류는 부모가 없는 것이 자연스러우므로 NULL이 맞는 표현이다.
2. PRIMARY KEY (order_id, line_no)로 정해야 한다. 한 주문 안에서 line_no는 그 주문에 한정된 일련번호일 뿐 전체 테이블에서 유일하지 않으므로, order_id와 함께 묶어야 비로소 최소성과 유일성을 만족하는 키가 된다. 이 복합키가 기본키로 지정되어야 개체 무결성이 지켜진다.

3. discount_rate REAL NOT NULL CHECK (discount_rate >= 0 AND discount_rate <= 1)

   discount_rate는 값이 반드시 있어야 하므로 NOT NULL을 걸고, 0 이상 1 이하라는 범위는 CHECK로 표현한다.
4. 도메인 무결성(범위 제약) 위반이다. rating 열에는 1 이상 5 이하의 값만 허용하는 CHECK 제약이 걸려 있는데 7은 이 범위를 벗어나므로, 테이블의 CHECK 조건에 걸려 INSERT 자체가 거부된다.

SQL 연구소 실습 과제의 해설은 다음과 같다. grade에 'PLATINUM'을 넣으면 CHECK (grade IN ('BASIC','SILVER','GOLD','VIP'))에 걸려 "CHECK constraint failed: grade IN ('BASIC','SILVER','GOLD','VIP')" 형태의 오류가 난다. 존재하지 않는 book_id로 inventory를 등록하면 "FOREIGN KEY constraint failed" 오류가 나며, 이는 PRAGMA foreign_keys = ON이 켜져 있을 때만 나타난다. book_author는 한 책에 한 저자가 같은 역할로 두 번 등록될 이유가 없으므로 PRIMARY KEY (book_id, author_id, role)로 지정하면, 중복 등록 시 개체 무결성 위반으로 거부된다.