외래키는 왜 필요할까? 나누어진 테이블을 다시 연결하는 방법

회원정보 화면에는 이름과 연락처가 보이고, 주문내역 화면에는 상품명과 결제금액이 나타난다. 사용자는 두 화면을 오가면서도 주문한 사람과 주문 기록이 당연히 연결되어 있다고 여긴다. 하지만 데이터베이스 안에서는 고객과 주문을 한 표에 모두 적어 두지 않는 경우가 많다.

고객 정보는 고객 테이블에, 주문은 주문 테이블에 나누어 저장한다. 한 고객이 주문을 열 번 했다면 고객의 이름과 주소를 열 번 반복해서 기록하는 대신, 주문마다 고객을 가리키는 값만 남긴다. 표는 떨어져 있지만 정보의 관계는 끊어지지 않는다.

앞선 글에서 살펴본 기본키가 자기 테이블 안의 한 행을 구별하는 이름표라면, 외래키는 다른 테이블에 있는 그 이름표를 가져와 관계를 표시하는 값이다. 관계형 데이터베이스가 여러 표를 따로 보관하면서도 하나의 정보처럼 다룰 수 있는 바탕에는 이 연결 방식이 있다.

한줄 요약
외래키는 한 테이블의 열이 다른 테이블의 기본키를 가리키게 하여 서로 떨어진 기록의 관계를 남긴다. 이 연결을 데이터베이스가 확인하도록 하면 존재하지 않는 고객의 주문이나 등록되지 않은 장비의 정비 이력처럼 앞뒤가 맞지 않는 데이터도 막을 수 있다.

모든 내용을 한 표에 넣으면 처음에는 편해 보인다

작은 가게의 주문을 엑셀로 관리한다고 해보자. 한 줄에 주문번호, 고객 이름, 전화번호, 주소, 상품명, 수량과 결제금액을 차례로 적으면 당장 필요한 내용이 한눈에 들어온다. 주문이 몇 건 되지 않을 때에는 굳이 표를 나눌 이유가 없어 보인다.

주문이 쌓이기 시작하면 같은 고객의 정보도 계속 반복된다. 한 사람이 다섯 번 주문하면 이름과 전화번호, 주소도 다섯 줄에 적힌다. 이 고객이 이사하여 주소를 바꾸면 과거 주문에 적힌 주소까지 모두 고쳐야 하는지, 앞으로 배송할 주소만 바꾸어야 하는지 판단해야 한다.

수정 과정에서 네 줄은 바꾸고 한 줄을 빠뜨리면 데이터베이스 안에는 같은 사람의 주소가 두 가지로 남는다. 고객이 아직 주문하지 않았다면 주문 정보 없이 고객만 등록하기도 어렵다. 반대로 마지막 주문 기록을 지우다가 고객의 연락처까지 함께 사라질 수도 있다. 모든 값을 한 표에 모은 편리함이 어느 순간 중복과 불일치의 원인이 된다.

그래서 성격이 다른 대상을 구분하여 저장한다. 고객 테이블에는 고객 한 명당 한 행을 두고, 주문 테이블에는 주문 한 건당 한 행을 둔다. 상품 역시 별도의 테이블로 관리할 수 있다. 무엇을 하나의 대상으로 볼 것인지 정하고 각 대상을 나누어 기록하면 같은 값을 불필요하게 되풀이하지 않아도 된다.

표를 나누는 순간 새로운 질문이 생긴다

고객과 주문을 별도로 저장하면 중복은 줄어든다. 그러나 주문 테이블만 보았을 때 그 주문이 누구의 것인지 알 수 있어야 한다. 고객 이름을 다시 적으면 동명이인 문제가 되살아나고, 전화번호를 적으면 번호가 바뀔 때 연결 기준도 함께 흔들린다.

여기에서 앞선 글의 기본키가 다시 필요해진다. 고객 테이블의 각 행에 고객번호라는 기본키가 있다면 주문 테이블에는 주문한 사람의 고객번호를 남길 수 있다. 고객 테이블의 고객번호는 그 행을 식별하는 기본키이고, 주문 테이블에 기록된 고객번호는 다른 표의 기본키를 가리키므로 외래키가 된다.

같은 값도 놓인 곳에 따라 역할이 달라진다. 고객번호 1007은 고객 테이블에서는 한 고객을 구별하지만, 주문 테이블에서는 해당 주문이 1007번 고객에게 속한다는 관계를 나타낸다. 주문이 여러 건이면 주문 테이블에 1007이 여러 번 등장할 수 있다. 기본키는 자기 테이블에서 중복될 수 없지만, 외래키는 여러 행에서 같은 대상을 가리킬 수 있기 때문이다.

이 구조는 고객 한 명이 여러 주문을 갖는 ‘일대다 관계’를 만든다. 학교라면 한 학과에 여러 학생이 속할 수 있고, 장비관리 체계라면 장비 한 대에 여러 건의 점검 및 정비 이력이 이어질 수 있다. 서로 다른 업무처럼 보여도 한쪽의 한 행을 다른 쪽의 여러 행이 참조한다는 구조는 같다.

외래키는 단순히 같은 번호를 적는 칸이 아니다

두 표에 고객번호 열이 있다고 해서 자동으로 관계가 보장되는 것은 아니다. 엑셀 파일 두 개에 같은 번호를 적어 놓아도 한쪽의 값을 잘못 입력하거나 지울 수 있다. 연결되어 보일 뿐, 프로그램이 그 관계를 검사하지 않는다면 존재하지 않는 고객번호가 주문 기록에 들어갈 수도 있다.

데이터베이스에서 외래키 제약조건을 설정하면 주문 테이블에 입력하는 고객번호가 고객 테이블에 실제로 존재하는지 확인할 수 있다. 고객 테이블에 1007번만 있는데 주문에 9000번 고객을 적으려 하면 데이터베이스가 저장을 거부한다. 이를 통해 자식 기록이 가리키는 부모 기록이 존재하도록 유지한다.

이 규칙을 참조 무결성이라고 한다. 말은 다소 딱딱하지만 뜻은 분명하다. 연결된 데이터의 앞뒤가 맞아야 한다는 의미다. 등록되지 않은 학생의 수강신청, 존재하지 않는 상품의 주문, 자산대장에 없는 장비의 정비 이력이 생기지 않도록 관계 자체를 데이터베이스가 확인한다.

외래키 열에 빈값을 허용할지는 업무에 따라 달라진다. 모든 직원이 반드시 부서에 속해야 한다면 부서번호를 비워둘 수 없게 만들 수 있다. 반면 배치 전 대기 중인 직원도 등록해야 한다면 잠시 부서가 없는 상태를 허용할 수 있다. 외래키라는 기술만으로 정답이 정해지는 것이 아니라 현실의 업무 규칙을 어떻게 데이터로 옮기느냐가 중요하다.

연결된 기록을 지울 때 데이터베이스는 멈춰 세운다

고객이 탈퇴했다고 해서 고객 행을 바로 삭제하면 어떤 일이 생길까. 주문 테이블에는 그 고객번호를 가리키는 기록이 남을 수 있다. 주문은 존재하지만 누구의 주문인지 확인할 수 없는 고아 기록이 되는 셈이다. 회계나 배송, 사후 분쟁에 필요한 거래 이력이라면 더 큰 문제가 된다.

외래키가 설정되어 있으면 데이터베이스는 연결된 주문이 남아 있는 고객의 삭제를 막을 수 있다. 담당자는 주문을 어떻게 보존할지 먼저 결정해야 한다. 고객을 실제로 삭제하지 않고 탈퇴 상태로 바꾸거나, 법적 보존기간이 지난 관련 기록을 정해진 절차에 따라 함께 처리하는 방법이 있다.

부모 기록을 지울 때 연결된 자식 기록도 함께 삭제하도록 설정하는 방식도 있다. 게시판 글을 삭제하면 그 글에 달린 임시 첨부파일도 함께 정리하는 경우처럼, 두 데이터가 독립적으로 존재할 이유가 없을 때 유용하다. 그러나 고객을 지웠다는 이유로 결제 기록까지 자동으로 사라지게 해서는 곤란할 수 있다. 편리한 기능이지만 데이터의 성격과 보존 의무를 따지지 않고 적용하면 위험하다.

참조 대상의 기본키가 바뀔 때 외래키 값을 함께 갱신하는 선택도 가능하다. 다만 처음부터 잘 바뀌지 않는 대체키를 기본키로 사용하면 이런 연쇄 수정 자체를 줄일 수 있다. 좋은 식별값을 정하는 일이 외래키 설계와 떨어져 있지 않은 이유다.

현실의 관계는 일대다만으로 끝나지 않는다

한 고객이 여러 주문을 하는 관계는 비교적 단순하다. 하지만 학생과 강좌처럼 양쪽 모두 여러 대상을 가질 수 있는 경우도 있다. 한 학생은 여러 강좌를 듣고, 한 강좌에도 여러 학생이 참여한다. 학생 테이블에 강좌번호를 하나만 적거나 강좌 테이블에 학생번호를 하나만 적어서는 이 관계를 표현할 수 없다.

이때는 수강 테이블처럼 관계 자체를 기록하는 중간 테이블을 만든다. 한 행에 학생번호와 강좌번호를 함께 두어 누가 어떤 강좌를 듣는지 저장한다. 두 값은 각각 학생 테이블과 강좌 테이블을 가리키는 외래키가 된다. 필요하다면 두 값을 묶어 같은 학생이 같은 강좌에 중복 등록되지 않도록 할 수도 있다.

중간 테이블에는 관계에서만 생기는 정보도 담을 수 있다. 수강신청일과 성적은 학생만의 속성도, 강좌만의 속성도 아니다. 특정 학생이 특정 강좌를 수강하면서 생긴 값이다. 장비와 임무의 관계라면 투입 시각이나 운용 역할이 여기에 해당할 수 있다.

관계형 데이터베이스에서 표를 나눈다는 것은 자료를 조각내어 흩어 놓는 일이 아니다. 사람, 주문, 상품처럼 서로 다른 대상을 구분하고, 그 사이의 관계도 하나의 관리 대상처럼 명확하게 기록하는 일이다. 외래키는 그 관계가 어느 행과 어느 행 사이에 놓여 있는지 잃어버리지 않게 한다.

정보체계가 많아질수록 연결 기준은 더 복잡해진다

하나의 데이터베이스 안에서는 기본키와 외래키 제약조건으로 관계를 비교적 엄격하게 관리할 수 있다. 장비 테이블의 자산번호를 정비 이력 테이블이 가리키도록 만들면, 어느 장비의 언제 발생한 이력인지 일관된 구조로 남길 수 있다.

그러나 서로 독립적으로 개발된 정보체계 사이에서는 같은 방식이 그대로 적용되지 않는다. 한 체계는 장비를 내부 숫자로 구별하고, 다른 체계는 자산번호와 부대코드를 조합하여 관리할 수 있다. 각 데이터베이스 안에서는 모두 올바른 외래키를 사용해도 체계 밖으로 나오는 순간 두 번호가 같은 대상을 뜻하는지 별도의 확인이 필요하다.

정보의 속도가 중요한 환경에서는 이 차이가 단순한 전산 불편으로 끝나지 않는다. 센서가 만든 관측 기록, 장비 상태, 부대 정보와 임무 자료가 서로 다른 식별 기준을 사용하면 데이터를 한 화면에 모은 뒤에도 관계를 잘못 해석할 수 있다. 정확한 연결에는 키뿐 아니라 공통 코드, 데이터 표준, 시간과 위치의 기준, 체계 간 식별자 매핑이 함께 필요하다.

외래키는 하나의 데이터베이스 내부에서 관계를 지키는 기본 장치다. 이것을 이해하면 여러 체계의 데이터를 연동할 때 왜 단순히 통신망을 연결하는 것만으로 충분하지 않은지도 조금씩 보이기 시작한다. 데이터가 전달되었다는 사실과 그 데이터가 올바른 대상을 가리킨다는 사실은 서로 다른 문제다.

짧은 생각
정보체계의 화면에서는 서로 관련된 자료가 한 번에 보이기 때문에 그 연결이 원래부터 존재했던 것처럼 느껴진다. 실제로는 대상을 구별하는 기준과 관계를 잇는 규칙을 누군가 먼저 정해 두었기 때문에 가능한 일이다. 급하게 정보를 찾아야 할수록 화면의 편리함보다 그 뒤의 연결이 믿을 만한지가 더 중요해진다.

관계를 만들면 같은 정보의 반복도 보이기 시작한다

기본키와 외래키를 이용하면 표를 나누어도 필요한 정보를 다시 연결할 수 있다. 고객 정보는 한 번만 저장하고 주문에는 고객번호만 남길 수 있으며, 존재하지 않는 고객을 가리키는 주문도 막을 수 있다. 데이터의 중복을 줄이면서 관계의 정확성까지 지키는 구조가 만들어진다.

그렇다고 표를 많이 나누는 것이 언제나 좋은 설계라는 뜻은 아니다. 무엇을 별도의 대상으로 볼지, 어느 값이 다른 값에 의존하는지, 실제 업무에서 함께 바뀌어야 하는 정보가 무엇인지 따져야 한다. 잘못 나누면 필요한 내용을 확인할 때마다 지나치게 많은 표를 연결해야 하고, 덜 나누면 같은 값이 여러 곳에 반복되어 수정할 때 불일치가 생긴다.

다음 글에서는 바로 이 반복 문제를 살펴보려 한다. 데이터 중복은 저장공간을 조금 더 차지하는 데서 끝나지 않는다. 같은 사실이 여러 행에 흩어지면 어느 값이 맞는지 알기 어려워지고, 한 번의 수정으로 모든 기록을 일치시키기도 힘들어진다. 관계형 데이터베이스가 정규화라는 원칙을 사용하는 이유도 여기에 있다.