기본 키와 외래 키

관계형 모델에서 테이블은 같은 종류의 행을 모은 집합이고, 열은 그 열에 들어갈 수 있는 값의 범위와 뜻, 곧 도메인(domain)을 정합니다. 작성자를 나타내는 열이라면 정수라는 것만으로는 부족하고, 그 정수가 어떤 회원을 가리키는지까지 정해야 합니다. 이 글에서는 행 하나를 구별하는 키와 테이블 사이의 관계를 먼저 정하고, 회원 한 명이 여러 게시글을 쓰는 관계를 posts 테이블로 만듭니다.

회원 정보를 게시글마다 복사하면

회원의 이메일과 이름을 게시글마다 함께 저장하는 넓은 테이블 하나로 시작했다고 해 봅시다. 행 하나만 보면 필요한 것이 다 들어 있어 편해 보입니다.

CREATE TEMPORARY TABLE flat_board_post (
  post_id BIGINT,
  member_email VARCHAR(255),
  member_name VARCHAR(60),
  post_title VARCHAR(80),
  view_count BIGINT
);

INSERT INTO flat_board_post VALUES
(1, '[email protected]', '안동민', 'SELECT 기초', 30),
(2, '[email protected]', '동민',   'WHERE 복습', 40);

SELECT member_email, COUNT(DISTINCT member_name) AS name_versions
FROM flat_board_post
GROUP BY member_email;

member_email    | name_versions
[email protected] | 2

같은 이메일의 이름이 두 가지로 저장되어 있어, 어느 게시글의 이름을 지금의 이름으로 봐야 할지 정할 수 없습니다. 회원이 이름을 바꿀 때마다 과거의 모든 게시글 행을 고쳐야 하고, 일부만 고치면 이런 불일치가 생깁니다. 존재하지 않는 이메일이 게시글에 들어가는 것도 막지 못합니다. 이렇게 한 행에 서로 다른 이유로 바뀌는 값을 섞어 생기는 문제를 갱신 이상(update anomaly)이라고 합니다.

게시글이 소유하는 사실은 제목과 본문, 상태와 시각이고, 회원의 이름과 이메일은 회원 테이블이 소유합니다. 게시글이 회원을 가리키는 키만 갖게 하면, 이름은 한곳에서만 바꾸면 되고 존재하지 않는 회원을 가리키는 것은 데이터베이스가 거부할 수 있습니다.

기본 키와 외래 키의 역할

기본 키는 테이블 안에서 행 하나하나를 유일하게 식별하며 NULL을 허용하지 않습니다. 외래 키는 자식 테이블의 값이 부모 테이블의 기본 키나 UNIQUE 키의 값 가운데 하나와 같도록 강제합니다. members 1 : N posts에서 같은 author_id가 여러 게시글에 되풀이되는 것은 관계를 나타내는 것이지, 회원의 정보가 중복된 것이 아닙니다.

여기서 N은 게시글이 반드시 여러 개 있다는 뜻이 아니라 여러 개를 허용한다는 뜻입니다. 회원 한 명에게 게시글은 0개 이상이고, 게시글 하나의 작성자는 정확히 한 명입니다. author_id의 NOT NULL은 작성자가 빠지는 것을, 외래 키는 존재하지 않는 작성자를 막지만, 둘 다 회원에게 게시글을 쓰라고 강제하지는 않습니다.

외래 키와 그것이 가리키는 열은 타입이 서로 맞아야 합니다. MySQL 8.4의 외래 키 설명에 따르면 정수나 DECIMAL처럼 정밀도가 정해진 타입은 크기와 부호가 같아야 하고, 문자열은 길이가 달라도 되지만 문자 집합과 정렬 규칙이 같아야 합니다. 그래서 members.id가 BIGINT UNSIGNED라면 posts.author_id도 BIGINT UNSIGNED로 만듭니다. InnoDB는 외래 키를 검사할 때 자식 쪽 열에 인덱스가 있어야 하며, 없으면 자동으로 만듭니다.

부모 행을 지우거나 키를 바꿀 때 자식을 어떻게 할지도 정합니다. RESTRICT는 자식이 있으면 거부하고, CASCADE는 자식도 함께 지우거나 바꾸며, SET NULL은 자식의 값을 NULL로 바꿉니다. 회원 게시판은 게시글을 남기는 것이 기본이므로, 회원을 지울 때 게시글이 함께 사라지지 않도록 RESTRICT를 명시합니다.

더 깊이: MySQL의 외래 키가 표준 SQL과 다른 점

InnoDB는 예전부터 부모 쪽의 유일하지 않은 인덱스나, 인덱스 앞부분의 일부 열만 가리키는 외래 키도 허용했습니다. 표준 SQL에는 없는 확장인데, MySQL 8.4.0에서 사용 중단(deprecated)되었습니다. 이 버전에 새로 생긴 시스템 변수 restrict_fk_on_non_standard_key의 기본값이 ON이어서, 그런 외래 키를 만드는 CREATE TABLE과 ALTER TABLE은 ER_FK_NO_INDEX_PARENT 오류로 거부됩니다. 8.0에서 업그레이드한 데이터베이스에 이런 외래 키가 남아 있으면 업그레이드는 진행되지만 서버가 해당 외래 키의 이름을 경고로 알려 주므로, 부모 쪽에 기본 키나 UNIQUE 키를 두도록 옮겨 두는 것이 좋습니다.

참조 동작의 이름도 저장 엔진에 따라 다르게 읽어야 합니다. NDB 저장 엔진에서 NO ACTION은 검사를 커밋할 때까지 미루라는 뜻이지만, InnoDB에서 NO ACTION은 RESTRICT와 똑같이 자식이 있는 부모의 삭제나 변경을 즉시 거부합니다. 또 RESTRICT나 NO ACTION을 적는 것은 ON DELETE나 ON UPDATE를 생략한 것과 같습니다. 그러니 이 글이 RESTRICT를 명시하는 것은 동작을 바꾸려는 것이 아니라, 삭제 규칙을 정했다는 사실을 DDL에 드러내려는 것입니다.

외래 키 정의에 MATCH FULL 같은 MATCH 절을 적는 것도 피해야 합니다. MySQL 8.4 매뉴얼에 따르면 어떤 저장 엔진도 MATCH 절을 지키지 않으며, MATCH 절을 쓰면 같은 정의의 ON DELETE와 ON UPDATE가 무시됩니다. MySQL은 사실상 MATCH SIMPLE로 동작해서, 여러 열로 이루어진 외래 키의 일부 열이 NULL이면 일치하는 부모 행이 없어도 자식 행이 들어갑니다.

게시글 테이블을 만든다

게시글의 식별자, 작성자를 가리키는 외래 키, 제목과 본문, 상태, 조회수, 시각을 별도 테이블에 둡니다. 동시에 같은 게시글을 고칠 때의 충돌을 알아채는 데 쓸 version도 0에서 시작합니다.

CREATE TABLE posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(80) NOT NULL,
  content VARCHAR(5000) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
  view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  published_at DATETIME(6) NULL,
  version BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_posts_author_created (author_id, created_at DESC, id DESC),
  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id) REFERENCES members (id)
    ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE = InnoDB;

INSERT INTO posts (
  author_id, title, content, status, view_count,
  created_at, updated_at, published_at
) VALUES (
  1, 'SELECT 기초', 'SELECT가 필요한 열을 고르는 이유를 정리합니다.',
  'PUBLISHED', 30,
  '2026-07-01 10:00:00', '2026-07-01 10:30:00', '2026-07-01 10:30:00'
);

SELECT id, author_id, title, view_count, status FROM posts;

id | author_id | title       | view_count | status
1  | 1         | SELECT 기초 | 30         | PUBLISHED

게시글은 author_id 1로 회원과 이어지고, 작성자의 이름은 회원 테이블에서 한 번만 읽습니다. 이름과 이메일이 필요하면 5장의 JOIN으로 읽으며, 편하다는 이유로 게시글에 복사하지 않습니다. 복합 인덱스 ix_posts_author_created는 외래 키 검사에 쓰일 뿐 아니라 회원별 최신 게시글을 찾는 조회도 돕는데, 그 순서를 정하는 방법은 7장에서 측정합니다.

지금의 테이블은 타입과 관계의 뼈대만 갖추었습니다. 상태 문자열에 아무 값이나 들어갈 수 있고, 게시된 글인데 게시 시각이 없는 것도 막지 못합니다. 이런 규칙은 다음 글에서 CHECK 제약으로 하나씩 더합니다. 모든 규칙을 첫 DDL에 한꺼번에 넣기보다, 제약마다 막는 오류를 직접 확인하며 쌓아 가는 것이 이 교재의 방식입니다.

만든 객체를 확인한다

SQL 파일을 실행한 뒤에는 "성공했다"는 메시지만 믿지 말고 서버에 무엇이 만들어졌는지 확인합니다. SHOW DATABASES는 이 계정이 볼 수 있는 데이터베이스를, SHOW TABLES는 지금 데이터베이스의 테이블을 보여 줍니다. DESCRIBE는 열의 이름과 타입, NULL 허용, 키, 기본값을 빠르게 보여 주고, SHOW CREATE TABLE은 엔진과 인덱스, 제약까지 포함한 실제 DDL을 돌려줍니다.

SELECT DATABASE() AS selected_database;
SHOW DATABASES;

USE board_lab;
SHOW TABLES;
DESCRIBE members;
DESCRIBE posts;
SHOW CREATE TABLE posts;

이 결과에서 지금 데이터베이스가 board_lab이고, 테이블이 members와 posts 둘이며, posts의 기본 키는 id, author_id는 members.id를 가리키는 외래 키, 엔진은 InnoDB인지를 확인합니다. DESCRIBE는 구조를 빨리 읽기에는 좋지만 제약식 전체를 보여 주지 않으므로, 외래 키의 규칙이나 CHECK의 식을 확인할 때는 SHOW CREATE TABLE이나 INFORMATION_SCHEMA를 씁니다. 같은 이름의 테이블이 다른 데이터베이스에도 있을 수 있으므로, 구조를 바꾸는 SQL을 실행하기 전에는 SELECT DATABASE()로 대상을 확인하는 습관을 들입니다.

객체를 지우는 명령은 범위가 다릅니다. DROP TABLE은 테이블 하나의 구조와 데이터를 함께 지우고, DROP DATABASE는 그 안의 모든 객체를 지웁니다. 다음처럼 연습용 데이터베이스를 따로 만들어 확인하고, 계속 쌓아 갈 board_lab에는 쓰지 않습니다.

CREATE DATABASE db_object_sandbox
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE db_object_sandbox.probe (
  probe_id BIGINT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE = InnoDB;

SHOW TABLES FROM db_object_sandbox;
DROP TABLE db_object_sandbox.probe;
DROP DATABASE db_object_sandbox;

테이블 이름 앞에 db_object_sandbox.를 붙였으므로, USE로 지금 데이터베이스를 바꾸지 않고도 연습용 데이터베이스 안에서만 테이블을 만들고 지웁니다. SHOW TABLES FROM은 테이블을 지우기 전이므로 probe 하나를 보여 주고, 마지막 DROP DATABASE가 끝나면 SHOW DATABASES의 목록에서도 db_object_sandbox가 사라집니다.

SQL을 목적에 따라 나누기

SQL 문장은 무엇을 바꾸느냐에 따라 네 범주로 나눕니다. 명령의 이름을 외우기보다 지금 문장이 구조, 행, 권한, 트랜잭션 가운데 무엇을 바꾸는지를 먼저 묻는 것이 좋습니다.

교재에 따라 SELECT를 DML에서 떼어 DQL이라고 부르기도 합니다. 이름이 어떻든, 조회는 행을 영구히 바꾸지 않고, MySQL의 DDL은 실행 전후에 암묵적으로 커밋되어 일반 DML처럼 ROLLBACK으로 되돌릴 수 없다는 차이는 같습니다. DCL은 8장 6편에서 애플리케이션 계정을 따로 만들며, TCL의 실제 효과는 8장 2편에서 자동 커밋과 한 트랜잭션의 결과를 비교하며 확인합니다.

관계의 메타데이터를 확인한다

외래 키가 어느 테이블을 가리키고 삭제 규칙이 무엇인지는 메타데이터에서 확인하고, 관계의 모양은 자식 행을 세어 확인합니다. 첫 조회의 information_schema.referential_constraints는 외래 키마다 한 행씩 제약 이름, 자식과 부모 테이블, 갱신과 삭제 규칙을 담은 메타데이터 테이블입니다. 둘째 조회는 author_id별로 게시글을 세므로, 일대다 관계라면 한 회원 아래에 여러 게시글이 묶여 세어집니다.

SELECT constraint_name, table_name, referenced_table_name, update_rule, delete_rule
FROM information_schema.referential_constraints
WHERE constraint_schema = 'board_lab' AND table_name = 'posts';

SELECT author_id, COUNT(*) AS post_count
FROM posts
GROUP BY author_id
ORDER BY author_id;

첫 조회에서는 fk_posts_author가 members를 가리키고 update_rule과 delete_rule이 모두 RESTRICT로 나와야 합니다. 둘째 조회에서는 지금 author_id 1의 게시글 1건이 보입니다. 같은 회원에 게시글을 더 넣으면 post_count만 늘고 회원 행은 복제되지 않습니다.

키와 삭제 규칙을 정하는 기준

숫자로 된 대리 키는 업무상 뜻이 없어도 바뀌지 않는 참조점이 되지만, 업무상의 중복까지 막아 주지는 않으므로 회원에는 이메일의 UNIQUE가 따로 필요합니다. 반대로 이메일 같은 자연 키를 기본 키로 쓰면, 그 값이 바뀌거나 길 때 모든 자식 테이블이 그 비용을 함께 집니다. 어느 쪽이든 어떤 열이 행을 식별하는지 기록해 둡니다. 이 선택은 10장 2편에서 자세히 비교합니다.

ON DELETE CASCADE는 편리하지만, 부모 하나를 지울 때 자식이 대량으로 함께 사라지고 감사 기록까지 잃을 수 있으므로 수명 주기를 분명히 정하지 않았다면 고르지 않습니다. 회원의 개인정보를 지워야 하는데 게시글은 남겨야 한다면, 16장 2편처럼 회원을 익명화하는 설계가 필요합니다. 외래 키에 NULL을 허용하면 "관계 없음"을 허용하는 것이고, 그런 행은 INNER JOIN에서 빠지므로, 작성자가 반드시 있어야 하는 게시글은 NOT NULL로 둡니다.

외래 키는 무결성을 지키는 대신 부모를 지울 때나 대량으로 가져올 때, 구조를 바꿀 때 잠금과 순서를 따지게 만듭니다. 외래 키는 한 서버 안에서만 검사되므로, 여러 서버나 샤드에 나뉜 데이터 사이의 관계는 따로 일관성을 지킬 방법을 마련해야 합니다. 이름이 바뀐다고 기본 키를 바꾸지는 않으므로 ON UPDATE도 RESTRICT로 둡니다.

같은 값이 되풀이된다는 이유만으로 테이블을 나누지는 않습니다. 정체성과 바뀌는 이유가 서로 다르고, 그 관계를 키로 설명할 수 있을 때 나눕니다.

정리

• 행 하나가 무엇을 뜻하는지와 각 속성을 어느 테이블이 소유하는지를 먼저 정합니다.
• 기본 키는 행을 식별하고, 외래 키는 부모의 존재와 관계를 강제하며, 둘의 타입은 크기와 부호까지 맞아야 합니다.
• 일대다 관계에서 자식의 외래 키 값이 되풀이되는 것은 관계의 표현이고, 부모 속성의 중복과 다릅니다.
• 객체는 SHOW, DESCRIBE, SHOW CREATE TABLE, INFORMATION_SCHEMA로 확인하고, SQL은 DDL, DML, DCL, TCL로 나누어 읽습니다.
• 삭제 규칙과 키의 타입은 데이터의 수명 주기에 맞춰 명시합니다.

연습 문제

1. 게시글을 더하고 없는 작성자를 넣어 보기

author_id 1로 초안 하나와 게시된 글 하나를 더한 뒤 회원별 게시글 수를 확인하세요. 이어서 존재하지 않는 author_id 999로 게시글을 넣어 보세요. 각 문장은 어떻게 되나요?

INSERT INTO posts (
  author_id, title, content, status, view_count,
  created_at, updated_at, published_at
) VALUES
(1, 'JOIN 예습', 'JOIN 내용을 게시 전에 정리합니다.', 'DRAFT', 0,
 '2026-07-02 09:00:00', '2026-07-02 09:00:00', NULL),
(1, 'WHERE 복습', 'WHERE 조건의 경계를 공개 글로 정리합니다.', 'PUBLISHED', 40,
 '2026-07-02 10:00:00', '2026-07-02 10:40:00', '2026-07-02 10:40:00');

SELECT author_id, COUNT(*) AS post_count
FROM posts
GROUP BY author_id;

INSERT INTO posts (author_id, title, content, status, created_at, updated_at)
VALUES (999, '고아 게시글', '존재하지 않는 작성자를 참조합니다.', 'DRAFT',
        '2026-07-02 12:00:00', '2026-07-02 12:00:00');

2. 관계만 보고 판단하기

회원 1과 2가 있고, 게시글의 author_id는 1과 1입니다. 회원 2가 아직 글을 쓰지 않은 것은 규칙 위반인가요? 새 게시글의 작성자를 NULL로 적는 것과 999로 적는 것은 각각 어느 규칙에 걸리나요?

3. 타입이 다른 외래 키

누군가 comments 테이블을 만들며 post_id를 부호 있는 BIGINT로 두고 posts.id(BIGINT UNSIGNED)를 가리키는 외래 키를 걸려 했습니다. 어떻게 되나요? 값의 범위가 겹치는 부분만 쓰면 괜찮지 않을까요?

다음 글 데이터 타입과 CHECK 제약에서는 열의 타입과 범위, CHECK 제약으로 게시글의 상태와 게시 시각이 서로 맞도록 지키는 방법을 다룹니다.