[오라클] PRIMARY KEY 삭제시 유의할 점 (유니크 제약조건 포함)

아래와 같이 구문을 사용할 때 주의할 점이 있습니다.

ALTER TABLE 테이블명 DROP PRIMARY KEY; 

또는 

ALTER TABLE 테이블명 DROP CONSTRAINT 유니크제약조건명;

그것은 Primary key를 생성하는 방법에 따라 제약조건과 인덱스가 모두 삭제가 될 때도 있고,
또는 제약조건만 삭제가 되고 인덱스는 그대로 남아 있는 경우가 발생한다는 것입니다.
Primary Key를 생성하는 방법에 따른 현상인데요.

Primary Key를 생성할 때 인덱스와 제약조건을 동시에 생성하면 삭제할 때도 동시에 삭제가 되고
이미 생성된 인덱스를 사용해서 Primary Key를 생성하면, 위 구문 수행 시 제약조건만 삭제가 되고
인덱스는 남아 있게 됩니다.

문제는 이미 생성된 Primary Key가 어떤 절차에 의해서 생성되었는지 구분하는 방법이 없다는 것입니다.
(오라클 11g 매뉴얼과 구글링 결과로 내린 결론입니다. 혹시 구분하는 방법을 아시는 분은 댓글로 남겨주세요)

아래와 구문과 같이 제약조건을 생성하면, 제약조건과 해당 인덱스가 동시에 생성이 됩니다.

create table dev4u.tt_test
(
a varchar2(10),
b varchar2(10)
);

alter table dev4u.tt_test add constraint pk_tt_test primary key (a);
alter table dev4u.tt_test add constraint uix_tt_test_01 unique (b);

인덱스를 만들고, 제약조건을 생성하는 경우

create table dev4u.tt_test
(
a varchar2(10),
b varchar2(10)
);

create unique index dev4u.pk_tt_test on dev4u.tt_test(a);
create unique index dev4u.uix_tt_test_01 on dev4u.tt_test(b);

alter table dev4u.tt_test add constraint pk_tt_test primary key (a);
alter table dev4u.tt_test add constraint uix_tt_test_01 unique (b);

가령 사용 중인 테이블의 Primary Key를 구성하는 컬럼에 한 개의 컬럼을 더 추가하거나 빼거나 해서 Primary Key를  
재생성하고 싶을 때, ALTER TABLE 테이블명 DROP PRIMARY KEY; 구문으로 Primary Key를 삭제하면
인덱스도 함게 삭제가 되어서 Primary Key를 다시 생성하기 전까지 실행계획이 변경이 돼서 장애가 발생할 수 있습니다.
또는 반대의 경우도 있겠죠. (참고로 Non Unique 인덱스도 논리적으로 Unique하다면 Primary Key로 생성할 수 있습니다.)

이미 운영 중인 시스템에서 Primary Key 생성 방식이 혼용되어서 사용이 되고 있다면 아래 옵션을 무조건 추가해서 
작업하도록 하면 장애를 방지할 수 있습니다.

그리고 제약조건과 인덱스를 한번에 생성하는 방법보다는 각각 생성해서 사용하는 것이 좀더 유연하게 처리할 수 있어서 
저는 선호합니다.

Primary Key와 Unique Index 모두 동일하게 적용됩니다.

1.) 항상 인덱스와 제약조건을 한번에 삭제하고 싶을 때 

ALTER TABLE 테이블명 DROP PRIMARY KEY DROP INDEX;

2.) 항상 제약조건만 삭제하고 인덱스는 남겨 놓고 싶을 때

ALTER TABLE 테이블명 DROP PRIMARY KEY KEEP INDEX;

PS1) 참고로 Primary Key 제약조건을 ALTER TABLE 테이블명 DROP  CONSTRAINT 제약조건명;
         으로 제거를 해도 인덱스와 제약조건이 동시에 생성된 Primary Key라면 인덱스도 함게 삭제가 됩니다.

PS2) Non-Unique 인덱스를 Primary Key로 생성해야만 하는 경우는 두 가지로  볼 수 있습니다.
       첫 번째는 대용량의 데이터가 이미 존재해서 새로 인덱스를 만드는 것이 비효율적일 때
       논리적으로 Unique하다면 이미 있는 Non-Unique 인덱스를 재활용하는 게 더 나을 때입니다.
       두 번째는 deferrable 옵션이 필요한 경우입니다.
       deferrable은 제약조건 수행을 데이터가 변경되는 시점이 아니라 commit 시점으로 미루는 옵션입니다.
       특수한 환경에서 사용되는데 대용량 데이터를 처리하는 배치 작업에서 사용할 수 있습니다.
       데이터가 변경되는 건건이 제약조건을 적용하는 게 아니라 commit 단위마다 제약조건을 적용하면 성능 면에서 
       유리할 수 있습니다. deferrable 옵션을 사용하려면 Primary Key 일지라도 Non-Unique 인덱스여야만 합니다.

       

 

---