코드 조각-Android, Java, Spring, JavaScript, C#, C, C++, PHP, HTML, CSS, Delphi

오라클에서 다음과 같은 상황에 대해 DB를 어떻게 변경하는지에 대해 포스팅하고자 한다.

상황 : 
-. 테이블을 새로 생성할수 있는 상황이 아닌 현재 이미 사용하고 있어 데이터가 적재되어 있는 테이블의 PK 변경 및 새로운 칼럼 추가하고자 하는 상황이다.
-. 현재 3개의 PK가 잡혀있는데 이 3개의 PK를 삭제하고 새로 추가하는 칼럼을 PK로 잡고자 한다.
-. PK로 잡히게 될 새로 추가하는 칼럼은 auto increment가 되는 칼럼이다.
-. 테이블 이름은 TB_MEMBER로 가정한다.
-. 새로운 PK로 사용하게 될 새롭게 생성하게 될 칼럼은 RSV_SEQ로 가정한다.

이상의 상황에서 다음 단계를 밟아 원하는 DB를 새롭게 구성할수 있다.

(1) 현재 사용하고 있는 3개의 PK를 삭제한다. TB_MEMBER라는 테이블에서 현재 사용하고 있는 PK들이 삭제된다. PK라는 속성이 제거되는것이지 해당 칼럼 자체가 삭제되는 것은 아니다.

ALTER TABLE TB_MEMBER DROP PRIMARY KEY; 

(2) 새로운 PK로 사용할 칼럼을 추가한다. 새로 추가될 칼럼인 RSV_SEQ를 추후 PK로 사용하게 될 것이고 이 칼럼은 auto increment되는 값을 갖게 될 칼럼이다. 그러나 지금의 시점에서는 단지 새로운 칼럼을 하나 추가하는 것 뿐이다. 

ALTER TABLE TB_MEMBER ADD(RSV_SEQ NUMBER); 

(3) PK로 사용하게 될 새로 추가할 칼럼의 검색 속도를 높이기 위해 index를 생성한다.

CREATE UNIQUE INDEX TB_MEMBER_PK ON TB_MEMBER(RSV_SEQ); 

(4) 새로 추가하게 될 PK 칼럼이 auto increment이므로 이에 대한 sequence를 생성한다. 오라클은 MySQL과 달리 해당 칼럼에 직접 auto_increment 속성을 지정할수가 없어서 별도의 시퀀서를 만들어 사용해야 한다. sequence의 이름은 TB_MEMBER_SEQ이고 1부터 1씩 자동 증가하는 방식의 sequence이다.

CREATE SEQUENCE TB_MEMBER_SEQ START WITH 1 INCREMENT BY 1; 

(5) 이상의 과정까지에서 PK로 사용하게 될 새로운 칼럼에는 아래와 같이 모두 null이 들어 있을 것이다.
---------------------------------
rsv_seq | aaa | bbb | ccc ...
---------------------------------
 null      | ...    | ...    | ...
 null      | ...    | ...    | ...
 null      | ...    | ...    | ...
---------------------------------
...
새로 생성한 칼럼인 RSV_SEQ에 대해 앞에서 생성한 sequence를 이용해서 auto increment된 값을 RSV_SEQ 칼럼에 update해 줘야 한다. 아래의 쿼리에 의해 기존 테이블의 각 row의 RSV_SEQ의 값은 1부터 시작해서 1씩 증가된 값들이 비로소 저장되게 된다.

UPDATE TB_MEMBER SET RSV_SEQ=TB_MEMBER_SEQ.nextval; 

---------------------------------
rsv_seq | aaa | bbb | ccc ...
---------------------------------
 1          | ...    | ...     | ...
 2         | ...    | ...     | ...
 3         | ...    | ...     | ...
---------------------------------
...

(6) 이제 비로소 새로 생성한 칼럼을 PK로 설정할수 있다. 아래와 같이 

ALTER TABLE TB_MEMBER ADD CONSTRAINT TB_MEMBER_PK PRIMARY KEY(RSV_SEQ); 

테스트 단계이거나 개발 단계에서 기존 적재되어 있는 데이터를 모두 날려 버리고 테이블을 새로 생성할수 있다면 굳이 위와 같은 다소 복잡한 과정 필요없이 그냥

drop table ...
create table ...로 간단히 변경된 내용이 적용된 DDL로 원하는 테이블을 구성할수 있을것이나 그러나 이미 사용하고 있는 경우나 기존 있는 데이터를 유지해야할 경우 오라클에서는 위와 같은 단계를 밟아서 처리를 해야 한다.