[10g] 지연된 제약조건 설정 및 제약조건 비활성화/활성화

/********************************************************************************************
-- Title : [10g] 지연된 제약조건 설정 및 제약조건 비활성화/활성화
-- Reference : self training 오라클 관리 -문성기 저-
-- Key word : deferrable 지연 deferred disable enable
********************************************************************************************/

/*******************************************
-- 지연된 제약조건 설정
*******************************************/
/*
-- 테이블 생성
*/
-- drop table scott.st purge;
-- drop table scott.ma purge;
create table scott.ma
( mno char(2)
, mname varchar2(20)
, constraint ma_mno_pk primary key (mno) deferrable
);

create table scott.st
( sno char(6)
, sname varchar2(20)
, mno char(2)
, constraint st_sno_pk primary key (sno) deferrable initially deferred
, constraint st_mno_fk foreign key (mno) references scott.ma (mno)
);

-- 제약 확인
select table_name, constraint_name, constraint_type, deferrable, deferred, status
from dba_constraints
where table_name in ('MA','ST')
order by 1;

/*
-- deferrable initially deferred
*/
-- 데이터 입력
insert into scott.st(sno, sname, mno) values('111111','장비', NULL);
commit;

-- 데이터 입력(동일 PK)
-- 에러 없음
insert into scott.st(sno, sname, mno) values('111111','유비', NULL);
insert into scott.st(sno, sname, mno) values('111111','관우', NULL);

-- 커밋시 에러 발생
commit;

/*
-- deferrable initially immediate
*/
-- 데이터 입력
insert into scott.ma values('10','화학');
commit;

-- 데이터 입력
-- 에러 발생
insert into scott.ma values('10','물리');

-- deferrable initially deferred로 변경
set constraint scott.ma_mno_pk deferred;

-- 데이터 입력
insert into scott.ma values('10','물리');

-- 커밋 시 에러
commit;

/*
-- Deferrable에 따른 UNIQUE 확인
*/
select table_name, index_name, uniqueness, status
from dba_indexes where table_name in ('MA', 'ST');

 

/*******************************************
-- 지연된 제약조건 상태에 따른 인덱스 변화
*******************************************/
/*
-- 테이블 생성
*/
-- drop table scott.t1 purge;
-- drop table scott.t2 purge;
create table scott.t1
( tno char(2)
, tname varchar2(20)
, constraint t1_tno_pk_deferrable primary key (tno) deferrable
);

create table scott.t2
( tno char(2)
, tname varchar2(20)
, constraint t2_tno_pk_notdeferrable primary key (tno)
);

-- 제약 확인
select table_name, constraint_name, constraint_type, deferrable, deferred, status
from dba_constraints
where table_name in ('T1','T2')
order by 1;

-- Deferrable에 따른 UNIQUE 확인
-- Deferrable(Non Unique), Not Deferrable(Unique)
select table_name, index_name, uniqueness, status
from dba_indexes where table_name in ('T1', 'T2')
order by 1;

/*
-- Disable에 따른 Unique 인덱스 변화
*/
-- PK제약 Disable
alter table scott.t1
disable constraint T1_TNO_PK_DEFERRABLE;

alter table scott.t2
disable constraint T2_TNO_PK_NOTDEFERRABLE;

-- Disable 확인
select table_name, constraint_name, constraint_type, deferrable, deferred, status
from dba_constraints
where table_name in ('T1','T2')
order by 1;

-- INDEX 확인(T2_TNO_PK_NOTDEFERRABLE인덱스 사라짐)
-- 일반적으로 제약조건이 disable되면 인덱스 삭제
select table_name, index_name, uniqueness, status
from dba_indexes where table_name in ('T1', 'T2')
order by 1;

-- PK제약 Enable
alter table scott.t1
enable constraint T1_TNO_PK_DEFERRABLE;

alter table scott.t2
enable constraint T2_TNO_PK_NOTDEFERRABLE;

-- INDEX 확인(T2_TNO_PK_NOTDEFERRABLE인덱스가 재생성됨)
-- 일반적으로 제약조건이 enable되면 인덱스 재생성
select table_name, index_name, uniqueness, status
from dba_indexes where table_name in ('T1', 'T2')
order by 1;