CHECK 제약조건 적용 사례

CHECK 제약조건 적용 사례를 살펴보자.

 

테스트를 위해 아래와 같이 테이블을 생성하자. 테이블에는 기간별 데이터가 저장된다. 주별은 주의 일요일, 월별, 분기별, 연별은 각각의 말일자로 dt 값을 저장해야 한다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    cd VARCHAR2(1) -- 코드
  , tm VARCHAR2(1) -- 기간 (D, W, M, Q, Y)
  , dt VARCHAR2(8) -- 일자
  , vl NUMBER      -- 값
  , CONSTRAINT t1_pk PRIMARY KEY (cd, tm, dt));

아래의 CHECK 제약조건을 추가하자. 

-- 2
ALTER TABLE t1 ADD CONSTRAINT t1_cd CHECK (dt =
    CASE tm
        WHEN 'D' THEN TO_CHAR (TO_DATE (dt, 'YYYYMMDD'), 'YYYYMMDD')
        WHEN 'W' THEN TO_CHAR (NEXT_DAY (TO_DATE (dt, 'YYYYMMDD') - 1, 1), 'YYYYMMDD')
        WHEN 'M' THEN TO_CHAR (LAST_DAY (TO_DATE (dt, 'YYYYMMDD')), 'YYYYMMDD')
        WHEN 'Q' THEN TO_CHAR (TRUNC (ADD_MONTHS (TO_DATE (dt, 'YYYYMMDD'),  3), 'Q') - 1, 'YYYYMMDD')
        WHEN 'Y' THEN TO_CHAR (TRUNC (ADD_MONTHS (TO_DATE (dt, 'YYYYMMDD'), 12), 'Y') - 1, 'YYYYMMDD')
    END);

 

아래 쿼리를 실행하면 2행은 "ORA-01847: 달의 날짜는 1에서 말일 사이어야 합니다", 3~6행은 "ORA-02290: 체크 제약조건(T1_C1)이 위배되었습니다" 에러가 발생한다.

-- 3
INSERT INTO t1 VALUES ('A', 'D', '20500500', 1); -- ORA-01847
INSERT INTO t1 VALUES ('A', 'W', '20500503', 2); -- ORA-02290
INSERT INTO t1 VALUES ('A', 'M', '20500530', 3); -- ORA-02290
INSERT INTO t1 VALUES ('A', 'Q', '20500531', 4); -- ORA-02290
INSERT INTO t1 VALUES ('A', 'Y', '20500531', 5); -- ORA-02290
INSERT INTO t1 VALUES ('A', 'D', '20500501', 1);
INSERT INTO t1 VALUES ('A', 'W', '20500504', 2);
INSERT INTO t1 VALUES ('A', 'M', '20500531', 3);
INSERT INTO t1 VALUES ('A', 'Q', '20500630', 4);
INSERT INTO t1 VALUES ('A', 'Y', '20501231', 5);
COMMIT;

 

아래는 t1 테이블을 조회한 결과다.

-- 4
SELECT * FROM t1;

C1  C2 C3       C4
--  -- -------- --
B   D  20500501  1
B   W  20500504  2
B   M  20500531  3
B   Q  20500630  4
B   Y  20501231  5

5 rows selected.

 

10.1 이전 버전은 조회시 CHECK 제약조건을 필터 조건으로 사용하기 때문에 쿼리의 성능이 저하될 수 있다. 아래처럼 세션 설정하면 필터링을 회피할 수 있다. 

-- 5
ALTER SESSION SET EVENTS '10195 trace name context forever, level 1';