[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
MODIFY DEPT_ID CHAR(3)
MODIFY DEPT_TITLE VARCHAR(30)
MODIFY LOCATION_ID VARCHAR2(2)
MODIFY CNAME CHAR(20)
MODIFY LNAME DEFAULT '미국';

 

컬럼 삭제

ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_ID;

 

제약조건 추가

ALTER TABLE DEPT_COPY
ADD CONSTRAINT DCOPY_DID_PK PRIMARY KEY(DEPT_ID)
ADD CONSTRAINT DCOPY_DTITLE_UNQ UNIQUE(DEPT_TITLE)
MODIFY LNAME CONSTRAINT DCOPY_LNAME_NN NOT NULL;

 

제약조건 삭제

ALTER TABLE DEPT_COPY
DROP CONSTRAINT DCOPY_DID_PK
DROP CONSTRAINT DCOPY_DTITLE_UNQ
MODIFY LNAME NULL;

 

컬럼 이름 변경

ALTER TABLE DEPT_COPY
RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

 

제약조건 이름 변경

ALTER TABLE USER_FOREIGNKEY
RENAME CONSTRAINT SYS_C007211 TO UF_UP_NN;
ALTER TABLE USER_FOREIGNKEY
RENAME CONSTRAINT SYS_C007212 TO UF_UN_PK;
ALTER TABLE USER_FOREIGNKEY
RENAME CONSTRAINT SYS_C007213 TO UF_UI_UQ;
ALTER TABLE USER_FOREIGNKEY
RENAME CONSTRAINT SYS_C007214 TO UF_GC_FK;

 

테이블 이름 변경

 

ALTER TABLE DEPT_COPY
RENAME TO DEPT_TEST;

RENAME DEPT_COPY TO DEPT_TEST;

 

2. DROP

 

DROP TABLE DEPT_TEST CASCADE CONSTRAINT;