외래 키와 참조 무결성

가리키는 관계를 데이터베이스가 지킵니다

2부에서 Board.COMMENTS 의 post_num 이 Board.POSTS 의 num 을 가리킨다고 보고 조인했습니다. 그 관계가 실제로 지켜지는지는 지금까지 확인하지 않았습니다.

외래 키는 그 관계를 데이터베이스가 강제하게 만드는 제약입니다. 없는 글에 댓글을 달 수 없고, 댓글이 달린 글을 함부로 지울 수 없습니다. 응용 프로그램이 아무리 잘못 짜여도 데이터베이스가 마지막에서 막습니다.

실습 데이터베이스에는 이미 여덟 개가 걸려 있습니다.

SELECT fk.name AS 이름,
       OBJECT_NAME(fk.parent_object_id) AS 자식표, cc.name AS 자식열,
       OBJECT_NAME(fk.referenced_object_id) AS 부모표,
       fk.delete_referential_action_desc AS 삭제동작
FROM sys.foreign_keys fk
    JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
    JOIN sys.columns cc ON cc.object_id = fkc.parent_object_id
                        AND cc.column_id = fkc.parent_column_id
ORDER BY 1;

오류 547 세 가지 모양

외래 키가 막을 때는 언제나 547 입니다. 다만 문장이 조금씩 다르고, 그 차이가 무엇이 막혔는지를 알려 줍니다.

-- 없는 글에 댓글을 답니다.
INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date)
VALUES (99999, 1, N'없는 글입니다', '2026-01-01');

-- 글이 있는 회원을 지웁니다.
DELETE FROM Member.USERS WHERE num = 1;

-- 답글이 달린 원글을 지웁니다(3번 글에 답글이 있습니다).
DELETE FROM Board.POSTS WHERE num = 3;

회원을 지울 수 없다는 것이 불편해 보이지만 그것이 목적입니다. 지워지면 507건의 글이 없는 사람을 가리키게 됩니다. 탈퇴는 행을 지우는 것이 아니라 탈퇴 표시를 남기는 것으로 처리합니다.

막는 대신 따라가게 하기

부모를 지울 때 무엇을 할지는 ON DELETE 로 정합니다. 넷이 있습니다.

SET NULL 은 그 열이 NULL 을 허용해야 하고, SET DEFAULT 는 기본값이 부모에 실제로 있어야 합니다. 없는 값을 기본값으로 두면 지울 때 다시 547 이 납니다.

실습 데이터베이스에서 CASCADE 인 셋은 모두 부모 없이는 뜻이 없는 자식입니다. 글이 사라지면 그 댓글과 첨부는 남을 까닭이 없습니다. 실제로 확인합니다.

BEGIN TRAN;

-- 4번 글에는 댓글이 4건 달려 있습니다.
DELETE FROM Board.POSTS WHERE num = 4;

SELECT (SELECT COUNT(*) FROM Board.POSTS) AS 글,
       (SELECT COUNT(*) FROM Board.COMMENTS) AS 댓글;

ROLLBACK;

이것이 CASCADE 의 위험입니다. 지워지는 범위가 문장에 보이지 않습니다. 지운 사람은 글 하나를 지웠다고 생각하지만 실제로는 댓글 4건이 함께 사라졌고, 그것을 되돌릴 방법은 백업뿐입니다.

CASCADE 를 걸기 전에 물어볼 것은 하나입니다. "부모가 사라지면 이 자식은 존재할 까닭이 없는가." 댓글과 첨부는 그렇습니다. 회원과 글은 아닙니다. 회원이 탈퇴해도 게시판의 글은 남아야 하므로 FK_POSTS_USERS 는 NO ACTION 입니다.

연쇄 경로가 둘이면 거부됩니다

CASCADE 를 원하는 대로 다 걸 수는 없습니다. SQL Server 는 같은 표에 연쇄가 두 갈래로 닿는 것을 아예 만들지 못하게 합니다. 실습 데이터베이스와 같은 모양을 작은 표로 흉내 내 보겠습니다.

CREATE TABLE Board.T_BOARD (num int IDENTITY PRIMARY KEY, name nvarchar(50) NOT NULL);

CREATE TABLE Board.T_CAT (num int IDENTITY PRIMARY KEY, board_num int NOT NULL,
    CONSTRAINT FK_TCAT_TBOARD FOREIGN KEY (board_num)
        REFERENCES Board.T_BOARD(num) ON DELETE CASCADE);

CREATE TABLE Board.T_POST (num int IDENTITY PRIMARY KEY, board_num int NOT NULL, cat_num int NULL,
    CONSTRAINT FK_TPOST_TCAT FOREIGN KEY (cat_num)
        REFERENCES Board.T_CAT(num) ON DELETE CASCADE);

-- 여기까지는 됩니다. 이제 두 번째 경로를 만듭니다.
ALTER TABLE Board.T_POST ADD CONSTRAINT FK_TPOST_TBOARD
    FOREIGN KEY (board_num) REFERENCES Board.T_BOARD(num) ON DELETE CASCADE;

게시판을 지우면 T_POST 에 두 갈래로 닿습니다. 하나는 게시판에서 곧바로, 하나는 말머리를 거쳐서입니다. 어느 쪽이 먼저인지에 따라 결과가 달라질 수 있으므로 엔진이 아예 만들지 못하게 합니다.

자기 참조에는 걸 수 없습니다

답글 구조도 같은 이유로 막힙니다.

CREATE TABLE Board.T_TREE (
    num int IDENTITY PRIMARY KEY,
    parent_num int NULL,
    CONSTRAINT FK_TTREE_PARENT FOREIGN KEY (parent_num)
        REFERENCES Board.T_TREE(num) ON DELETE CASCADE);

그래서 FK_POSTS_PARENT 가 NO ACTION 인 것입니다. 선택이 아니라 유일한 답입니다. 답글이 달린 원글을 지우는 일은 응용 프로그램이 차례를 맞춰 처리해야 합니다. 깊은 것부터 지우거나, 지웠다는 표시만 남기고 자리는 두는 방식입니다. 계층 자료를 다루는 방법은 3.9 에서 봅니다.

인덱스는 따라오지 않습니다

기본 키를 만들면 인덱스가 함께 생깁니다. 외래 키는 그렇지 않습니다. 그런데 부모를 지울 때마다 엔진은 "이 부모를 가리키는 자식이 있는가" 를 확인해야 합니다. 자식 쪽에 인덱스가 없으면 표 전체를 봅니다.

실습 데이터베이스는 한쪽만 인덱스를 두었습니다. IX_COMMENTS_post 와 IX_FILES_post 는 있지만 user_num 에는 없습니다. 회원 하나와 글 하나를 지우면서 SET STATISTICS IO ON 으로 읽은 페이지를 세어 봅니다.

-- 실습 자료를 건드리지 않도록 임시 행을 넣고 그것을 지웁니다.
INSERT INTO Member.USERS (user_id, nickname, email, reg_date)
VALUES ('tmp1', N'임시회원', NULL, '2026-01-01');
DECLARE @u int = SCOPE_IDENTITY();

SET STATISTICS IO ON;
-- 인덱스가 없는 쪽입니다. user_num 에 인덱스가 없습니다.
DELETE FROM Member.USERS WHERE num = @u;
SET STATISTICS IO OFF;

-- 인덱스가 있는 쪽입니다. post_num 에 IX_COMMENTS_post · IX_FILES_post 가 있습니다.
INSERT INTO Board.POSTS (board_num, user_num, group_num, depth, sort_no, title, hit_count, reg_date)
VALUES (1, 1, 999999, 0, 1, N'임시 글', 0, '2026-01-01');
DECLARE @p int = SCOPE_IDENTITY();

SET STATISTICS IO ON;
DELETE FROM Board.POSTS WHERE num = @p;
SET STATISTICS IO OFF;

읽기가 11과 2 로 다섯 배 차이지만, 이 숫자 자체는 작습니다. 문제는 훑기는 행이 늘면 같이 늘고 찾기는 거의 그대로라는 점입니다. 글이 500만 건이 되면 회원 하나를 지우는 데 500만 행을 봐야 합니다.

외래 키를 만들었다면 자식 쪽 열에 인덱스를 두는지 함께 생각하십시오. 부모를 자주 지우거나 그 열로 조회하는 자리라면 필요합니다. 반대로 부모를 지울 일이 없고 조회에도 사용하지 않는다면 두지 않아도 됩니다. 인덱스는 공짜가 아니고, 넣고 지울 때마다 함께 갱신됩니다(3.5).

걸려 있지만 믿지 못하는 제약

이미 자료가 들어 있는 표에 외래 키를 거는 일이 있습니다. 어긋난 행이 하나라도 있으면 제약이 붙지 않으므로, WITH NOCHECK 로 있는 자료는 넘어가고 앞으로 들어올 것만 검사하게 할 수 있습니다.

-- T_CHILD 에는 부모가 없는 행이 하나 섞여 있습니다(parent_num = 99).
ALTER TABLE Board.T_CHILD WITH NOCHECK
    ADD CONSTRAINT FK_TCHILD_TPARENT FOREIGN KEY (parent_num)
        REFERENCES Board.T_PARENT(num);

SELECT name, is_not_trusted AS 신뢰안함
FROM sys.foreign_keys WHERE name = 'FK_TCHILD_TPARENT';

is_not_trusted 가 1 이라는 것은 엔진이 이 제약을 사실로 여기지 않는다는 뜻입니다. 두 가지가 따라옵니다. 자료가 실제로 어긋나 있을 수 있고, 실행 계획을 세울 때 이 제약을 근거로 사용하지 못합니다. 믿을 수 있는 외래 키는 조인을 생략하는 근거가 되기도 하는데 그 기회를 잃습니다.

신뢰를 되찾으려면 다시 검사하게 해야 합니다. 어긋난 행이 남아 있으면 거부됩니다.

ALTER TABLE Board.T_CHILD WITH CHECK CHECK CONSTRAINT FK_TCHILD_TPARENT;

WITH CHECK CHECK 는 오타가 아닙니다. 앞쪽은 "지금 검사하라", 뒤쪽은 "이 제약을 켜라" 입니다. WITH NOCHECK 는 옮기는 동안의 임시 상태로만 사용하고, 자료를 정리한 뒤 반드시 신뢰를 되찾아 두십시오.

직접 해보기

이 단원의 T_ 로 시작하는 표들은 설명을 위한 것이므로 만들었다면 지우십시오.
DROP TABLE Board.T_POST, Board.T_CAT, Board.T_BOARD, Board.T_TREE, Board.T_CHILD, Board.T_PARENT;