개발 학습 실습실

이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

기초개념 › DB

외래 키

외래 키는 무엇이고, 없으면 무엇이 달라지는가

주문 테이블의 member_id 열에는 "이 주문을 한 회원의 번호"를 적습니다. 이 열에 FOREIGN KEY (member_id) REFERENCES member(id)라는 줄을 적으면 외래 키가 됩니다. member_id의 값은 member 테이블에 실제로 있는 id여야 한다고 DB에 약속하는 것이고, 약속을 어기는 INSERT와 DELETE는 DB가 거부합니다. 이 실습은 그 줄을 지우거나 둘째 주문의 member_id를 바꿔 가며 같은 SQL을 실행해, DB가 문장을 받아들이는지와 마지막에 없는 회원을 가리키는 주문이 남는지를 봅니다.

실행으로 대조 · H2 2.4.240에서 여섯 가지 조합을 실행해 대조, 규칙은 MySQL 문서와 대조

이 실습에 나오는 개념

기본 키, 유니크 키, 외래 키
기본 키(PK)는 테이블의 각 행을 유일하게 식별하는 컬럼으로 값이 중복되거나 비어 있을 수 없고 테이블에 하나만 둔다. 유니크 키는 값의 중복을 막는 제약으로 여러 개 둘 수 있다. 외래 키(FK)는 다른 테이블의 기본 키(또는 유니크 키)를 참조하는 컬럼으로, 참조 대상이 실제로 존재하도록 보장한다.
제약 조건
테이블에 들어갈 수 있는 값의 규칙을 테이블 정의에 적어 두는 것이다. 기본 키, 유니크, NOT NULL, CHECK, 외래 키가 대표적이다. 규칙에 어긋나는 INSERT, UPDATE, DELETE가 오면 데이터베이스가 그 문장을 오류로 거부하고 테이블은 바뀌지 않는다. 애플리케이션 코드에서만 검사하면 검사를 빠뜨린 코드나 직접 실행한 SQL로 잘못된 값이 들어갈 수 있지만, 제약 조건은 어떤 경로로 들어오는 값이든 똑같이 막는다. 제약이 없으면 잘못된 값도 오류 없이 저장되고, 문제는 나중에 조회할 때에야 드러난다.
참조 무결성
다른 테이블의 행을 가리키는 값은 그 행이 실제로 있어야 한다는 규칙이다. 예를 들어 주문의 member_id가 3이면 member 테이블에 id가 3인 회원이 있어야 하고, 주문이 남아 있는 동안 그 회원이 지워져서는 안 된다. 외래 키를 선언하면 DB가 이 규칙을 문장마다 확인해, 없는 값을 넣는 INSERT와 가리키는 행이 남아 있는 부모 행을 지우는 DELETE를 거부한다. 외래 키가 없으면 가리키는 행이 없는 주문이 생겨도 DB는 알아채지 못하므로, 애플리케이션이 매번 확인하거나 나중에 데이터가 어긋난 것을 찾아내야 한다. 외래 키 열이 비어 있는(NULL) 행은 가리키는 대상이 없다고 적은 것이어서, MySQL InnoDB를 포함한 대부분의 DB에서 이 규칙에 어긋나지 않는 것으로 본다.
부모 테이블과 자식 테이블
외래 키로 이어진 두 테이블 가운데 값을 가리키는 쪽을 자식 테이블, 가리킴을 당하는 쪽을 부모 테이블이라고 한다. 주문(orders)의 member_id가 회원(member)의 id를 가리키면 orders가 자식이고 member가 부모이다. 외래 키는 자식 테이블에 정의하며, 자식 행의 외래 키 값은 부모 테이블에 있는 값이어야 한다. 회원 한 명이 주문을 여러 번 할 수 있으므로 부모 행 하나를 여러 자식 행이 가리킬 수 있지만, 자식 행 하나는 부모 행 하나만 가리킨다. 부모 행을 지우거나 바꿀 때 어떻게 할지는 외래 키의 참조 동작으로 정하고, 적지 않으면 기본값은 NO ACTION이다.

이렇게 바꿔 보세요

처음 설정으로 실행하면

  1. CREATE TABLE: DB가 member와 orders라는 테이블 두 개를 만듭니다. 지금은 행이 하나도 없습니다. orders의 member_id 열에는 "이 주문을 한 회원의 번호"를 적습니다. orders에 FOREIGN KEY (member_id) REFERENCES member(id) 줄이 있으므로, DB는 "member_id 열의 값은 member 테이블에 실제로 있는 id여야 한다"는 약속을 기억해 둡니다. 이것이 외래 키입니다.
  2. INSERT INTO member VALUES (1, '김철수'): DB가 member 테이블에 id 1, 김철수 행을 넣습니다. 이제 회원이 한 명 있습니다.
  3. INSERT INTO orders VALUES (10, 1): 주문 10을 넣습니다. member_id에 적은 1은 "이 주문을 한 회원의 번호"이고, member 테이블의 id가 1인 행(김철수)을 가리킵니다. DB는 행을 넣기 전에 외래 키의 약속을 확인합니다. member 테이블에서 id가 1인 행을 찾아보고, 있으므로 통과해 주문 10이 들어갑니다. 이 확인을 하는 코드를 우리는 쓰지 않았습니다. DB가 INSERT가 올 때마다 스스로 합니다.
  4. INSERT INTO orders VALUES (11, 99): 둘째 주문을 넣으려 합니다. DB는 행을 넣기 전에 member 테이블에서 id가 99인 행을 찾아봅니다. 이 확인도 DB가 스스로 합니다.
  5. DB의 확인 결과: member 테이블에 id가 99인 행이 없습니다. 약속을 어기므로 DB는 이 INSERT를 거부하고, "참조하는 행이 없다"는 종류의 오류를 돌려줍니다. 주문 11은 orders에 들어가지 않았고, 테이블은 INSERT 전 그대로입니다.
  6. DELETE FROM member WHERE id = 1: 회원 1(김철수)을 지우려 합니다. DB는 지우기 전에 orders 테이블에서 member_id가 1인 주문이 있는지 찾아봅니다. 주문 10이 있습니다.
  7. DB의 확인 결과: 이 회원을 가리키는 주문이 남아 있으므로 DB는 이 DELETE를 거부하고, "다른 테이블이 참조하고 있다"는 종류의 오류를 돌려줍니다. 회원 1은 지워지지 않았고 member 테이블은 DELETE 전 그대로입니다. 그래서 주문이 가리키는 회원이 사라지는 일은 일어나지 않습니다.
  8. SELECT * FROM orders ORDER BY id: orders에 남은 주문을 id 순으로 읽습니다. 결과는 (10, 1)입니다. 주문 11은 거부되어 들어가지 않았고, 남은 주문 10은 member에 있는 회원 1을 가리킵니다.
  9. 참고로 이 코드에는 없는 확인용 문장을 따로 실행합니다. SELECT COUNT(*) FROM orders o LEFT JOIN member m ON o.member_id = m.id WHERE o.member_id IS NOT NULL AND m.id IS NULL은 orders의 주문마다 member에서 짝을 찾아, 짝이 없는 주문을 셉니다. 결과는 0건입니다. 외래 키가 있어서 짝이 없는 주문은 한 건도 생기지 못했습니다.

실행 결과

코드

CREATE TABLE member (
    id   INT PRIMARY KEY,
    name VARCHAR(20)
);
CREATE TABLE orders (
    id        INT PRIMARY KEY,
    member_id INT,
    -- 외래 키: member_id 열의 값은 member 테이블에 있는 id여야 한다
    FOREIGN KEY (member_id) REFERENCES member(id)
);

INSERT INTO member VALUES (1, '김철수');
INSERT INTO orders VALUES (10, 1);
INSERT INTO orders VALUES (11, 99);

DELETE FROM member WHERE id = 1;
SELECT * FROM orders ORDER BY id;

이 모형의 전제

H2 2.4.240(메모리 DB, JDBC)에서 실행해 대조했습니다. 여섯 가지 조합을 모두, 화면의 SQL 전체(주석 줄 포함)를 글자 그대로 한 문장씩 실행했고, 문장 하나가 거부되어도 다음 문장은 계속 실행했습니다. 둘째 주문의 INSERT와 회원 1을 지우는 DELETE가 받아들여졌는지 거부됐는지와 오류의 종류(H2의 SQLSTATE 23506은 INSERT가 외래 키를 어김, 23503은 DELETE가 참조하는 행을 지우려 함), 마지막 SELECT 결과를 모형과 대조했습니다. "없는 회원을 가리키는 주문"은 화면의 코드에 없는 확인용 문장 SELECT COUNT(*) FROM orders o LEFT JOIN member m ON o.member_id = m.id WHERE o.member_id IS NOT NULL AND m.id IS NULL을 Probe가 마지막에 따로 실행해 센 값입니다. 화면의 코드는 열 정의 옆에 REFERENCES만 적는 모양이 아니라 FOREIGN KEY (member_id) REFERENCES member(id) 줄을 따로 쓰는 모양입니다. MySQL 8.4 문서(1.7.2.3절 FOREIGN KEY Constraint Differences와 15.1.20절 CREATE TABLE의 Indexes, Foreign Keys, and CHECK Constraints)가 열 정의 옆에 적은 REFERENCES를 해석만 하고 무시하며 별도의 FOREIGN KEY 줄에 적은 것만 받아들인다고 하기 때문입니다. H2는 두 모양을 모두 외래 키로 받아들이는 것을 따로 확인했습니다. MySQL 8.4 문서의 15.1.20.5절 FOREIGN KEY Constraints를 직접 열어, 외래 키 제약은 자식 테이블에 정의하며, 부모 테이블에 짝이 되는 값이 없는 값을 자식 테이블에 만드는 INSERT와 UPDATE는 거부되고, ON DELETE를 적지 않으면 기본 동작은 NO ACTION이며 InnoDB에서는 RESTRICT와 같이 부모 행의 삭제가 즉시 거부된다는 것을 확인했습니다. 외래 키 열이 NULL이면 부모에 짝이 없어도 INSERT가 가능하다는 것은 15.1.20절과 1.7.2.3절의 MATCH 설명(MATCH SIMPLE은 외래 키가 NULL인 것을 허용)을 따랐습니다. 오류의 번호와 메시지의 글자는 DB마다 다르므로 화면에는 종류만 적었습니다. MySQL에서의 실행 대조는 하지 않았습니다. 그래서 위 규칙은 문서와 H2 실행이 일치한다는 근거이고, MySQL이 같은 SQL에서 같은 결과를 내는지는 실행으로 확인하지 않았습니다. FOREIGN KEY 줄이 없는 테이블이 없는 회원 번호를 받아들이고 회원 삭제를 막지 않는 것은 H2에서 관찰한 동작입니다. 이 실습은 외래 키의 기본 동작(거부)만 다루며, ON DELETE CASCADE 같은 참조 동작, UPDATE, NOT NULL, 복합 외래 키는 다루지 않습니다.

앞 실습: 기본 키 · 다음 실습: WHERE

이 실습에 나오는 개념

이 실습에서 보는 것

설정

코드


      
    


    

      
그림

      

      

    


    

      

      

      

    


  


  

    

      
실행 비교

      

      

    

    

      
이렇게 바꿔 보세요