[MySQL] DDL(2) ALTER

📌 ALTER

- 테이블 수정

- 테이블에 대한 정의 변경

- 새로운 컬럼 추가, 특정 컬럼의 기본값 변경, 특정 컬럼 삭제 등

ALTER TALBE 테이블명

• ADD : 컬럼 추가
• RENAME COLUMN : 컬럼 이름 변경
• MODIFY : 컬럼 데이터 형식 변경
• CHANGE : 컬럼 이름과 데이터 형식 함께 변경
• DROP COLUMN : 컬럼 삭제
• DROP : 여러 개 컬럼 삭제
• DROP PRIMARY KEY : 기본키 삭제
• DROP CONSTRAINT : 제약조건 삭제

---

사용 테이블

• student / department 테이블
• 상세코드 : DDL(1) CREATE

---

1️⃣ Table 이름 변경

• 한 테이블 이름 변경
  ALTER TABLE 기존테이블명 RENAME 새테이블명;
ALTER TABLE RENAME TABLE 기존테이블명 TO 새테이블명;

• 여러 테이블 이름 변경

ALTER TABLE RENAME TABLE 기존테이블명1 TO 새테이블명1,
				         기존테이블명2 TO 새테이블명2,
				         기존테이블명3 TO 새테이블명3, 
                         ...;

---

2️⃣ Column 추가

ALTER TABLE 테이블명 ADD 컬럼이름;

ALTER TABLE student ADD stdTel VARCHAR(15);
ALTER TABLE student ADD(
	stdAge VARCHAR(2),
    stdAddress2 VARCHAR(50)
);

---

3️⃣ 테이블 정보/데이터 조회

DESCRIBE 테이블명
DESC 테이블명

DESCRIBE student;

DESC student;

---

4️⃣ Column 속성 변경

ALTER TABLE 테이블명 MODIFY 컬럼명 변경할데이터형식;

ALTER TABLE student MODIFY stdName VARCHAR(20) NULL;
# 기존 VARCHAR(30) NOT NULL 에서 VARCHAR(20) NULL 로 변경

---

5️⃣ Column 이름 변경

ALTER TABLE 테이블명 RENAME COLUMN 변경전이름 TO 변경후이름;

ALTER TABLE student RENAME COLUMN stdTel To stdHP;

---

6️⃣ Column 이름 & 데이터 형식 변경

ALTER TABLE 테이블명 CHANGE 컬럼변경전이름 변경후이름 데이터형식;

ALTER TABLE student CHANGE stdAddress stdAddress1 VARCHAR(30);
# stdAdress 에서 stdAddress1 으로 이름변경
# VARCHAR(50) 에서 VARCHAR(30) 으로 데이터 형식 변경

---

7️⃣ Column 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명

ALTER TABLE 테이블명 DROP 컬럼명,
					DROP 컬럼명2,
                    DROP 컬럼명3, ...;

• 컬럼 1개 삭제

ALTER TABLE student DROP COLUMN stdHP;

• 컬럼 2개 이상 삭제

ALTER TABLE student DROP stdAge,
					DROP stdAddress1,
                    DROP stdAddress2;

• 외래키 혹은 연결되어 있는 키는 삭제가 불가하니 제약조건 등을 해제한 후에 DROP 을 진행해야 한다.

---

8️⃣ 제약조건 변경 (Constraint)

1. 기본키 삭제

ALTER TABLE 테이블명 DROP PRIMARY KEY

• 기본키를 참조하고 있는 다른 테이블이 있는 경우, 기본키를 먼저 삭제할 수 없다.
• 외래키 테이블을 변경한 후에 기본키 삭제가 가능하다.

ALTER TABLE department DROP PRIMARY KEY;
# department 의 primary key 인 dptNo 을 student 테이블이 foreign key 로 사용하고 있어 에러가 발생한다.

2. 제약조건 삭제

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

• 외래키 제약조건 삭제 후 기본키 삭제 가능

ALTER TABLE student DROP CONSTRAINT FK_student_dptNo;

3. 기본키 제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 PRIMARY KEY (컬럼명);
ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명);

ALTER TABLE department ADD CONSTRAINT PK_department_dptNo PRIMARY KEY (dptNo);

ALTER TABLE department ADD PRIMARY KEY (dptNo);

4. 외래키 제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 FOREIGN KEY (컬럼명) REFERENCES 참조테이블(컬럼명);

ALTER TABLE student ADD CONSTRAINT FK_student_dptNo 
					FOREIGN KEY (dptNo) REFERENCES department(dptNo);

※ 제약조건 목록 보기

• 제약조건 목록을 통해 설정했던 제약조건 이름 및 타입을 확인하여 제약조건 변경 , 삭제에 활용한다.
• 제약조건 목록 결과
  

SELECT * FROM information_schema.table_constraints
	WHERE CONSTRAINT_SCHEMA='testdb2';

5. 외래키 제약조건 변경

• 기존 제약조건 삭제 후 다시 설정
  ON DELETE RESTRICT ON UPDATE RESTRICT
ON DELETE CASCADE ON UPDATE CASCADE

• 현재 student 테이블은 department 의 기본키인 deptNo 을 외래키로 가지고 있다. 따라서 아래의 코드는 오류가 발생한다.

DELETE FROM `testdb2`.`department` WHERE (`dptNo = '3');

• student 테이블의 외래키 제약조건 삭제 후 ON DELETE CASCADE 를 작는 외래키 제약조건으로 다시 설정한다.
  - 단, 데이터 무결성, 일관성 관점에서는 추천하지 않는다.

ALTER TABLE student DROP CONSTRAINT FK_student_dptNo;

ALTER TABLE student ADD CONSTRAINT FK_student_dptNo
	FOREIGN KEY(dptNo)
    REFERENCES department(dptNo) ON DELETE RESTRICT ON UPDATE CASCADE;

• Update 를 CASCADE 하여 Update 가 적용된다.

UPDATE `testdb2`.`department` SET `dptNo` = '10' WHERE (`dptNo` = '3');

6. CHECK / DEFAULT 제약조건 추가, 삭제

• 삭제 시 제약조건 이름을 사용해야 한다.
• 먼저 제약조건 이름을 확인 (4번의 ※ 참조)
  - SELECT * FROM information_schema.table_constraints;
  • Ex) CONSTRAINT_TYPE 이 CHECK인 행에서 CONSTRAINT_NAME 확인

SELECT * FROM information_schema.table_constraints
	WHERE CONSTRAINT_SCHEMA='testdb2' AND TABLE_NAME='student' 
    		AND CONSTRAINT_TYPE='CHECK';

• DEFAULT값 CHECK 조건이 적용된 경우 : stdYear 이 1~4 범위만 입력 가능하나 5인 데이터를 추가하려는 경우, 에러 발생한다. 따라서, 제약조건 삭제 후 데이터 삽입, 그 후 제약조건을 다시 추가한다.
  - 에러 : student_chk_1 이라는 CHECK CONSTRAINT 위반이다.

Executing:
INSERT INTO `testdb2`.`student` (`stdNo`, `stdName`, `stdYear`, `stdBirthDay`, `dptNo`) VALUES ('2022', '방자', '5', '2000-01-10', '1');

ERROR 3819: 3819: Check constraint 'student_chk_1' is violated.
SQL Statement:
INSERT INTO `testdb2`.`student` (`stdNo`, `stdName`, `stdYear`, `stdBirthDay`, `dptNo`) 
VALUES ('2022', '방자', '5', '2000-01-10', '1')

• CHECK 제약조건 삭제

ALTER TABLE student DROP CONSTRAINT student_chk_1;

• CHECK 제약조건 추가

ALTER TABLE student ADD CHECK(stdYear >= 1 AND stdYear <= 5);