#2[DDL]_ MySQL 을 이용하여 제약조건 사용 및 DROP TABLE, ALTER TABLE 정리

본 포스트는 개인 스터디에 대한 정리 및 기록의 용도로써, 오개념이 존재 할 수 있습니다. 글은 상시 수정되며, 지적사항에 대해서 검토 후 수정하겠습니다.

본 포스트는 한국데이터진흥원 발간, SQL 전문가 가이드 2013Edition을 참고하였습니다.

---

제약조건 (CONSTRAINT)

데이터의 무결성을 유지하기 위해 특정 칼럼에 설정하는 제약이다. 애초에 무결성을 깨는 값의 입력을 거부한다는 의미로 받아 들여도 좋을거 같다.

 

- NOT NULL 

해당 열(colum)에 NULL값이 올 수 없다는 제약조건을 준다. NULL은 질의를 복잡하게 만드는 원인임으로 가능하면 NOT NULL을 선언해주는게 좋고, NULL값이 꼭 필요하다면 DEFAULT '****' 처럼 약간의 꼼수를 이용해도 된다. '****'은 해당 열에서는 나올 수 없는 값을 넣어 준다.

CREATE TABLE PLAYER (
       PLAYER_ID CHAR(7) NOT NULL,
       PLAYER_NAME VARCHAR(20) NOT NULL DEFAULT '****',
       Eng_PLAYER_NAME VARCHAR(40) NOT NULL DEFAULT '****'
);

# NULL 은 공백이나 숫자 0이 아닌, 현재 데이터를 입력하지 못하는 경우를 의미 === 아직 정의 되지 않은 미지의 값

 

- PRIMARY KEY, FOREIGN KEY 지정하기

• [CONSTRAINT]   [옵션이름부여]   [PRIMARY KEY]   (지정할 열(colum))
• [CONSTRAINT]   [옵션이름부여]   [FOREIGN KEY]   (지정할 열(colum)) [REFERENCESS] [테이블명](해당 열(colum))

이름을 부여하는 이유는 나중에 해당사항을 재사용 및 제거하기에 용이하기 때문이다.

CREATE TABLE PLAYER (
       PLAYER_ID     CHAR(7) 		NOT NULL,
       PLAYER_NAME   VARCHAR(20) 	NOT NULL,
       TEAM_ID       CHAR(3) 		NOT NULL,
       E_PLAYER_NAME VARCHAR(40),
       NICKNAME      VARCHAR(30),
       JOIN_YYYY     CHAR(4),
	   POSITION      VARCHAR(10),
       BACK_NO       TINYINT,
       NATION        VARCHAR(20),
       BIRTH_DATE    DATE,
       SOLAR         CHAR(1),
       HEIGHT        SMALLINT,
       WEIGHT        SMALLINT,
       CONSTRAINT 	 PK_PLAYER 		PRIMARY KEY (PLAYER_ID),
       CONSTRAINT 	 FK_PLAYER_TEAM	FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
       );
       
       
       DESCRIBE PLAYER; --현재 테이블의 상태를 보여주는 명령어

PLAYER_ID, PLAYER_NAME, TEAM_ID 는 NOT NULL 제약 조건이 주어졌고,  PLAYER_ID는 기본키(PK), TEAM_ID는 외래키(FK) 제약조건이 주어졌다.

 

 

---

- DROP TABLE

• 해당 테이블을 통째로 지운다.
• 해당 테이블을 지우기 전에 참조하는 외래키(FK)의 제약 조건 또는 테이블을 먼저 삭제해야, 지정한 테이블을 삭제 할 수 있다.

DROP TABLE PLAYER; 

DESCRIBE PLAYER; --현재 테이블의 상태를 보여주는 명령어

 

 

명령어가 정상적으로 실행되고, 해당 테이블이 존재하는지 찾아보려고 하면 삭제되어 존재하지 않는다는 것 에러로 알려준다.

여기서 위와 같은 에러 메시지를 표출하기 싫을 경우, 아래 명령어를 입력하면 된다.

DROP TABLE IF EXISTS PLAYER;

에러메시지 대신, 해당 테이블이 없다고 나온다.

- TRUNCATE TABLE

• 테이블의 원형은 유지시키고 내용만 지우고 싶을때 사용한다.

TRUNCATE TABLE STADIUM;

테이블의 내용만 사라졌다.

 

---

ALTER TABLE

테이블에 칼럼을 추가/삭제/수정

테이블에 제약조건을 추가/삭제   => 제약조건은 한번에 수정이 불가능하기 때문에 삭제하고 다시 만들어주는 과정을 통하여 수정한다.

 

- ADD COLUMN

• 기존 테이블에 새로운 열(colum)을 추가 하는 명령어.
• 테이블의 마지막 열에 추가되며 위치를 지정할 수는 없다.

ALTER TABLE PLAYER
ADD NICKNAME VARCHAR(10); 

-- NOT NULL, DEFAULT 옵션도 추가 가능

NICKNAME이 추가 된 것을 볼 수 있다.

 

- DROP COLUMN

• 칼럼 삭제 후, 최소 하나 이상의 칼럼이 테이블에 존재해야 한다.

ALTER TABLE PLAYER
 DROP COLUMN PLAYER_ID;

첫번째 열의 PLAYER_ID가 삭제 됐다.

 

 

- MODIFY COLUMN

• 데이터 유형, 디폴트값, NOT NULL, 제약 조건을 변경 할 수 있는 명령어이다.
• 데이터가 있는경우를 고려해야하기 때문에, 적용이 제한적이다. 

1. 해당 열(colum)의 크기를 늘릴 수는 있지만 줄이지는 못함. (기존 데이터가 훼손될수 있기때문)

2. 해당 열(colum)이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면, 칼럼의 크기를 줄일 수 있음.

3. 해당 열(colum)이 NULL 값만을 가지고 있으면, 데이터 유형(datatype)을 변경할 수 있음.

4. 해당 열(colum)의 DEFAULT값을 바꾸면, 변경 작업 이후 발생하는 행 삽입에만 영향을 미침.

5. 해당 열(colum)에 NULL 값이 없을 경우에만, NOT NULL 제약조건을 추가할 수 있음.

ALTER TABLE PLAYER
MODIFY NICKNAME VARCHAR(20) NOT NULL DEFAULT 'GHOST';

PLAYER 테이블은 비어있는 상태로 위 명령어 실행 결과

- RENAME COLUMN

• 해당 열(colum)에 이름을 바꿈
• MySQL, Oracle 등 일부 DBMS에서만 지원

ALTER TABLE PLAYER
RENAME COLUMN NICKNAME TO ENGLISH_NAME;

이름이 바뀌었다.

- DROP PRIMARY KEY, FOREIGN KEY

• 기본키(PK)를 지워준다. 기본키는 1개 밖에 존재하지 않기때문에 따로 옵션명을 지정하지 않아도 된다.

--DROP FOREIGN KEY 
ALTER TABLE TEAM
DROP PRIMARY KEY;

--DROP FOREIGN KEY 
ALTER TABLE TEAM
DROP FOREIGN KEY FK_TEAM_STADIUM;

기본키가 지웠졌다. FK도 똑같이 사용하면 된다.

 

 

---

이상으로 DDL명령어를 다루는 방법을 정리해봤다. 다음 장부터는 DML 명령어를 다루는 방법을 공부하면서 포스팅하도록 하겠다. DDL을 정리하면서 느낀점은 DROP을 사용할때는 매우 신중해야한다는 생각을 했다. 외래키로 각 테이블이 연결되어 있는경우가 대다수다 보니까. 잘 생각하고 지우지 않으면 원하는 테이블을 지우지 못할 뿐더러, 다른테이블의 튜플들에도 영향을 줄 수 있다는 것을 알았다. 시간이 된다면 ON UPDATE / DELETE , CASCADE와 같은 옵션들에 대해서도 한번 정리해 보도록 하겠다.