[DB] DDL(Data Definition Language) (ALTER, DROP)

▶ DDL(Data Definition Language)이란?

 

데이터 정의 언어로 객체(OBJECT)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말함

 

 

▷ ALTER

- 테이블에 정의된 내용을 수정할 때 사용하는 데이터 정의어

= 컬럼의 추가/삭제, 제약조건의 추가/삭제, 컬럼의 자료형 변경, DEFAULT 값 변경,

= 테이블 명/컬럼 명/제약 조건 명 변경 등을 할 수 있음

 

 

▷ DROP

데이터베이스 객체를 삭제하는 구문

 

---

 

1. ALTER

[컬럼 추가]

ALTER TABLE DEPT_COPY
ADD (CNAME VARCHAR2(20));

 

 

ALTER TABLE DEPT_COPY
ADD (LNAME VARCHAR2(40) DEFAULT ‘한국’);

 

[컬럼 수정]

ALTER TABLE DEPT_COPY
MODIFY DEPT_ID CHAR(3)
MODIFY DEPT_TITLE VARCHAR(30)
MODIFY LOCATION_ID VARCHAR2(2)
MODIFY CNAME CHAR(20)
MODIFY LNAME DEFAULT '미국'; -- 기본 값을 '미국'으로 변경

 

 

DEPT_COPY 테이블의 DEPT_ID 컬럼의 데이터 타입을 CHAR(2) -> VARCHAR2(3)으로 변경

ALTER TABLE DEPT_COPY
MODIFY DEPT_ID VARCHAR2(3);
--> 테이블 정보에서 확인

-- (수정 에러 상황 : 저장된 값보다 작은 크기로 변환 불가)
-- DEPT_TITLE 컬럼의 데이터타입을 VARCHAR2(10)으로 변경
CREATE TABLE DEPT_COPY
MODIFY DEPT_TITLE VARCHAR2(10);
-- 일부 값이 너무 커서 열 길이를 줄일 수 없음

 

 

*  데이터 타입 수정 시 컬럼에 저장된 데이터 크기 미만으로 변경할 수 없다.
      EX) CHAR(3) 짜리 컬럼에 'ABC' 저장된 상태
         -> CHAR(1) 수정 하려고하면 오류 발생

 

[컬럼 삭제]

ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_ID;

 

 

※ 컬럼 삭제 시 참조하고 있는 컬럼이 있다면 컬럼 삭제 불가능

CREATE TABLE TB1(

PK NUMBER PRIMARY KEY,
FK NUMBER REFERENCES TB1,
COL1 NUMBER,
CHECK(PK > 0 AND COL1 > 0)

);
ALTER TABLE TB1
DROP COLUMN PK; // 오류 발생

 

 → 참조하고 있는 컬럼 삭제 시 CASCADE 사용

 

※ DDL / DML을 혼용해서 사용할 경우 발생하는 문제점

- DML을 수행하여 트랜잭션에 변경사항이 저장된 상태에서
- COMMIT/ROLLBACK 없이 DDL 구문을 수행하게되면
- DDL 수행과 동시에 선행 DML이 자동으로 COMMIT 되어버림.

 

 

---

 

[제약조건 추가]

ALTER TABLE 테이블명 
ADD [CONSTRAINT 제약조건명] 제약조건(컬럼명) [REFERENCES 테이블명[(컬럼명)]];

 

 

<테이블에 PK / UNIQUE / CHECK / NOT NULL 제약조건 추가>

 

-- 서브쿼리를 이용해서 DEPARTMENT 테이블 복사(DEPT_COPY) --> NOT NULL 제약조건만 복사됨
CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;

SELECT *
FROM DEPT_COPY;

-- DEPT_COPY 테이블에 PK 추가
ALTER TABLE DEPT_COPY 
ADD CONSTRAINT DEPT_COPY_PK PRIMARY KEY(DEPT_ID);


-- DEPT_COPY 테이블의 DEPT_TITLE 컬럼에 UNIQUE 제약조건 추가
-- (DEPT_COPY_TITLE_U)
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DEPT_COPY_TITLE_U UNIQUE(DEPT_TITLE);


-- DEPT_COPY 테이블의 LOCATION_ID 컬럼에 CHECK 제약조건 추가
-- 컬럼에 작성할 수 있는 값은 L1, L2, L3, L4, L5 
-- 제약조건명 : LOCATION_ID_CHK
ALTER TABLE DEPT_COPY
ADD CONSTRAINT LOCATION_ID_CHK
CHECK (LOCATION_ID IN ('L1', 'L2', 'L3', 'L4', 'L5'));



-- DEPT_COPY 테이블의 DEPT_TITLE 컬럼에 NOT NULL 제약조건 추가
-- * NOT NULL 제약조건은 다루는 방법이 다름
-->  NOT NULL을 제외한 제약 조건은 추가적인 조건으로 인식됨.(ADD/DROP)
-->  NOT NULL은 기존 컬럼의 성질을 변경하는 것으로 인식됨.(MODIFY)
ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE NOT NULL; -- NULL 허용 X

ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE NULL; -- NULL 허용

 

 

<테이블에 추가한 제약조건 중 PK 빼고 모두 삭제>

ALTER TABLE DEPT_COPY
DROP CONSTRAINT DEPT_COPY_TITLE_U;

ALTER TABLE DEPT_COPY
DROP CONSTRAINT LOCATION_ID_CHK;


-- NOT NULL 제거 시 MODIFY 사용
ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE NULL; -- NULL 허용

 

 

[제약조건 삭제]

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