[SQL] ALTER TABLE

alter table 은 테이블을 수정하는 명령어이다.

ALTER TABLE table

ADD (column datatype [DEFAULT expr]

[, column datatype]...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]

[, column datatype]...);

ALTER TABLE table

DROP (column);

Adding a Column (컬럼 추가)

컬럼을 추가하게 되면 테이블의 제일 마지막에 생성된다.
추가된 컬럼의 순서는 변경할 수 없으며 순서를 변경하려면 'DROP' 후 다시 생성해야 한다.

Modifying a Column (컬럼 수정)
사용자는 data Type, size, default value 를 수정 할 수 있다.

- 크기 축소에 가능하려면 다음 3가지중 하나는 해당되어야 한다.
1. 사이즈를 축소하려는 컬럼의 데이터가 모두 null 일때 가능
2. 테이블의 데이터가 no row 일때 가능.
3. 데이터가 들어있다면 데이터중 가장 큰 데이터보다 크게해야 가능.

- varchar2와 char 는 서로 String 이라 변환이 가능하다. 
하지만 변경시에는 char Data Type 은 고정형이다. varchar2 데이터 타입을 char 데이터타입으로 변경하면 저장공간을 확보해야 하므로 이에 대한 고려가 필요하다.

Dropping a Column (컬럼 삭제)
drop 명령어는 컬럼을 삭제 한다. 컬럼 삭제는 당연한 말이지만 신중히 해야 한다. 일단 수행되면 Rollback 이 불가능하고 성능의 하락을 가져올 수 있다. 
- 성능하락 : 데이터가 많은 컬럼을 drop 하게 되면 기존 데이터는 삭제가 되지 않고 'undo' 로 바뀌게 된다. 이는 곧 쓰레기 데이터가 되므로 성능상 좋지 않다는 말이다. 반대로 데이터가 없는 컬럼 삭제는 관계없다.
- 사이즈가 큰 테이블인 경우 삭제에 많은 시간이 소요될 수 있다. 이때 테이블에는 rock 이 걸린다.


SET UNUSED 

ALTER TABLE <table_name>

SET UNUSED(<column_name>);
 

ALTER TABLE <table_name>

SET UNUSED COLUMN <column_name>;

 
unused 컬럼 완전 삭제 

ALTER TABLE <table_name>

DROP UNUSED COLUMNS;

 
이 방법은 테이블의 컬럼이 삭제가 되는것처럼 작동하게 한다. 이렇게 처리된 컬럼과 같은 이름의 컬럼의 추가도 가능하다. 완전 삭제와 같다. 또 이렇게 처리된 컬럼은 복구는 불가능하다.

하지만 삭제하는 시간이 걸리지 않으므로 lock 이 걸리지도 않는다. 데이터의 'undo' 여부는 잘 모르겠다.

Adding a Constraint( 제약조건 추가)
1. 컬럼의 제약조건은 추가할 수는 있지만, 이미 추가된 제약조건에 대해서는 수정할 수 없다.
2. 제약조건을 사용 또는 불가능  상태로 바꿀 수 있다.
3. Not Null 제약조건은 추가하는게 아니라 수정으로 설정이 가능하다.(not null 예외)

ON DELETE CASCADE
FK(FOREIGN KEY) 제약 조건을 추가할 때 이 옵션을 설정 할 수 있다. 이 옵션은 부모의 키가 삭제되면 자동으로 부모의 키를 참조하던 자식의 데이터가 삭제 되게 하므로써 참조무결성을 지키게 해준다.
 

제약조건 DISABLE
제약조건을 사용하지 않게 할 수 있다.

ALTER TABLE table

DROP PRIMARY KEY | UNIQUE (column) |

CONSTRAINT constraint [CASCADE];

- pk 를 사용안함으로 설정하면 nuique index 가 모두 삭제된다. 다시 사용하면 생성됨.
- CASCADE 를 사용하게 되면 PK 를 바라보고 있는 FK 들도 모두 Disable 상태가 된다. 하지만 enable로 바꿀 시에는 CASCADE 기능이 없으므로 따로따로 enable 상태로 해줘야 한다.  (사용시 주의)

제약조건 ENABLE

ALTER TABLE table

ENABLE CONSTRAINT constraint;

위의 Disable 에서도 설명했지만 CASCADE 를 사용하여 DISABLE 를 했을시에 ENABLE 에는 CASCADE 가 없다.











참고

Oracle Database 10g: SQL Fundamentals