MySQL - ALTER 구문 정리

개인적인 공부를 할 때에나, 혹은 프로젝트를 할 때에 항상 ALTER문이 헷갈렸었습니다.

물론, 잘하시는 분들이거나 혹은 MySQL을 너무 많이 보신분이라면 익숙해 지셨겠지만,

제 경우는 항상 잘 기억이 안나서 찾아보게 됬었습니다.

그래서, 정리를 해 보려고 합니다.

 

포스팅을 위해 새로운 테이블을 하나 생성하겠습니다.

 

1) 컬럼 추가

:: 컬럼 추가는 ALTER 구문의 ADD COLUMN을 사용합니다.

:: ALTER TABLE (테이블명) ADD COLUMN (추가할컬럼명) (컬럼타입) (NOT NULL 등의 제약조건)

 

2) 컬럼 삭제

:: 컬럼삭제는 ALTER 구문의 DROP 구문을 사용합니다.

:: ALTER TABLE (테이블명) DROP (컬럼명)

 

3) 컬럼(명) 변경

:: 컬럼명 변경은 CHANGE 구문을 사용합니다.

:: ALTER TABLE (테이블명) CHANGE (변경할컬럼명) (변경할이름) (타입)

:: 물론 타입을 명시하는것을 보면 알겠지만, 타입을 변경하면 해당 타입이 변경됩니다.

:: 이때, 만약 타입을 변경한다고 했을 때, 변환되지 않는 데이터가 들어있을경우 구문이 실패됩니다.

:: VARCHAR형식에 문자열 데이터가 들어가있는 상황에서 타입을 INT형으로 바꾸려고 한다면 구문이 실패됩니다.

 

4) 컬럼타입 변경

:: 컬럼타입 변경은 MODIFY 구문을 사용합니다.

:: ALTER TABLE (테이블명) MODIFY (변경할컬럼병) (변경할타입)

아래부터는 다시 PHONE을 VARCHAR로 바꾸어 진행하겠습니다

 

5) 컬럼 기본값 설정

::컬럼의 기본값 설정은 ALTER키워드 혹은 MODIFY키워드를 사용합니다.

:: ALTER TABLE (테이블명) ALTER COLUMN (컬럼명) SET DEFAULT (디폴트값)

:: ALTER TABLE (테이블명) ALTER (컬럼명) SET DEFAULT (디폴트값)

:: ALTER TABLE (테이블명) MODIFY (컬럼명) (컬럼타입) DEFAULT (디폴트값)

:: 입맛에 맞는걸 사용하면 될 것 같습니다.

:: ALTER COLUMN과 ALTER 구문은 같은 기능을 하는, 순수하게 해당 컬럼을 수정하는 내용같으며,

:: MODIFY는 컬럼을 수정하면서, 위의 컬럼타입을 변경하면서, 변경과 동시에 DEFAULT값을 선언해주는것 같습니다.

 

 

6) 테이블 외래키 설정

7) 테이블 외래키 삭제

8) ON DELETE CASCADE / ON UPDATE CASCADE

:: 외래키 설정과 삭제, 그리고 ON DELETE CASCADE부분에 대해서는 함께 다루도록 하겠습니다.

:: 테이블을 생성하면서 외래키를 지정하고, 외래키를 삭제하고, 기존테이블에 외래키를 설정하면서

:: ON DELETE CASCADE를 설정해보려고 합니다.

:: 테이블을 하나 생성하면서 FOREIGN KEY를 지정합니다.

:: 테이블 생성시에 FOREIGN KEY (컬럼명) REFERENCES 참조테이블 (참조컬럼명) 으로 지정합니다.

:: 이때, 참조컬럼의 경우에는 PK로 지정되있어야 하며,

:: 외래키가 지정되면 참조하는컬럼에 있는 데이터만 FK지정 컬럼에 값이 될 수 있습니다.

 

:: 외래키를 삭제해보도록 하겠습니다.

:: 외래키를 삭제하기 위해서는 해당 외래키 제약조건의 이름을 알아야 합니다.

:: 만약, MySQL Workbench를 사용중이라면 아래와 같은 루트를 통해 편하게 알 수 있습니다.

 

:: 아니라면 아래와 같이 입력하여 줍니다.

:: INFORMATION_SCHEMA.TABLE_CONSTRAINTS에는 제약조건과 관련된 사항이 담겨져 있습니다.

:: 저희가 알고싶은거 저중에서 CONSTRAINT_NAME입니다만... 

:: SELECT CONSTRAINT_NAME FROM INFORMATION_SCHMA.TABLE_CONSTRAINTS라고 적으셔도 무방합니다.

:: 이때, 저의 경우는 WHERE절을 추가해줬는데,

:: WHERE절을 추가하지 않을경우 MySQL내에 제약조건이 모두 나오게 되어 WHERE절을 추가해줬습니다.

:: 결과는 아래와 같습니다.

:: 이제 제약조건을 삭제시키려고 합니다.

:: 외래키의 삭제는 DROP FOREIGN KEY 키워드를 이용합니다.

:: ALTER TABLE (테이블명) DROP FOREIGN KEY (제약조건명)

:: 외래키를 삭제했습니다.

:: 이제 ALTER TABLE 구문을 통해 외래키를 생성해보도록 하겠습니다.

:: 이때, 포스팅에서는 ON DELETE CASCADE 옵션을 사용하려고합니다.

:: 기존에 일반적인 경우에는 외래키가 참조하는 컬럼을 삭제하려고 할 경우 외래키 제약조건에 의해 오류가 발생합니다.

:: 해당 값이 지워지면 이를 참조하는 외래키가 존재할 수 없기때문입니다.

:: 이때, ON DELETE CASCADE 옵션을 사용하면 오류는 뜨지않고 삭제가 가능하게되며,

:: 이때, 해당 컬럼을 참조하는 외래키를 가지는 데이터들은 모두 연쇄적으로 삭제됩니다.

:: ALTER TABLE 테이블명 ADD CONSTRAINT FOREIGN KEY (컬럼명) REFERENCES 참조테이블(참조컬럼명)

:: 위 구문을 통해 일반적인 외래키 생성이 가능합니다. 이때 제일 뒤에 ON DELETE CASCADE 를 함께 작성해주면

:: 이제부터 참조되는 테이블의 컬럼이 삭제되면, 그에 대응되는 참조하는 테이블의 데이터들이 함께 삭제됩니다.

:: 현재 테이블의 상태는 이와 같습니다.

:: 기존 별다른 설정을 하지 않았을경우, POSTTBL의 데이터는 현재 STD_NUM를 CLASSTBL에서 참조하기때문에

:: DELETE구문으로 삭제할 수 없습니다.

:: 하지만, 외래키를 생성하면 ON DELETE CASCADE구문을 사용했을 경우

:: 위와같이 참조하고있던 CLASSTBL의 데이터도 함께 사라짐을 알 수 있습니다.

 

+추가.

ON DELETE CASCADE구문의 경우에는 편리한 옵션입니다만,

MODIFY CONSTRAINT에 명시되어있는 부분만 기존 제약조건에 수정이 가능합니다.

이 옵션을 추가하기 위해서는 제약조건을 삭제하고 다시 생성하는 과정이 필요합니다.

https://stackoverflow.com/questions/1571581/how-to-add-on-delete-cascade-in-alter-table-statement

How to add 'ON DELETE CASCADE' in ALTER TABLE statement

하지만, 10년 전 질문이기때문에 지금은 가능할지도 모르겠네요.