SQL - ALTER TABLE

SQL ALTER TABLE 테이블 수정 (ADD, DROP, MODIFY, RENAME)

한번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시의 구조를 유지하게 된다.

처음의 테이블 구조를 그대로 유지하는 것이 최선이지만, 업무적인 요구 사항이나 시스템 운영상 테이블을 사용하는 도중에 변경해야할 일들이 발생하기도 한다.

이 경우 주로 컬럼을 추가/삭제 또는 제약조건을 추가/삭제 작업을 하게 된다.

테이블 구조 보기

DESC 테이블명 DESCRIBE 테이블명 EXPLAIN 테이블명

1. ADD COLUMN (컬럼 추가)

기존 테이블에 필요한 컬럼을 추가하는 명령어

ALTER TABLE 테이블명 ADD 추가할 컬럼명  데이터 유형; ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

- PLAYER 테이블 마지막에 ADDRESS(길이 80) 컬럼을 추가한다.

* 새롭게 추가된 컬럼은 테이블의 마지막 컬럼이 되며 컬럼의 위치를 지정할 수는 없다.

2. DROP COLUMN (컬럼 삭제)

DROP COLUMN은 테이블에서 필요 없는 컬럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다.

한 번에 하나의 컬럼만 삭제 가능하며, 컬럼 삭제 후 최소 하나 이상의 컬럼이 테이블에 존재해야 한다.

ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명; ALTER TABLE PLAYER DROP COLUMN ADDRESS;

- PLAYER 테이블에 ADDRESS 컬럼을 삭제한다.

* 한번 삭제된 컬럼은 복구가 불가능하다.

3. MODIFY COLUMN (컬럼 수정)

테이블에 존재하는 컬럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경을 한다.

ALTER TABLE 테이블명 MODIFY 컬럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],                컬럼명2 데이터 유형 [DEFAULT 식] [NOT NULL];                           ALTER TABLE TEAM_TEMP MODIFY ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL; 다른 컬럼 다음으로 이동: ALTER TABLE 테이블명  MODIFY COLUMN 컬럼명 자료형 AFTER 다른컬럼; ALTER TABLE jsp_user MODIFY COLUMN birthyy varchar(20) AFTER mail; 첫번째 위치로 이동: ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 자료형 FIRST;

- TEAM 테이블의 ORIG_YYYY 컬럼의 데이터 유형을 CHAR(4)->VARCHAR2(8)로 변경하고, 향후 입력되는 데이터의 DEFAULT 값으로 '20020129'를 적용하고, 모든 행의 ORIG_YYYY 컬럼에 NULL이 없으므로 제약조건 NULL -> NOT NULL로 변경한다.

 

* MODIFY COLUMN 시 고려사항

- 해당 컬럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. 이는 기존의 데이터가 훼손될 수 있기 때문이다.

- 해당 컬럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 컬럼의 폭을 줄일 수 있다.

- 해당 컬럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 컬럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

- 해당 컬럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

4. RENAME COLUMN (컬럼명 수정)

테이블을 생성하면서 만들어졌던 컬럼명을 변경해야 할 경우에 사용한다.

ALTER TABLE 테이블명                                             //   Oracle RENAME COLUMN 변경해야할 컬럼명 TO 새로운 컬럼명; ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEAM_ID; ALTER TABLE 테이블명                                            //mysql CHANGE  변경해야할 컬럼명  새로운 컬럼명 컬럼옵션 (INT, VARCHAR(255),....  and so on); ALTER TABLE PLAYER CHANGE  PLAYER_ID  TEAM_ID VARCHAR(11);

- PLAYER 테이블의 PLAYER_ID 컬럼명을 TEAM_ID 로 변경한다.

* RENAME COLUMN으로 컬럼명을 변경하면, 해당 컬럼과 관계된 제약조건에 대해서도 자동으로 변경된다.

(Oracle 등 일부 DBMS에서만 지원한다.)

5. RENAME TO (테이블명 수정)

테이블을 생성하면서 만들어졌던 테이블명을 변경해야 할 경우에 사용한다.

ALTER TABLE 테이블명                                             //   Oracle RENAME  TO 새로운 테이블명; ALTER TABLE PLAYER RENAME  TO NEW_PLAYER; ALTER TABLE 테이블명                                            //mysql RENAME  TO 새로운 테이블명; ALTER TABLE PLAYER RENAME  TO NEW_PLAYER;

- PLAYER 테이블의 PLAYER_ID 컬럼명을 TEAM_ID 로 변경한다.

6. ADD CONSTRAINT

테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명); ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK    FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

- PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다.

- 제약조건명은 PLAYER_FK로 하고, PLAYER 테이블의 TEAM_ID 컬럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건이다.

* FOREIGN KEY 제약조건을 위와 같이 추가하면 PLAYER 테이블의 TEAM_ID 컬럼이 TEAM 테이블의 TEAM_ID 컬럼을 참조하게 된다.

참조 무결성 옵션에 따라서 만약 TEAM 테이블이나 TEAM 테이블의 데이터를 삭제하려 할 경우 외부(PLAYER 테이블)에서 참조되고 있기 때문에 삭제가 불가능하게 제약을 할 수 있다.

즉 외부키(FK)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도치 않은 삭제와 같은 불상사를 방지하는 효과를 볼 수 있다.

7. DROP CONSTRAINT

테이블 생성 시 부여했던 제약조건을 삭제하는 명령어

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명; ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

- PLAYER 테이블의 제약조건 PLAYER_FK 를 제거한다.