RDBMS: FOREIGN KEY

※ RDBMS: FOREIGN KEY.
 
안녕하세요. 듀스트림입니다.
 
저는 이직하고 계속 야근과 주말 작업이 너무 많아서 지쳐가는 중입니다.. (조금 편한 자리로 갈 걸 그랬나 싶기도 해요..)
 
오늘 포스팅은 RDBMS를 사용한다면 꼭 알아야 하는 외래키(FOREIGN KEY)에 대한 내용입니다.

---

외래키는 그냥 FK 한 줄 추가하는 기능이 아니라,
데이터 모델의 품질과 애플리케이션 안정성, 운영상의 성능과 락 구조까지 모두 연결되는 핵심 요소입니다.

---

1. RDBMS에서 왜 외래키가 중요할까요?

관계형 DB에서 관계(relation)를 실제로 강제하는 장치가 외래키(FOREIGN KEY, FK) 입니다.

• 부모(Parent) 테이블: 기준(참조 대상)이 되는 테이블 (보통 PK/UK를 가진 쪽)
• 자식(Child) 테이블: 부모의 키 값을 참조하는 테이블 (FK를 가진 쪽)
• 레퍼런셜 무결성(Referential Integrity): 자식에 적힌 값이 반드시 부모에 존재해야 한다는 규칙

예를 들어:

-- 부모: 회원
CREATE TABLE member (
    member_id  BIGINT PRIMARY KEY,
    name       TEXT NOT NULL
);

-- 자식: 주문
CREATE TABLE orders (
    order_id   BIGINT PRIMARY KEY,
    member_id  BIGINT NOT NULL,
    order_at   TIMESTAMP NOT NULL,
    CONSTRAINT fk_orders_member
        FOREIGN KEY (member_id)
        REFERENCES member (member_id)
);

→ 여기서 orders.member_id 에는 항상 member.member_id에 존재하는 값만 들어갈 수 있고, 부모의 행을 지울 때는 자식의 존재 여부를 기준으로 허용/거부/전파할 수 있습니다.

---

2. FK의 기본 규칙

2.1 참조 가능한 대상

대부분의 DBMS에서 FK는 다음을 참조해야 합니다.

• PRIMARY KEY
• UNIQUE 제약이 걸린 컬럼(집합)
• 또는 그에 대응하는 UNIQUE 인덱스 (PostgreSQL·MySQL 8.4 등은 "부분 인덱스/비표준 인덱스" 제약에 대한 강화가 있음)

PostgreSQL: FK는 PK, UNIQUE 제약, 또는 non-partial UNIQUE 인덱스를 참조해야 함.
MySQL 8.4+: FK는 반드시 unique or primary key를 참조해야 하도록 기본 설정이 강화됨.

---

2.2 NULL 허용 여부

• FK 컬럼은 NULL 허용 가능합니다.
• NULL 값은 "아직 참조하지 않는다"는 의미로, 레퍼런셜 무결성 위반이 아닙니다.

---

2.3 ON DELETE / ON UPDATE 동작

SQL 표준과 대부분 DBMS에서 지원하는 옵션:

옵션	의미
RESTRICT	자식이 존재하면 부모의 DELETE/UPDATE 거부
NO ACTION	대부분 DB에서 RESTRICT와 동일하게 동작 (체크 시점 차이 정도)
CASCADE	부모 변경/삭제를 자식에게 전파 (DELETE → 자식 삭제, UPDATE → 자식 FK 값도 같이 변경)
SET NULL	부모 삭제/변경 시 자식 FK 값을 NULL로 설정 (자식 컬럼이 NULL 허용일 때만 가능)
SET DEFAULT	부모 삭제/변경 시 자식 FK 값을 DEFAULT 값으로 설정 (MySQL 등 일부는 지원 X 또는 제한적)

---

3. PostgreSQL에서의 FK

3.1 기본 문법

-- 1. 테이블 정의 시 FK 지정
CREATE TABLE orders (
    order_id   BIGSERIAL PRIMARY KEY,
    member_id  BIGINT NOT NULL,
    order_at   TIMESTAMPTZ NOT NULL,

    CONSTRAINT fk_orders_member
      FOREIGN KEY (member_id)
      REFERENCES member (member_id)
      ON DELETE RESTRICT
      ON UPDATE CASCADE
);


-- 2. ALTER TABLE 로 나중에 추가
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_member
  FOREIGN KEY (member_id)
  REFERENCES member (member_id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

 

5.5. Constraints

---

3.2 체크 시점: IMMEDIATE vs DEFERRED

PostgreSQL은 제약 조건을 지연(Deferrable)시킬 수 있는 기능을 가지고 있습니다.

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_member
  FOREIGN KEY (member_id)
  REFERENCES member (member_id)
  DEFERRABLE INITIALLY DEFERRED;

 

• NOT DEFERRABLE (기본값):
  • FK 검사는 각 INSERT/UPDATE 시점에 즉시 수행 (트랜잭션 커밋 전이라도 위반 시 바로 오류)
• DEFERRABLE INITIALLY DEFERRED:
  • 트랜잭션 동안에는 위반이 있어도 일단 허용하고, COMMIT 시점에 한 번에 검사
  • 서로를 참조하는 순환 FK, 복잡한 변경 로직에 유용

트랜잭션 단위로도 지연 여부를 바꿀 수 있습니다.

SET CONSTRAINTS fk_orders_member DEFERRED;

---

3.3 FK와 인덱스

• 부모 테이블:
  • FK가 참조하는 컬럼은 PK / UNIQUE / non-partial UNIQUE index 위에 존재해야 합니다.
• 자식 테이블:
  • PostgreSQL은 자식 FK에 자동으로 인덱스를 만들지 않습니다.
  • 하지만, 부모에서 DELETE/UPDATE 할 때 자식 테이블을 스캔해야 하기 때문에 FK 컬럼에 인덱스가 없으면 대량 삭제 시 풀 스캔 + 락 경쟁이 생길 수 있습니다.

공식 문서에서도 자식 FK 컬럼에 인덱스를 생성하는 것이 일반적으로 좋다고 안내합니다.

 

❗TIP:
• 자주 JOIN 되는 FK 컬럼 + 부모에서 삭제/갱신이 자주 일어나는 경우에는 반드시 인덱스를 고려합니다.
• 대용량 OLTP에서 FK 컬럼 인덱스가 없으면, 단일 DELETE 하나가 자식 테이블 풀 스캔 + 긴 락을 유발할 수 있습니다.

---

3.4 FK 메타데이터 조회

PostgreSQL는 information_schema와 카탈로그 뷰를 통해 FK 정보를 조회할 수 있습니다.

-- 특정 스키마의 FK 목록 조회
SELECT
    tc.constraint_name,
    tc.table_schema,
    tc.table_name   AS child_table,
    kcu.column_name AS child_column,
    ccu.table_name  AS parent_table,
    ccu.column_name AS parent_column,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints AS rc
  ON tc.constraint_name = rc.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON rc.unique_constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name, tc.constraint_name;

---

4. MySQL(MariaDB)에서의 FK

4.1 기본 전제: InnoDB + 제약사항

MariaDB / MySQL에서 외래키는 InnoDB에서만 지원됩니다.

• MyISAM 같은 엔진은 FK를 문법상 허용해도 실제로는 무시합니다.
• MariaDB에서 파티션 테이블은 외래키를 가질 수 없고, 다른 테이블이 이를 참조할 수도 없습니다.
  → 계정계에서 파티션을 많이 쓰면 FK 설계에 제약이 큽니다.

---

4.2 기본 문법

CREATE TABLE member (
    member_id BIGINT NOT NULL,
    name      VARCHAR(100) NOT NULL,
    PRIMARY KEY (member_id)
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id  BIGINT NOT NULL,
    member_id BIGINT NOT NULL,
    order_at  DATETIME NOT NULL,
    PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_member
      FOREIGN KEY (member_id)
      REFERENCES member (member_id)
      ON DELETE RESTRICT     -- 또는 CASCADE / SET NULL / NO ACTION
      ON UPDATE CASCADE
) ENGINE=InnoDB;

→ MySQL 문서에서도 RESTRICT, CASCADE, SET NULL, NO ACTION 네 가지를 지원한다고 명시합니다.

MySQL :: MySQL 8.4 Reference Manual :: 1.7.3.2 FOREIGN KEY Constraints

---

4.3 FK와 인덱스

• 부모 테이블:
  • FK는 기본적으로 PK 또는 UNIQUE 인덱스를 참조해야 하며, MySQL 8.4에서는 이 조건이 더 엄격하게 강제됩니다.
• 자식 테이블:
  • InnoDB는 FK 인덱스를 자동으로 생성하는 경우가 많지만, 어떤 인덱스를 쓸지 예측하기 어렵고, 복합 PK/FK 구성 시에는 설계자가 직접 인덱스를 설계하는 것이 좋습니다.

---

4.4 FOREIGN_KEY_CHECKS

대량 데이터 적재나 스키마 변경 시, MySQL/MariaDB에서는 다음처럼 FK 체크를 끌 수 있습니다.

SET FOREIGN_KEY_CHECKS = 0;

-- 대량 INSERT / ALTER / DROP/CREATE TABLE 등…

SET FOREIGN_KEY_CHECKS = 1;

• FOREIGN_KEY_CHECKS = 0 상태에서 FK를 위반하는 데이터를 넣어버리면, 다시 1로 돌렸을 때도 이미 들어간 데이터는 자동으로 정리되지 않습니다.
• 가능하면 일시적인 마이그레이션 상황에서만, 명확한 절차/검증 쿼리와 함께 사용할 것을 권장합니다.

---

5. ON DELETE / ON UPDATE

관계 하나하나마다 어떤 동작이 비즈니스 규칙에 맞는지를 고민해야 합니다.

5.1 대표적인 패턴

1. 마스터-디테일 (주문·주문상세)
   • orders (부모) – order_items (자식)
   • 일반적으로: ON DELETE CASCADE
     • 주문 삭제 시 주문 상세도 같이 삭제
     • 운영에서 실수로 주문을 지웠을 때, 상세도 같이 사라지므로 로그/아카이브 테이블을 별도로 두는 게 보통 안전
2. 마스터-참조 (회원·주문, 상품·주문)
   • member – orders
   • 보통: ON DELETE RESTRICT 또는 NO ACTION
     • 회원에 주문이 걸려 있으면 회원 삭제 금지
     • 실제 서비스에서는 회원을 논리 삭제(플래그)로 처리하고 FK는 그대로 유지하는 경우가 많음
3. 선택적 참조 (게시글·첨부파일 or 옵션)
   • ON DELETE SET NULL
     • 부모가 삭제되면 자식 FK를 NULL로 두고 record는 살려둔다든지, 고아 레코드를 나중에 별도의 배치로 처리
4. 코드 테이블 (공통 코드, 상태 코드 등)
   • 거의 항상 RESTRICT/NO ACTION
   • 코드 테이블 값은 역사적으로 남겨야 하는 값이므로, 삭제·변경 자체를 엄격하게 관리

---

5.2 CASCADE 지옥 피하기

ON DELETE CASCADE를 사용하면 하나의 DELETE가 여러 개의 테이블로 전파되어 실제 트랜잭션 범위가 눈에 보이지 않아 사고의 여지가 커집니다.
 
따라서,

• 로그/이력/회계성 테이블은 CASCADE를 사용하지 말고, 별도 정리 배치(예: 5년 이상 지난 데이터 purge)를 두는 것을 권장 드립니다.
• 트랜잭션 내에서 어떤 레코드가 어느 테이블까지 삭제되는지를 명확히 알고 있어야 합니다.

---

6. FK와 성능·락(Lock)

외래키는 단순 제약이 아니라 락과 조회 비용을 동반합니다.

6.1 INSERT / UPDATE (자식 쪽)

자식 테이블에 INSERT/UPDATE가 발생하면:

1. DB는 FK 컬럼 값으로 부모 테이블을 조회
2. 해당 값이 존재하는지 확인
3. 필요하면 부모 row에 공유(lock-S)를 걸어 다른 세션이 동시에 삭제하지 못하게 함

→ 이 때 부모 PK/UK 인덱스가 튜닝 포인트입니다.

---

6.2 DELETE / UPDATE (부모 쪽)

부모 테이블에서 PK/UK 값이 바뀌거나 삭제될 때:

1. DB는 자식 테이블에서 FK 컬럼으로 해당 값이 있는지 조회
2. FK 인덱스가 없을 경우 → 자식 테이블 풀 스캔 + 대량 row-lock
3. ON DELETE RESTRICT / CASCADE / SET NULL 등 정책에 따라 자식 처리

→ 자식 FK 인덱스의 존재 여부가 동시성·성능의 핵심입니다.

---

6.3 SOFT-FK vs HARD-FK 관점

• HARD-FK:
  • DB에 실제 FK 제약을 생성해 무결성을 엔진이 보장합니다.
  • 장점: 휴먼 에러 방지, ETL/배치/애플리케이션이 혼재되어도 안전합니다.
  • 단점: 락·성능 비용, 복잡한 FK 구조일 때 DDL/배치 작업 난이도 상승합니다.
• SOFT-FK:
  • DB에는 FK 제약이 없고 애플리케이션/ETL 로직, 배치 검증 스크립트, 거버넌스를 통해 논리적으로만 보장합니다.
  • 장점: 파티션·샤딩·레플리카 구조에서 제약을 덜 받음, 일부 대용량 DW/ODS에서 활용합니다.
  • 단점: 잘못된 데이터가 들어가도 DB 입장에서는 정상이며, 나중에 찾기 어렵고 삭제/정리 배치 중에 고아 레코드, 카운트 불일치 등 장애로 이어질 수 있습니다.

계정계/OLTP 영역에서는 기본적으로 HARD-FK(실제 외래키 제약)를 쓰고, BI/DW·로그성 데이터에서는 상황에 따라 SOFT-FK를 선택하는 패턴이 많습니다.

---

7. 파티션과 FK

7.1 PostgreSQL

• PostgreSQL은 버전에 따라 제약이 조금씩 다르지만, 최근 버전(14~17 기준)에서는 파티션된 테이블에 FK를 두고/참조하는 기능이 계속 개선되는 중입니다.
• 실제로는 FK와 파티션 설계를 동시에 복잡하게 가져가기보다는 핵심 관계만 FK로 강제하고 나머지는 검사 뷰/배치로 관리하는 전략을 많이 사용합니다.

---

7.2 MariaDB

MariaDB 공식 문서: "Partitioned tables cannot contain foreign keys, and cannot be referenced by a foreign key."

• 즉, 파티션 테이블에는 FK를 걸 수 없고 다른 테이블에서 참조도 불가능합니다.
• 계정계에서 날짜 파티션을 적극 사용한다면, FK는 대부분 논리적(SOFT-FK)으로만 관리하게 되고 배치 검증 및 거버넌스가 매우 중요해집니다.

---

8. FK 운영·관리 팁

8.1 FK 목록·의존 관계 문서화

• PostgreSQL: information_schema / pg_constraint / pg_class 등을 이용해 FK를 추출하여 문서화
• MySQL/MariaDB: INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, INNODB_SYS_FOREIGN 등 활용

이 정보를 정기적으로 덤프해서 ERD/문서/거버넌스 표로 관리하면 스키마 변경 영향도 분석, 배치 순서(부모→자식), 마이그레이션 시 의존 관계 파악에 큰 도움이 됩니다.

---

8.2 대량 마이그레이션 절차 예시

1. FK 의존 관계 파악
   • 부모 → 자식 순서 정리
2. 데이터 적재 순서 결정
   • 부모 테이블부터 insert
   • 필요한 경우, FK 일시 해제(MySQL: SET FOREIGN_KEY_CHECKS=0) → 이후 검증 필수
3. 검증 쿼리
   • 자식에 존재하는 FK 값이 부모에는 없는 건수 체크
4. FK 생성/재생성
   • 제약 이름·인덱스 이름 규칙에 맞춰 생성
5. 오류 발생 시 롤백/재시도 전략 마련

---

8.3 DDL 변경 시 주의

• FK 있는 테이블에서 컬럼 타입 변경, 이름 변경, PK 재구성, 파티션 변경 등은 FK DROP/ADD까지 포함한 전체 영향도 분석이 필요합니다.
• 대규모 UPDATE/DELETE + FK 존재
  → 운영 시간 중에는 지양하고 배치 윈도우에 인덱스를 활용한 적절한 단위(batch 단위)로 나누는 것이 좋습니다.

---

9. 흔한 FK 오류 상황들

1. 타입 불일치
   • 부모 PK는 BIGINT, 자식 FK는 INT → FK 생성 실패/경고
2. 인덱스 조건 불충족
   • 참조 대상 컬럼에 UNIQUE/PK 없음 → FK 생성 시 오류 (특히 MySQL 8.4)
3. 기존 데이터가 이미 위반 상태
   • FK 생성 시 기존 자식 데이터에 부모가 없음 → 제약 생성 실패
4. DELETE/UPDATE 시 위반
   • "cannot delete or update a parent row: a foreign key constraint fails"
   • "update or delete on table violates foreign key constraint …"
   • 비즈니스 규칙에 맞는 ON DELETE/ON UPDATE 설정 또는 데이터 클렌징 필요

---

10. FK 설계·운영 체크리스트

10.1 설계 단계

1. 이 관계는 정말로 레퍼런셜 무결성이 필요한가?
   • (YES면 FK, NO면 SOFT-FK 고려)
2. 부모의 키 후보 중 가장 안정적인 값을 참조하고 있는가?
   • (자연 키 vs Surrogate 키)
3. ON DELETE / ON UPDATE 옵션이 비즈니스 규칙에 부합하는가?
   • 로그/회계/이력에는 CASCADE 지양
4. 부모·자식 테이블 모두에 적절한 인덱스 계획을 세웠는가?
   • 부모: PK/UK 인덱스
   • 자식: FK 컬럼 인덱스(복합 인덱스 순서 포함)
5. MariaDB에서 파티션을 쓸 계획이라면,
   • FK를 실제로 쓸 수 있는 구조인지, 아니면 SOFT-FK로 설계해야 하는지 검토했는가?

---

10.2 운영·배포 단계

1. 새로운 FK/변경된 FK에 대해 사전 검증 쿼리(고아 레코드 존재 여부)를 준비했는가?
2. 대량 DML / DDL 작업 전 FK 관계 문서화 + 영향도 분석을 했는가?
3. FK 이름·인덱스 이름 규칙 fk_<child>_<parent> 같은 규칙으로 일관성을 유지하고 있는가?
4. 정기적으로 FK 위반 여부를 점검하는 배치/리포트가 존재하는가? (특히 SOFT-FK 사용 구간)

---

10.3 성능·장애 대응

• FK 관련 Deadlock/Lock wait timeout 발생 시
  → FK 인덱스 유무, DELETE/UPDATE 패턴, 트랜잭션 범위를 점검하고 있는가?
  
  
• FK 때문에 대규모 트랜잭션이 길어지는 경우
  → 작은 배치 단위로 나누거나 필요한 범위에서만 CASCADE를 쓰도록 설계를 조정했는가?

---

요약하면,

1. 가능하면 HARD-FK로 무결성을 DB가 책임지게 하고,
2. FK마다 ON DELETE / ON UPDATE 정책을 비즈니스 규칙에 맞게 명확하게 선택하고,
3. 부모·자식 모두 적절한 인덱스를 갖추며,
4. 파티션·샤딩·DW 구간에서는 필요한 경우에만 SOFT-FK + 거버넌스/배치 검증을 설계하는 것이 좋습니다.

---

오늘은 여기까지~