데이터베이스 기초 (2) - Constraints and Schema Design

데이터베이스 기초 시리즈의 2편입니다. 전체 목차는 0편에 있습니다.

1편에서 users table을 만들 때 PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT는 이름만 알아두면 된다고 하고 넘어갔습니다. 이번 편에서 이 제약들을 설명하고, posts와 comments table을 추가해 시리즈 내내 쓸 게시판 schema를 완성합니다. 실습 환경은 PostgreSQL 편에서 만든 PostgreSQL 18 container와 psql을 그대로 사용합니다.

한 table에 다 넣으면 생기는 일

게시판의 가장 단순한 설계는 글과 글쓴이 정보를 한 table에 전부 넣는 것입니다. 제약을 배우기 전에 이 설계가 왜 문제인지 먼저 확인합니다. 반례이므로 본문 column은 생략하고 제목, 글쓴이 이름, 글쓴이 가입 시각만 둡니다.

CREATE TABLE posts_flat (
    title            TEXT,
    author_name      TEXT,
    author_joined_at TIMESTAMPTZ
);
INSERT INTO posts_flat VALUES
    ('첫 글입니다', 'alice', '2026-07-01 10:00:00+09'),
    ('PostgreSQL 질문 있습니다', 'alice', '2026-07-01 10:00:00+09');

alice가 글을 두 개 썼으므로 이름과 가입 시각이 두 row에 똑같이 들어갑니다. 이 상태에서 alice가 이름을 alice2로 바꾼다고 할 때, 한 row만 고치면 어떻게 되는지 봅니다.

UPDATE posts_flat SET author_name = 'alice2' WHERE title = '첫 글입니다';
SELECT author_name, title FROM posts_flat ORDER BY author_name;

 author_name |          title
-------------+--------------------------
 alice       | PostgreSQL 질문 있습니다
 alice2      | 첫 글입니다

같은 사람이 어떤 글에서는 alice이고 어떤 글에서는 alice2입니다. 이제 이 데이터만 봐서는 두 이름이 같은 사람인지 알 방법이 없습니다. 이렇게 여러 row에 중복된 값의 일부만 고쳐서 데이터가 어긋나는 것을 갱신 이상이라고 부릅니다. author_joined_at도 마찬가지로, 가입 시각이 잘못 입력되어 있었다면 그 사람이 쓴 글 수만큼 고쳐야 합니다. 반례는 여기까지 보고 DROP TABLE posts_flat;로 지웁니다.

정규화

정규화는 이런 중복이 생기지 않도록 table을 나누는 원칙입니다. 교과서에는 제1정규형(1NF)부터 여러 단계가 수식으로 정의되어 있지만, 실무에서 필요한 내용은 사례 세 가지로 정리할 수 있습니다.

1NF는 한 칸에 값을 하나만 넣으라는 규칙입니다. 반례는 글에 태그를 붙일 때 tags라는 column 하나에 ‘잡담,질문’처럼 쉼표로 이어 넣는 설계입니다. 이렇게 하면 특정 태그가 붙은 글을 찾을 때 문자열을 쪼개서 비교해야 하고, 태그 이름을 바꾸려면 문자열 치환을 해야 하고, 존재하지 않는 태그가 들어와도 막을 수 없습니다. 값이 여러 개 붙는 관계는 column이 아니라 별도 table의 row로 저장해야 하며, 태그 table은 3편에서 만듭니다.

2NF와 3NF는 각 값이 자기가 설명하는 대상에만 붙어 있어야 한다는 규칙입니다. 글 번호와 태그 번호 쌍을 row로 저장하는 연결 table을 가정합니다. 이 연결을 (post_id, tag_id, tag_name) 세 column으로 저장하면 tag_name은 연결이 아니라 tag_id에만 붙는 값이라서 같은 태그 이름이 연결 수만큼 반복됩니다. 이렇게 값이 key 전체(post_id, tag_id)가 아니라 그 일부인 tag_id에만 종속되는 것을 부분 종속이라고 부르고, 태그 이름을 별도 table로 분리하면 해결됩니다(2NF 위반 사례). 위 posts_flat에서 author_joined_at은 글이 아니라 글쓴이에 붙는 값이고 글은 글쓴이를 거쳐서야 가입 시각과 연결되는데, 이것을 이행 종속이라고 부르고 글쓴이 정보를 users로 분리하면 해결됩니다(3NF 위반 사례). 두 경우 모두 결론은 같습니다. 한 가지 사실은 한 곳에만 저장하고, 다른 table에는 그것을 가리키는 값 하나만 둡니다.

다만 정규화를 항상 끝까지 적용하지는 않습니다. 나눠 놓은 table을 조회할 때 다시 붙이는 데는 비용이 들기 때문에, 조회가 잦고 변경이 드문 값은 의도적으로 중복해 두기도 합니다. 글 목록을 보여줄 때마다 댓글을 세지 않도록 posts에 댓글 수 column을 두는 설계가 그 예입니다. table을 다시 붙이는 JOIN과 그 비용은 3편과 5편에서 직접 체감합니다.

게시판 요구사항에서 schema로

이 시리즈의 게시판 요구사항은 두 문장입니다. 회원이 글을 쓰고, 회원이 글에 댓글을 답니다. 이 정도 규모는 ERD 같은 그림 도구 없이 문장에서 바로 table을 도출할 수 있습니다. 문장의 명사가 table 후보가 되므로 회원은 users, 글은 posts, 댓글은 comments가 됩니다.

다음은 관계입니다. 글 하나에는 작성자가 한 명이고 한 회원은 글을 여러 개 쓸 수 있으므로, 글과 회원은 다대일 관계입니다. 다대일 관계에서는 여러 쪽 table이 하나 쪽 row를 가리키는 column을 갖습니다. 그래서 posts에 user_id column을 두고, users에는 글 관련 column을 두지 않습니다. 글 하나에 댓글이 여러 개 달리고 댓글에도 작성자가 한 명 있으므로, comments는 post_id와 user_id 두 column으로 글과 회원을 각각 가리킵니다. 정리하면 users(id, username, joined_at), posts(id, user_id, title, body, created_at), comments(id, post_id, user_id, body, created_at)입니다.

제약

제약(constraint)은 잘못된 데이터가 들어오는 것을 database가 직접 막게 하는 선언입니다. 위 schema에 쓰이는 제약을 하나씩 확인합니다. 아래 예제는 1편에서 만든 users table을 사용합니다. SELECT id, username FROM users ORDER BY id;를 실행했을 때 id 1이 alice, 2가 bob, 3이 carol이어야 하며, 실습을 이어오지 않았다면 1편의 users 생성과 INSERT를 먼저 실행합니다. 상태가 다르면 DROP TABLE users;로 지운 뒤 1편의 생성과 INSERT를 다시 실행합니다.

PRIMARY KEY는 row를 유일하게 식별하는 column을 지정하는 제약으로, 그 column에는 중복과 NULL이 모두 금지되며 table마다 하나를 둡니다. users의 id BIGSERIAL PRIMARY KEY가 그 예입니다. BIGSERIAL은 1씩 증가하는 정수를 자동으로 채우는 PostgreSQL의 관행 표기입니다. 표준 SQL 표기는 BIGINT GENERATED ALWAYS AS IDENTITY이고 새 스키마에는 이쪽이 권장되지만, 기존 코드에서 널리 쓰이는 쪽은 BIGSERIAL이라서 이 시리즈도 BIGSERIAL을 씁니다.

FOREIGN KEY는 다른 table의 row를 가리키는 column에 REFERENCES로 선언하며, 가리키는 row가 실제로 존재하는지를 database가 검사합니다. 존재하지 않는 회원 번호를 넣으면 어떻게 되는지 임시 table로 확인합니다.

CREATE TABLE fk_demo (user_id BIGINT REFERENCES users(id));
INSERT INTO fk_demo (user_id) VALUES (99);
-- ERROR:  insert or update on table "fk_demo" violates foreign key constraint "fk_demo_user_id_fkey"
-- DETAIL:  Key (user_id)=(99) is not present in table "users".

id가 99인 회원이 없으므로 INSERT가 거부됩니다. 삭제 쪽 동작도 정할 수 있습니다. ON DELETE를 붙이지 않은 기본 동작은 참조하는 row가 남아 있는 한 원본 row의 삭제를 오류로 막는 것이고, ON DELETE CASCADE를 붙이면 참조하는 row를 함께 지웁니다. 게시판에서는 글을 지우면 그 글의 댓글이 남을 이유가 없으므로 comments의 post_id에 CASCADE를 붙이고, posts의 user_id는 기본 동작으로 두어 글이 있는 회원의 삭제를 막습니다.

UNIQUE는 column 값의 중복을 막습니다. users의 username에 이미 선언되어 있으므로 alice를 한 번 더 넣으면 오류가 납니다.

INSERT INTO users (username) VALUES ('alice');
-- ERROR:  duplicate key value violates unique constraint "users_username_key"

NOT NULL은 그 column에 NULL을 허용하지 않는 제약입니다. 1편에서 본 대로 NULL은 값이 없다는 표시이므로, 제목 없는 글이나 이름 없는 회원처럼 성립하지 않는 데이터를 막을 때 붙입니다.

CHECK는 임의의 조건식을 검사합니다. 게시판에 중고 거래 기능을 붙인다면 가격이 양수여야 한다는 조건을 이렇게 선언할 수 있습니다.

CREATE TABLE market_items (price INT CHECK (price > 0));
INSERT INTO market_items (price) VALUES (-50000);
-- ERROR:  new row for relation "market_items" violates check constraint "market_items_price_check"

DEFAULT는 INSERT에서 값을 생략했을 때 채울 값을 정합니다. users의 joined_at TIMESTAMPTZ NOT NULL DEFAULT now()가 그 예로, 1편에서 username만 넣어도 가입 시각이 채워진 이유가 이것입니다. now()처럼 함수 호출도 DEFAULT로 쓸 수 있습니다. 실습용 table 두 개는 DROP TABLE fk_demo, market_items;로 지웁니다.

schema 완성

users는 1편에서 만든 것을 그대로 씁니다. 이제 앞에서 도출한 설계에 제약을 붙여 posts와 comments를 만듭니다. 이 정의가 시리즈 전체에서 쓰는 표준 schema입니다.

CREATE TABLE posts (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    title      TEXT NOT NULL,
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE comments (
    id         BIGSERIAL PRIMARY KEY,
    post_id    BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

seed data도 시리즈 전체에서 공유하므로 아래 내용 그대로 입력하고, row 수를 세는 SELECT count(*)로 결과를 확인합니다. 글 4건과 댓글 6건이고, 4번 글에는 댓글이 없습니다.

INSERT INTO posts (user_id, title, body) VALUES
    (1, '첫 글입니다', '가입 인사드립니다'),
    (1, 'PostgreSQL 질문 있습니다', 'index가 뭔가요'),
    (2, '중고 키보드 팝니다', '상태 좋습니다'),
    (3, '이번 주 모임 공지', '토요일 2시입니다');
INSERT INTO comments (post_id, user_id, body) VALUES
    (1, 2, '환영합니다'),
    (1, 3, '반갑습니다'),
    (2, 2, '5편을 기다리세요'),
    (2, 3, '저도 궁금합니다'),
    (2, 1, '감사합니다'),
    (3, 1, '쿨거 가능한가요');
SELECT count(*) FROM posts;
SELECT count(*) FROM comments;

첫 번째 count가 4, 두 번째가 6이면 seed data 입력이 끝난 것입니다.

MySQL 차이

MySQL 8.4에는 BIGSERIAL과 IDENTITY 표기가 없고, id BIGINT AUTO_INCREMENT PRIMARY KEY처럼 AUTO_INCREMENT를 씁니다. TIMESTAMPTZ와 DEFAULT now()도 그대로 쓸 수 없으므로 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP처럼 바꿔 씁니다. CHECK는 오랫동안 문법만 받아들이고 실제로는 검사하지 않았는데, 8.0.16부터 강제되기 시작해서 8.4에서는 PostgreSQL과 같이 위반 시 오류가 납니다. FOREIGN KEY는 기본 storage engine인 InnoDB(MySQL 편 참고)에서 강제되지만 표기에 주의할 점이 있습니다. 이 편의 예제처럼 column 정의 옆에 붙이는 inline REFERENCES 표기를 MySQL은 문법만 받아들이고 무시하므로, 제약이 만들어지지 않은 채 아무 검사도 하지 않습니다. FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE처럼 table 수준 표기로 선언해야 하며, 그렇게 선언하면 REFERENCES 검사, 기본 삭제 차단, ON DELETE CASCADE 모두 이 편의 예제와 동일하게 동작합니다.

다음 편

다음 편인 3편에서는 이렇게 나눠 놓은 users, posts, comments를 JOIN으로 다시 붙여서 글 목록에 글쓴이 이름과 댓글을 함께 조회합니다.