[Oracle SQL] PRIMARY KEY 삭제시 유의할 점

ALTER TABLE 테이블명 DROP PRIMARY KEY; 

위 구문을 사용할때 주의할 점이 있습니다.

그것은 Primary key를 생성하는 방법에 따라 제약조건과 인덱스가 모두 삭제가 될때도 있고,

또는 제약조건만 삭제가 되고 인덱스는 그대로 남아 있는 경우가 발생 한다는 것입니다.

Primary Key를 생성하는 방법에 따른 현상인데요.

Primary Key를 생성할때 인덱스와 제약조건을 동시에 생성하면 삭제할 때도 동시에 삭제가 되고

이미 생성된 인덱스를 사용해서 Primary Key를 생성하면, 위 구문 수행시 제약조건만 삭제가 되고

인덱스는 남아 있게 됩니다.

문제는 이미 생성된 Primary Key가 어떤 절차에 의해서 생성 되었는지 구분하는 방법이 없다는 것입니다.

(오라클 11g 메뉴얼과 구글링 결과로 내린 결론 입니다. 혹시 구분하는 방법을 아시는 분은 댓글로 남겨주세요)

가령 사용 중인 테이블의 Primary Key를 구성하는 컬럼에 한개의 컬럼을 더 추가하거나 빼거나해서 Primary Key를  재생성하고 

싶을 때 ALTER TABLE 테이블명 DROP PRIMARY KEY; 구문 수행시

 인덱스도 함게 삭제가 되버리면 Primary Key를 다시 생성하기 전까지 실행계획이 변경이 되서 장애가 발생할 수 있을 것입니다.

또는 반대의 경우도 있겠죠. (참고로 Non Unique 인덱스도 논리적으로 Unique하다면 Primary Key로 생성할 수 있습니다.)

결론은 이미 운영중인 시스템에서 Primary Key 생성방식이 혼용되어서 사용이 되어지고 있다면 아래 옵션을 무조건 추가해서 

작업하도록 하면 장애를 방지할 수 있습니다.

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인덱스여야만 합니다.

출처: http://dev4u.tistory.com/873 [dev for you]