[DB설계] 16. 키(식별자)

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

by AI개발자

Mar 1. 2025

이번에는 키(Key)에 대해 생각해 봅시다. 예를 들어, 학생마스터의 학생은 '학생번호'로 식별할 수 있는가?

학생번호만 알면 누구인지 알 수 있습니다. 이런 값을 '키(식별자)'라고 합니다.

다시 설명하면, 데이터를 식별하는데 사용할 수 있는 항목을 식별자(identifier) 또는 '키(Key)'라고 부릅니다. 예를 들어, 학생을 학생번호로 식별하고 있다면 학생번호가 식별자가 되는 것입니다.

⑴ 기본키와 후보키

키는 한개만 있는 것일까?

학생의 경우, 이름으로도 식별할 수 있을까?

만약 동명이인이 없다면 이름도 키로 사용할 수 있습니다. 하지만, 앞으로도 동명이인이 없을 것이라고 단정할 수 없으므로 키로는 사용하기 어렵습니다. 그러나, 예를 들어, 자사 상품을 관리하는 상품마스터가 있고, 동일한 상품명을 사용하지 않기로 정해졌다면 상품명이 키가 될 수 있습니다. 즉 어려개의 키도 가능합니다.

키로 사용할 수 있는 열들은 '후보키(candidate key)'라고 불리며, 후보키 주엥서 주로 사용하는 것을 주키(primary key) 또는 1차키(PK)'라고 하고, 나머지 키들은 2차키(secondary key) 또는 대리키(alternate key)라고 부릅니다. 즉, 키로 사용할 수 있는 항목들은 주키가 될 가능성이 있다는 의미에서 후보키라고도 합니다.

예를 들어, 상품을 식별할 때 상품코드와 상품명 2가지 모두 사용할 수 있다면, 두 항목 모두 중복되지 않으므로 상품코드만 알면 어떤 상품인지, 상품명만 알면 어떤 상품인지 알 수 있는 상태가 됩니다. 이 경우 후보키는 상품코드와 상품명입니다. 반면에 학생의 경우, 학생번호와 일흠으로 특정할 수 있을 것 같지만, 이름에는 중복이 있을 수 있으므로 후보키는 학생번호 하나만 됩니다. 따라서 학생의 경우 학생번호가 주키가 됩니다.

⑵ 기본키 및 기타 후보키 선언

주키는 PRIMARY KEY로 선언합니다. 앞서 설명했던 CREATE TABLE에서도 나왔었습니다. 후보키 전용으로 별도로 선언하는 방법은 없고, 단지 중복불가와 NULL 금지를 나타내면 됩니다. 중복이 불가능하면 데이터를 식별하는데 사용할 수 있습니다. 즉, 중복불가와 NULL금지가 필요합니다.

주키는 테이블 생성시에 PRIMARY KEY로 선언합니다. 다음은 '상품코드'와 '상품명'을 가진 '상품마스터' 테이블의 정의 예제입니다. 상품코드와 상품명 모두 상품을 식별할 수 있는 후보키이지만, 여기서는 상품코드를 주키로 사용합니다. 참고로 PRIMARY KEY는 항상 NULL이 금지되므로 NOT NULL지정은 생략할 수 있습니다.

그외의 후보키는 중복불가를 의미하는 UNIQUE와 NULL금지를 의미하는 NOT NULL으로 선언합니다. UNIQUE만 지정한 경우에는 NULL값이 등록될 수 있으므로, 'NULL이외의 값은 중복되지 않는다'라는 의미가 됩니다. NULL은 'NULL인지 아닌지'만 판별할 수 있을 뿐, 값의 일치 여부 판단 대상이 아니기 때문에 UNIQUE가 지정되어 있어도 NULL데이터는 몇 건이든 등록될 수 있습니다. 따라서, UNIQUE만으로는 데이터를 식별하는 '식별자'로 사용할 수 없습니다.

아래와 같이, PRIMARY KEY나 UNIQUE를 열정의와 별도로 선언할 수도 있습니다. 나중에 설명하겠지만, 여러 열을 조합하여 지정하고자 하는 경우에는 이 형식으로 선언합니다. 별도로 선언시, CONSTRAINT를 사용하여 제약명을 지정할수도 있습니다.

후보키는 데이터를 식별하는데 사용할 수 있으므로, 외부키에서 참조될 수 있습니다. 반대로 말하면, 외부키에서 참조하려는 열은 후보키여야 하며, 즉 PRIMARY KEY 또는 UNIQUE와 NOT NULL이 지정되어 있어야 합니다.

⑶ 복합키 선언

우리 학생번호는 지금은 모든 캠퍼스에서 공통으로 사용되지만, 예전에는 캠퍼스별로 부여되었던 적이 있습니다. 이런 경우 어떻게 처리해야 할까요?

예를 들어, '캠퍼스 코드 + 학생번호'와 같이 여러 개의 열을 결합하여 키로 사용할 수 있습니다. 이를 '복합키(composite key)'라고 합니다. 키는 반드시 단독열일 필요는 없으며, 여러 열을 조합해서 키로 사용할 수 있습니다. 이런 키를 복합키라고 하며, CREATE TABLE로 복합키를 설정할 때는 열명 뒤에 PRIMARY KEY를 지정하는 방식은 사용할 수 없으므로 PRIMARY KEY를 별도로 선언합니다. 다음 샘플 데이터의 exams테이블정의 예제입니다. 여기서는 student_id, exam_no, subject라는 3개의 열을 결합하여 주키로 지정하고 있습니다.

DBMS상에서 관리하기 쉽게 하기 위해서, 또는 복합키를 주키로 사용할 수 없는 DBMS의 경우, 복합키와는 별도로 DBMS가 자동 생성한 연속번호를 저장하는 열을 만들어 그것을 주키로 사용하는 경우가 있습니다. 이러한 경우에는 복합키를 후보키(candidate key)로 사용하여 즉 UNIQUE와 NOT NULL을 지정해 둡니다.

연속번호의 정의는 SERIAL로 수행합니다. SERIAL은 표준SQL에는 없지만, MySQL/MariaDB와 PostgreSQL에서 공통으로 사용할 수 있습니다. 참고로 표준 SQL에서는 SQL:2003에서 자동번호 생성기능을 가진 식별열(Identity columns)이 규정되었습니다.

참고로 상품코드처럼 원래 존재해서 사용되던 값을 자연키(natural key)라고 부르는 반면, DBMS에서 자동으로 생성하는 키는 대리키(surrogate key) 또는 대체키라고 합니다. 복합키는 익숙해지기까지 약간 시간이 필요할 것입니다. 즉, 데이터를 특정하기 위해서는 ID가 있어야 한다고 생각하면 됩니다. 일상업무에서도 복합키와 비슷한 것을 사용했을 텐데, 어쩌면 여러 조건으로 좁혀서 하나를 특정해내는 정도의 감각이었을지도 모르겠습니다. '우연히 특정할 수 있는 조합'이라는 것도 있고, 이름과 마찬가지로 원래는 특정할 수 없는 값이라도 동명이인이 없으면 실용적으로는 특정할 수 있었습니다. 하지만 데이터베이스는 설계할 때는 이런 조합이 중복되어도 되는지 여부를 검토하는 것이 중요한 작업입니다. 키와 관련된 작업이기 때문입니다.