Day 5 - Null 속성과 본질식별자 vs 인조식별자

NULL이란

모델링 단계에서 속성마다 "값이 비어도 되는가"를 정한다. 이것이 Null 허용 여부다.

• 주식별자는 존재성 때문에 NULL을 허용하지 않는다(Day 1).
• 일반 속성은 업무에 따라 다르다. EMP의 COMM(커미션)은 영업직에게만 있어 다른 직무는 NULL이다.
• NULL은 "값이 0"이라는 뜻이 아니다. 값이 없다는 상태다. 0은 숫자 값이고, 공백 ' '은 문자 값이다.

• Oracle은 길이 0인 문자열 ''을 NULL로 취급한다. SQL Server는 ''과 NULL을 구분한다. 2과목 DBMS 차이에서 다시 나온다.

NULL과 산술 연산

NULL에 무엇을 더하거나 곱해도 결과는 NULL이다. 30번 부서로 확인했다.

SELECT ENAME, SAL, COMM, SAL + COMM AS TOTAL, SAL + NVL(COMM, 0) AS TOTAL2
FROM EMP WHERE DEPTNO = 30 ORDER BY EMPNO;

ENAME             SAL       COMM      TOTAL     TOTAL2
---------- ---------- ---------- ---------- ----------
ALLEN            1600        300       1900       1900
WARD             1250        500       1750       1750
MARTIN           1250       1400       2650       2650
BLAKE            2850                             2850
TURNER           1500          0       1500       1500
JAMES             950                              950

• BLAKE와 JAMES는 COMM이 NULL이라 SAL + COMM도 NULL이 됐다.
• TURNER는 COMM이 0이라 정상적으로 1500이 나왔다. 0과 NULL의 차이가 결과에 그대로 드러난다.
• NULL을 0으로 바꿔 계산하려면 NVL(COMM, 0)을 쓴다. NULL 관련 함수는 3주차에서 다룬다.

NULL과 비교 연산

NULL은 어떤 값과도 같거나 다르다고 말할 수 없다. 비교 결과는 UNKNOWN이고, WHERE 절은 참인 행만 남기므로 UNKNOWN 행은 빠진다.

SELECT COUNT(*) FROM EMP WHERE COMM = NULL;
SELECT COUNT(*) FROM EMP WHERE COMM IS NULL;

  COUNT(*)
----------
         0
 
  COUNT(*)
----------
        10

• COMM = NULL은 오류 없이 실행되지만 결과는 0건이다. 모든 행에서 비교가 UNKNOWN이기 때문이다.
• NULL인 사원 10명을 찾으려면 IS NULL을 써야 한다.

<>도 마찬가지다. "커미션이 0이 아닌 사원"을 찾으면 NULL인 사원은 빠진다.

SELECT ENAME, COMM FROM EMP WHERE COMM <> 0 ORDER BY EMPNO;

ENAME            COMM
---------- ----------
ALLEN             300
WARD              500
MARTIN           1400

⚠️ 함정: WHERE 칼럼 = NULL은 문법 오류가 아니다. 실행은 되고 결과가 0건이다. "오류가 발생한다"는 보기와 "NULL인 행이 조회된다"는 보기 모두 틀린다.

NULL과 집계 함수

집계 함수(COUNT, SUM, AVG, MAX, MIN)는 NULL을 계산에서 제외한다.

SELECT COUNT(*) AS CNT, COUNT(COMM) AS COMM_CNT, SUM(COMM) AS COMM_SUM,
       AVG(COMM) AS COMM_AVG, AVG(NVL(COMM, 0)) AS COMM_AVG2
FROM EMP;

       CNT   COMM_CNT   COMM_SUM   COMM_AVG  COMM_AVG2
---------- ---------- ---------- ---------- ----------
        14          4       2200        550 157.142857

• AVG의 분모는 NULL이 아닌 행 수다. "사원 전체의 평균 커미션"이 목적이면 NVL로 바꾼 뒤 평균을 내야 한다.
• 대상 값이 모두 NULL이면 SUM·AVG의 결과는 NULL이다. 0이 아니다.

부서별로 묶으면 마지막 규칙이 보인다.

SELECT DEPTNO, COUNT(*), COUNT(COMM), SUM(COMM), AVG(COMM)
FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

    DEPTNO   COUNT(*) COUNT(COMM)  SUM(COMM)  AVG(COMM)
---------- ---------- ----------- ---------- ----------
        10          3           0
        20          5           0
        30          6           4       2200        550

• 10번·20번 부서는 COMM이 전부 NULL이다. COUNT(COMM)은 0이지만 SUM·AVG는 NULL로 비어 있다.
• 모델링에서 이런 칼럼에 기본값 0을 둘지 정하는 이유다. 기본값을 두면 "정해지지 않음"과 "0원"을 구분할 수 없게 되니 업무 의미를 보고 고른다.

정렬에서의 NULL

SELECT ENAME, COMM FROM EMP WHERE DEPTNO = 30 ORDER BY COMM, ENAME;

ENAME            COMM
---------- ----------
TURNER              0
ALLEN             300
WARD              500
MARTIN           1400
BLAKE
JAMES

• Oracle은 NULL을 가장 큰 값처럼 취급해 오름차순에서 맨 뒤에 둔다. SQL Server는 가장 작은 값처럼 취급해 맨 앞에 둔다.

본질식별자와 인조식별자

Day 1의 분류 중 "대체 여부"를 깊게 본다.

인조식별자의 장점

• 복합 식별자 여러 칼럼을 칼럼 하나로 대신한다. 키가 단순해진다.
• 자식 엔터티로 상속되는 칼럼이 줄어 조인 조건이 짧아진다.
• 본질식별자 값이 바뀌어도 인조식별자는 그대로라 참조가 안정적이다.

인조식별자의 단점 — 중복 데이터

일련번호를 PK로 쓰고 (ORDER_NO, PROD_NO)에는 아무 제약도 두지 않은 테이블을 만들어 같은 행을 두 번 넣었다.

CREATE TABLE T_ITEM_SURR (ITEM_ID NUMBER(6) PRIMARY KEY, ORDER_NO NUMBER(4), PROD_NO VARCHAR2(4), QTY NUMBER(3));
INSERT INTO T_ITEM_SURR VALUES (1, 1001, 'P1', 2);
INSERT INTO T_ITEM_SURR VALUES (2, 1001, 'P1', 2);
SELECT * FROM T_ITEM_SURR ORDER BY ITEM_ID;

   ITEM_ID   ORDER_NO PROD        QTY
---------- ---------- ---- ----------
         1       1001 P1            2
         2       1001 P1            2

• 같은 주문의 같은 상품이 두 행으로 들어갔다. PK ITEM_ID가 다르므로 DBMS는 막지 않는다.
• 같은 데이터를 본질식별자 (ORDER_NO, PROD_NO)를 PK로 둔 테이블에 넣으면 두 번째 INSERT가 거부된다.

CREATE TABLE T_ITEM_NAT (ORDER_NO NUMBER(4), PROD_NO VARCHAR2(4), QTY NUMBER(3),
  CONSTRAINT T_ITEM_NAT_PK PRIMARY KEY (ORDER_NO, PROD_NO));
INSERT INTO T_ITEM_NAT VALUES (1001, 'P1', 2);
INSERT INTO T_ITEM_NAT VALUES (1001, 'P1', 2);

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T_ITEM_NAT_PK) violated on table SCOTT.T_ITEM_NAT columns (ORDER_NO, PROD_NO)

실습 후 두 테이블은 DROP TABLE … PURGE로 지웠다.

인조식별자의 단점 — 불필요한 인덱스

• 중복을 막으려면 인조식별자 PK와 별도로 (ORDER_NO, PROD_NO)에 UNIQUE 인덱스를 하나 더 만들어야 한다.
• PK 인덱스 하나로 충분했을 테이블에 인덱스가 둘이 된다. 저장 공간이 늘고 입력·수정·삭제 때마다 인덱스 두 개를 갱신해야 한다.
• 실제 조회는 대개 주문번호로 하므로 ITEM_ID PK 인덱스는 잘 쓰이지 않는 인덱스가 될 수 있다.

• 그래서 교재는 본질식별자가 복잡하지 않다면 본질식별자를 쓰고, 속성 수가 지나치게 많을 때 인조식별자를 검토하라고 정리한다.

다음 주부터는 2과목 SQL로 넘어가 SELECT 문과 함수를 다룬다.

📖 용어

• NULL : 값이 정해지지 않았거나 모르는 상태. 0이나 공백 문자와 다르다.
• UNKNOWN : NULL을 =·<>로 비교했을 때의 결과. WHERE 절에서는 거짓처럼 걸러진다.
• IS NULL : NULL 여부를 확인하는 비교 연산자. 부정형은 IS NOT NULL이다.
• NVL : NULL을 지정한 값으로 바꾸는 Oracle 함수.
• Null 허용 여부 : 속성에 값이 비어도 되는지를 모델링 단계에서 정한 것.
• 본질식별자 : 업무에 의해 만들어진 식별자. 원조식별자라고도 한다.
• 인조식별자 : 업무에는 없지만 키를 단순하게 하려고 만든 식별자. 일련번호가 대표적이다.
• UNIQUE 인덱스 : 칼럼 값의 중복을 막는 인덱스. 인조식별자를 쓸 때 본질 칼럼에 추가로 필요하다.