[8i] 데이터 무결성 관리(OLN)

/********************************************************************************************
-- Title : [8i] 데이터 무결성 관리(OLN)
-- Reference : OLN
-- Key word : data constraint
********************************************************************************************/

/****************************************************************************************
-- 제약 정보 얻기
****************************************************************************************/

  
SQL> SELECT t.owner, t.trigger_name, t.trigger_type, t.triggering_event,
  2         t.status, c.column_name, o.status as "VALIDITY"
  3  FROM dba_triggers t, dba_trigger_cols c, dba_objects o
  4  WHERE t.owner = c.trigger_owner (+)
  5    AND t.trigger_name = c.trigger_name (+)
  6    AND t.owner = o.owner
  7    AND t.trigger_name = o.object_name
  8    AND o.object_type = 'TRIGGER'
  9    AND t.table_owner = 'SCOTT'
 10    AND t.table_name = 'EMP';
 
OWNER RIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT           STATUS  COLUMN_NAME VALIDITY
----- ----------- ------------ -------------------------- ------- ----------- ---------
SCOTT EMP_CONV_LN BEFORE       STATEMENT INSERT OR UPDATE ENABLED LAST_NAME   VALID
SCOTT T1          BEFORE       STATEMENT UPDATE           ENABLED EMPNO       INVALID

2 rows selected.

/****************************************************************************************
-- 부모 테이블에 관련된 DDL
****************************************************************************************/
-- 부모 테이블 삭제 전 외래키 우선 삭제
SQL> DROP TABLE table_name CASCADE CONSTRAINTS;

-- 부모 테이블을 포함하고 있는 테이블스페이스를 삭제하기 전에 외래 키가 먼저 삭제.
SQL> DROP TABLESPACE tablespace_name
  2  INCLUDING CONTENTS CASCADE CONSTRAINTS;
 

/****************************************************************************************
-- 테이블 생성 시 제약 조건
****************************************************************************************/
-- In-Line 제약 조건
SQL> CREATE TABLE scott.employees
  2  ( empno NUMBER(4)
  3          CONSTRAINT emp_pk PRIMARY KEY
  4          DEFERRABLE
  5          USING INDEX
  6                STORAGE(INITIAL 100K
  7                        NEXT 100K)
  8                TABLESPACE indx
  9  , last_name VARCHAR2(30)
 10          CONSTRAINT emp_ln_nn NOT NULL
 11  , deptno NUMBER(2)
 12  )
 13  TABLESPACE user_data;

Table created.

-- Out-Of-Line 제약 조건
SQL> ALTER TABLE scott.employees
  2  ADD
  3  ( CONSTRAINT emp_dept_fk FOREIGN KEY(deptno)
  4    REFERENCES scott.dept(deptno)
  5    DEFERRABLE INITIALLY DEFERRED
  6  );

Table altered.

/****************************************************************************************
-- 제약 조건 DISABLE
****************************************************************************************/
SQL> ALTER TABLE scott.dept
  2  DISABLE CONSTRAINT pk_dept CASCADE;

Table altered.

/****************************************************************************************
-- 제약 조건 ENABLE
****************************************************************************************/
-- ENABLE NOVAILDATE
SQL> ALTER TABLE scott.dept
  2  ENABLE NOVALIDATE CONSTRAINT pk_dept;

Table altered.

-- ENABLE VAILDATE
SQL> ALTER TABLE scott.dept
  2  ENABLE VALIDATE CONSTRAINT pk_dept;

Table altered.

/****************************************************************************************
-- EXCEPTIONS 테이블 사용 절차
****************************************************************************************/
-- 1. exceptions 테이블 생성
SQL> @?/rdbms/admin/utlexcpt

Table created.

SQL> DESCRIBE exceptions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT                                         VARCHAR2(30)

-- 2. EXCEPTIONS 절을 포함하는 ALTER TABLE 명령을 실행
--    기존 제약이 disable이면서 제약에 위배되는 데이터가 있다는 전제하
SQL> ALTER TABLE scott.emp
  2  ENABLE VALIDATE CONSTRAINT fk_deptno
  3  EXCEPTIONS INTO system.exceptions;
enable validate constraint fk_deptno
                           *
ERROR at line 2:
ORA-02298: cannot validate (SCOTT.FK_DEPTNO) - parent keys not found

-- 3. EXCEPTIONS 테이블에 서브쿼리를 사용하여 부적합한 데이터를 추출
SQL> SELECT rowid, empno, ename, hiredate, deptno
  2  FROM scott.emp
  3  WHERE rowid in (SELECT row_id
  4                  FROM exceptions
  5                 )
  6  FOR UPDATE;

ROWID                   EMPNO ENAME      HIREDATE      DEPTNO
------------------ ---------- ---------- --------- ----------
AAAAx/AABAAABsCAAP       9999 mapbak     09-SEP-99         99

-- 4. 오류 데이터 update
SQL> UPDATE scott.emp
  2  SET deptno = 10
  3  WHERE rowid = 'AAAAx/AABAAABsCAAP';

1 row updated.

SQL> SELECT *
  2  FROM scott.emp
  3  WHERE rowid = 'AAAAx/AABAAABsCAAP';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------
      9999 mapbak     DBA             9999 09-SEP-99       9999         99        10
     
-- 5. exceptions 테이블 truncate
SQL> TRUNCATE TABLE system.exceptions;

Table truncated.

-- 6. 다시 확인
SQL> ALTER TABLE scott.emp
  2  ENABLE VALIDATE CONSTRAINT fk_deptno
  3  EXCEPTIONS INTO system.exceptions;

Table altered.

/****************************************************************************************
-- 트리거 DISABLE, ENABLE 하기
****************************************************************************************/
-- 트리거 DISABLE
SQL> ALTER TRIGGER scott.sum_trg
  2  DISABLE;

Trigger altered.

-- 트리거 ENABLE
SQL> ALTER TRIGGER scott.sum_trg
  2  ENABLE;

Trigger altered.

-- 모든 트리거 DISABLE
SQL> ALTER TRIGGER scott.dept
  2  DISABLE ALL TRIGGERS;
DISABLE ALL TRIGGERS
        *
ERROR at line 2:
ORA-00933: SQL command not properly ended -- 왜 에러가 날까?

-- 모든 트리거 ENABLE
SQL> ALTER TRIGGER scott.dept
  2  ENABLE ALL TRIGGERS;
DISABLE ALL TRIGGERS
        *
ERROR at line 2:
ORA-00933: SQL command not properly ended -- 왜 안될까?

/****************************************************************************************
-- 제약 조건 삭제
****************************************************************************************/
-- 제약 조건 삭제
SQL> ALTER TABLE scott.emp
  2  DROP CONSTRAINT fk_deptno;

Table altered.

-- 테이블 삭제와 그 테이블을 참조하는 외래 키 동시 삭제
SQL> DROP TABLE employees
  2  CASCADE CONSTRAINT;

Table dropped.

/****************************************************************************************
-- 트리거 삭제
****************************************************************************************/
SQL>DROP TRIGGER scott.sum_trg;

Trigger dropped.