본질식별자와 인조식별자

본질식별자(natural key)는 업무가 만든 식별자이고, 인조식별자(surrogate key)는 일련번호처럼 인위적으로 만든 식별자입니다. 인조식별자는 식별자를 짧게 해 주지만, 중복 데이터를 막지 못하고 인덱스를 하나 더 들게 합니다.

본질식별자가 여러 속성이면 자식 엔터티에 물려줄 때마다 속성이 쌓여 조인 조건이 길어집니다. 그래서 번호 하나로 된 인조식별자를 쓰고 싶어지지만, 인조식별자에는 대가가 있어서 언제 쓰고 무엇을 함께 걸어야 하는지 알아야 합니다. 그림의 주문상품(ORDER_ITEM)과 반품상품은 이 레슨의 예제에서 만드는 테이블입니다.

1본질식별자와 물려받기

주문상품은 주문서에 적히는 주문번호 ORDNO와 상품번호 PRODNO를 합쳐야 한 줄이 가려집니다. 둘 다 업무에서 쓰는 값이라 본질식별자입니다. 이 두 열로 기본키를 만들면 인덱스가 하나 생깁니다.

식별 관계로 물려받는 자식은 이 두 속성을 모두 자기 주식별자에 넣습니다. 반품상품은 여기에 자기 속성 반품순번을 더해 주식별자가 세 속성이 되고, 아래로 물려줄수록 더 길어집니다.

2인조식별자와 그 단점

인조식별자는 업무에 없던 번호를 시스템이 행을 구분하려고만 매긴 것입니다. ITEM_ID를 1, 2, …로 매겨 주식별자로 삼으면 주식별자가 속성 하나로 줄고, 반품상품도 ITEM_ID와 반품순번 두 속성이면 됩니다. 번호라고 다 인조식별자는 아닙니다. 사번 EMPNO는 회사가 사원에게 붙여 업무에서 쓰는 번호라 본질식별자입니다.

대신 단점이 둘 생깁니다. 첫째, 같은 주문번호·상품번호 행이 다시 들어와도 막지 못합니다. 새 행은 새 ITEM_ID를 받으므로 기본키에 걸리지 않습니다. 둘째, 이 중복을 막으려면 ORDNO·PRODNO에 UNIQUE 제약을 따로 걸어야 합니다. UNIQUE도 인덱스를 만들어서 인덱스가 둘이 되고, 저장 공간과 입력·수정 때 할 일이 늘어납니다.

3어느 쪽을 고르나

본질식별자를 먼저 찾고, 속성이 적고 값이 바뀌지 않으면 그대로 씁니다. 너무 길거나 복잡할 때만 인조식별자를 두고, 그때도 본질식별자 속성에는 UNIQUE를 겁니다.

예제

ITEM_ID를 인조식별자로 둔 주문상품 테이블에 같은 주문상품 (1, 101)을 두 번 넣습니다. GENERATED ALWAYS AS IDENTITY⁠는 행을 넣을 때마다 1, 2, 3으로 번호를 매기는 IDENTITY 열을 만듭니다. 번호는 시퀀스(SEQUENCE — 번호를 차례로 하나씩 내어 주는 객체)로도 매길 수 있습니다.

CREATE TABLE ORDER_ITEM (
  ITEM_ID NUMBER
    GENERATED ALWAYS
    AS IDENTITY PRIMARY KEY,
  ORDNO NUMBER NOT NULL,
  PRODNO NUMBER NOT NULL,
  QTY NUMBER NOT NULL
);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 101, 2);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 102, 1);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 101, 2);
SELECT ITEM_ID,
  ORDNO,
  PRODNO,
  QTY
FROM ORDER_ITEM
ORDER BY ITEM_ID;

ITEM_ID 1과 3은 같은 주문상품이지만, 기본키 값이 달라 DBMS는 중복으로 보지 않습니다.

본질식별자 속성에 UNIQUE 제약 ORDER_ITEM_UK를 더하면 같은 행이 막힙니다.

CREATE TABLE ORDER_ITEM (
  ITEM_ID NUMBER
    GENERATED ALWAYS
    AS IDENTITY,
  ORDNO NUMBER NOT NULL,
  PRODNO NUMBER NOT NULL,
  QTY NUMBER NOT NULL,
  CONSTRAINT ORDER_ITEM_PK
    PRIMARY KEY (ITEM_ID),
  CONSTRAINT ORDER_ITEM_UK
    UNIQUE (ORDNO, PRODNO)
);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 101, 2);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 102, 1);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 101, 2);

세 번째 INSERT는 이미 있는 주문번호·상품번호라서 ORDER_ITEM_UK에 막힙니다(ORA-00001). 막힌 INSERT도 ITEM_ID 3을 쓰므로 번호가 비어, 이다음에 넣는 행은 4를 받습니다.

내가 만든 인덱스를 보여 주는 USER_INDEXES로 보면 이 테이블의 인덱스는 둘입니다.

CREATE TABLE ORDER_ITEM (
  ITEM_ID NUMBER
    GENERATED ALWAYS
    AS IDENTITY,
  ORDNO NUMBER NOT NULL,
  PRODNO NUMBER NOT NULL,
  QTY NUMBER NOT NULL,
  CONSTRAINT ORDER_ITEM_PK
    PRIMARY KEY (ITEM_ID),
  CONSTRAINT ORDER_ITEM_UK
    UNIQUE (ORDNO, PRODNO)
);
SELECT INDEX_NAME,
  UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME
    = 'ORDER_ITEM'
ORDER BY INDEX_NAME;

시험 포인트

• 본질식별자·인조식별자는 식별자 분류의 '대체 여부'(업무가 만들었나)로 나눈 짝입니다.
• 자료에 따라 본질식별자를 원조식별자, 인조식별자를 대리식별자라고도 부릅니다. 어느 이름으로 나와도 같은 것입니다.
• 사번처럼 업무에서 쓰는 번호는 본질식별자입니다. 번호라서 인조식별자로 고른 보기는 함정입니다.
• 인조식별자는 주식별자를 속성 하나로 줄여, 자식에게 물려주는 속성과 조인 조건을 줄입니다.
• 대표 단점은 둘입니다. 같은 업무 데이터의 중복을 막지 못하고, 막으려면 UNIQUE 인덱스가 하나 더 듭니다.
• 인조식별자를 주식별자로 쓰면 본질식별자 속성에 UNIQUE를 겁니다.

확인 문제

1. 수강의 주식별자는 '학번 + 과목코드 + 학기'입니다. 자식 출석은 이것을 식별 관계로 물려받고 자기 속성 수업일을 더합니다.

• (가) 출석의 주식별자는 속성 몇 개일까요?
• (나) 수강에 행마다 1씩 늘어나는 수강ID를 주식별자로 두면, 출석의 주식별자는 몇 개가 될까요?
• (다) 학번과 수강ID는 각각 본질식별자와 인조식별자 가운데 무엇일까요?

2. 예제의 UNIQUE를 ORDNO 하나에만 걸었습니다. 세 INSERT 뒤 남는 행은 무엇이고, 이 UNIQUE는 무엇이 잘못됐을까요?

CREATE TABLE ORDER_ITEM (
  ITEM_ID NUMBER
    GENERATED ALWAYS
    AS IDENTITY,
  ORDNO NUMBER NOT NULL,
  PRODNO NUMBER NOT NULL,
  QTY NUMBER NOT NULL,
  CONSTRAINT ORDER_ITEM_PK
    PRIMARY KEY (ITEM_ID),
  CONSTRAINT ORDER_ITEM_UK
    UNIQUE (ORDNO)
);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 101, 2);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (1, 102, 1);
INSERT INTO ORDER_ITEM
  (ORDNO, PRODNO, QTY)
VALUES (2, 101, 3);
SELECT ORDNO,
  PRODNO,
  QTY
FROM ORDER_ITEM
ORDER BY ORDNO, PRODNO;

3. 회원마다 다른 이메일을 회원 엔터티의 주식별자로 쓰려는데, 회원은 이메일을 바꿀 수 있습니다. 주식별자는 무엇으로 정하고 이메일에는 무엇을 걸어야 할까요?