mysql - alter문

<목차로 돌아가기>

alter 문은 이미 만들어진 table에 대하여 테이블명, 컬럼명 테이블 타입, 커럼 타입 등을 바꾸거나 이미 만들어진 테이블에 추가적인 컬럼을 생성하거나 삭제 할 수 있습니다.

1.테이블 형식 변경	ㄱ.Engine 사용	ALTER TABLE 테이블이름 ENGINE=형식
	ㄴ.type 사용	ALTER TABLE 테이블이름 TYPE=형식
2.테이블 이름 변경	ㄱ.alter에 의한	ALTER TABLE 테이블이름 RENAME 새테이블이름
	ㄴ.rename에 의한	RENAME TABLE 테이블이름 TO 새테이블이름
3.컬럼 추가	ㄱ.마지막 컬럼에	ALTER TABLE 테이블이름 ADD COLUMN 컬럼이름 컬럼타입
	ㄴ.지정한 컬럼 뒤에	ALTER TABLE 테이블이름 ADD COLUMN 새컬럼이름 컬럼타입 AFTER 컬럼이름
	ㄷ.첫 컬럼에	ALTER TABLE 테이블이름 ADD COLUMN 새컬럼이름 컬럼타입 FIRST
4.컬럼 삭제		ALTER TABLE 테이블이름 DROP COLUMN 컬럼이름
5.컬럼 변경	ㄱ.modify에 의한	ALTER TABLE 테이블이름 MODIFY 컬럼이름 새컬럼타입
	ㄴ.change에 의한	ALTER TABLE 테이블이름 CHANGE 컬럼이름 (새)컬럼이름 새컬럼타입
6.인덱스에 새항목 추가		ALTER TABLE 테이블이름 ADD INDEX(컬럼이름)
7.인덱스 삭제	ㄱ.alter에 의한	ALTER TABLE 테이블이름 DROP INDEX 컬럼이름
	ㄴ.drop에 의한	DROP INDEX 인덱스이름 ON 테이블이름

1. ADD COLUMN (컬럼 추가)

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

ALTER TABLE 테이블명 ADD 추가할 컬럼명  데이터 유형; ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80)); // 기본키 추가 ALTER TABLE TEST  ADD CONSTRAINT member PRIMARY KEY(id);

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

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

2. DROP COLUMN (컬럼 삭제)

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

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

ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명; ALTER TABLE MEMBER DROP COLUMN NAME;

- MEMBER 테이블에 NAME 컬럼을 삭제한다.

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

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); [출처] MSSQL CASCADE 문 쓰는 방법 . MSSQL 기본키 거는 방법. ALTER 문 사용방법|작성자 고고싱

- 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 테이블명 RENAME COLUMN 변경해야할 컬럼명 TO 새로운 컬럼명; ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEAM_ID;

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

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

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

5. 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)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도치 않은 삭제와 같은 불상사를 방지하는 효과를 볼 수 있다.

6. DROP CONSTRAINT

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

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

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