[DB설계] 17. 참조제약(외부키)

표준 SQL 및 데이터베이스 입문

Mar 1. 2025

시험 테이블에 등록되는 학생번호는 반드시 학생마스터에 등록되어 있어야 한다는 참조규칙이 바로 참조제약입니다. 입력하는 쪽에서 주의하거나 별도로 공들여 처리하는 것이 아니라, 참조제약을 테이블에 설정해 두는 것이 핵심입니다. 참고로 다른 테이블을 참조하는 열은 '외부키(foreign key)'라고 불리므로, 참조제약은 외부키 제약이라고도 합니다.

다른 테이블을 참조하는 열을 외부키(Foreign Key, FK)라고 하고, 외부키의 값은 참조대상(참조테이블의 특정 행)을 특정할 수 있어야 한다는 제약을 참조제약, 참조정합성 제약(referential constraint) 또는 '외부키 제약(foreign key constgraint)'이라고 합니다. 대부분의 경우, 참조되는 쪽의 열은 주키이지만, 후보키이면 즉 NULL도 중복도 없는 열이라면 주키가 아니어도 괜찮습니다.

참조 제약이 설정되어 있는 경우, 참조되는 쪽의 테이블이나 열은 부모테이블(Parent Table), '부모키(Parent Key)'라고 하고, 참조하는 쪽의 테이블이나 열은 자식테이블(Child Table), 자식키(Child key) 또는 외부 테이블(External Table), '외부키(External Key)'라고 부릅니다.

⑴ 참조제약(외부키)의 선언

참조제약은 FOREIGN KEY로 선언합니다. 이는 앞에서 설명했던 CREATE TABLE부분에서 다루었습니다. 참조제약(외부키)은 'FOREIGN KEY (열명) REFERENCES 참조대상테이블(열명)'과 같이 선언합니다.

여러 열을 참조하고자 하는 경우에는 'FOREIGN KEY (열명1, 열명2) REFERENCES 참조대상테이블(열명1, 열명2)'와 같이 쉼표(,)로 구분하여 지정합니다. 이 경우, 값의 조합이 참조대상에 존재하지 않으면 등록할 수 없습니다.

주키(PRIMARY KEY)와 달리, 외부키는 하나의 테이블에 몇 개든 선언할 수 있습니다. 예를 들어, 샘플 데이터의 courses테이블은 students 테이블과 course_master테이블을 참조하고 있습니다.

⑵ 키값을 변경하면 어떻게 되는가?

키의 값을 변경하고 싶은 경우에는 어떻게 해야 할까요?

학원은 예전에는 캠퍼스별로 학생번호를 정해졌는데, 3번째 캠퍼스가 생겼을 때 전 캠퍼스에서 사용할 수 있는 연속번호로 변경했습니다. 실무적으로는 신구를 대응하는 마스터를 별도로 만드는 것이 필요할 수도 있지만, 데이터베이스 상에서는 값의 변경이 가능합니다. 단, 다른 곳에서 참조되고 있는 경우에는 어떻게 처리할지를 미리 결정해 두어야 합니다.

미리 결정해두었나?

예를들어, 학생번호를 변경하면 다른 데이터도 모두 연동하여 변경해ㅑ 하는지, 아니면 다른 데이터가 이미 등록되어 있는 경우에는 변경해서는 안되는지 결정해야 합니다. 삭제의 경우도 마찬가지입니다. 삭제해도 되는지를 미리 정해두어야 합니다. 이런 것도 테이블 설계에서 신경써야 합니다. 키는 사물을 식별하기 위한 항목으로 원칙적으로느 변경되지 않아야 합니다. 하지만 현실에서는 주키로 사용되는 값(참조되는 값)이 변경되는 경우도 있을 수 있습니다. 표준 SQL에서는 참조되는 값이 변경되었을 때의 동작을 설정할 수 있도록 되어 있습니다. 설정은 아래 표에서 제시된 5가지 경우로 부모키(참조되는 쪽의 키)를 삭제할 때의 동작(ON DELETE)과 변경할 때의 동작(ON UPDATE)을 각각 지정합니다.

피참조값이 변경되었을 때의 동작

부모키의 삭제나 변경으로 인해 다른 테이블의 참조제약이나 NOT NULL 제약에 위배되는 데이터가 발생하는 경우에는 오류가 발생합니다. 예를 들어, exams테이블의 student_id가 students테이블의 student_id를 참조하고 있고, 'ON DELETE SET NULL'이 설정되어 있더라도, exams테이블의 student_id가 PRIMARY KEY의 일부이므로 NULL값을 넣을 수 없어 오류가 발생합니다. 따라서, 부모키의 삭제는 불가능합니다.

제약은 항상 보호

실제로는 CASCADE와 RESTRICT 2가지 중 하나를 선택하게 됩니다. 아무것도 지정하지 않으면 'NO ACTION'이 되며, 동작결과는 RESTRICT와 동일합니다. 즉 참조되는 값은 변경이나 삭제가 불가능합니다. 예를 들어, 그만둔 학생의 데이터는 모두 삭제해야 한다면, 'ON DELETE CASCADE'를 사용하고 다른 기록있는 한 삭제하면 안된다면 'ON DELETE RESTRICT'를 사용하면 됩니다. 최종적으로 삭제되는 경우에도 RESTRICT로 설정하는 편이 안전할 것 같습니다.

기본값도 'NO ACTION'이기 때문에 (이는 RESTRICT가 나중에 추가된 규정이기 때문) 참조되는 값은 변경도 삭제도 하지 않는 것이 기본동작입니다. 다른 테이블에서 아직 사용되지 않는 경우, 예를 들어 입학한지 얼마 안된 학생의 경우에는 삭제나 변경이 가능하겠죠? 이는 전적으로 참조제약을 지키기 위한 설정일뿐입니다.