[SQL] Oracle DDL 정리 - CREATE / CTAS / ALTER / DROP / TRUNCATE

1. CREATE

1) 기본 구문

CREATE TABLE 테이블명 (
컬럼명1 데이터타입 [제약조건],
컬럼명2 데이터타입 [제약조건],
...
);

구분	설명
데이터 타입	NUMBER, VARCHAR2, DATE 등 컬럼에 저장할 값의 형식
DEFAULT	값을 입력하지 않았을 때 자동으로 들어갈 기본값
제약조건	PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY

CREATE TABLE emp (
    empno    NUMBER(4)       PRIMARY KEY,
    ename    VARCHAR2(20)    NOT NULL,
    job      VARCHAR2(30),
    salary   NUMBER(10, 2)   DEFAULT 0,
    hiredate DATE,
    deptno   NUMBER(2)
);

 

2) CTAS(Create Table As Select) 구문

• 현재 있는 테이블과 같은 구조와 저장된 데이터를 갖는 테이블을 생성
• CTAS 후 제약조건이 필요하면 ALTER TABLE로 별도로 추가해야 함

CREATE	TABLE	테이블명	AS SELECT문;

-- employees 테이블 구조와 데이터를 갖는 테이블
CREATE TABLE emp1 AS SELECT * FROM employees;

-- employees 테이블 구조만 갖는 테이블 (WHERE 1=2가 항상 False니)
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2;

구분	복사 여부
컬럼 구조	⭕ 복사됨
데이터	⭕ 복사됨 (WHERE 조건에 따라 다름)
PK / FK / UNIQUE 등 제약조건	❌ 복사 안 됨
인덱스	❌ 복사 안 됨

 

2. ALTER

• 이미 생성된 테이블의 구조를 변경하는 DDL 명령어

ALTER TABLE 테이블명 명령어

 

1) ADD / MODIFY / DROP / RENAME / CONSTRAINT 사용

• 컬럼 추가/삭제, 컬럼 이름/크기 변경, 제약조건 추가/삭제 등

-- 1. 컬럼 추가 : emp 테이블에 job 컬럼 추가
ALTER TABLE emp ADD job VARCHAR2(30);

-- 2. 컬럼 속성 변경 : ename 컬럼의 길이를 50으로 변경
ALTER TABLE emp MODIFY ename VARCHAR2(50);

-- 3. 컬럼 이름 변경 : ename 컬럼명을 emp_name으로 변경
ALTER TABLE emp RENAME COLUMN ename TO emp_name;

-- 4. 컬럼 삭제 : job 컬럼 삭제
ALTER TABLE emp DROP COLUMN job;

-- 5. 테이블 이름 변경 : emp 테이블명을 employee로 변경
RENAME emp TO employee;

-- 6. 제약조건 추가 : empno 컬럼에 기본키 제약조건 추가
ALTER TABLE empADD CONSTRAINT emp_pk PRIMARY KEY (empno);

-- 7. 제약조건 삭제 : emp_pk 제약조건 삭제
ALTER TABLE emp DROP CONSTRAINT emp_pk;

구분	가능 여부
크기 늘리기	⭕ 가능
크기 줄이기	기존 데이터가 없거나 전부 NULL일 때만 가능
데이터 타입 변경	컬럼이 비어있을 때만 가능

 

2) SET UNUSED과 DROP UNUSED COLUMNS 옵션

-- 테이블 구조
CREATE TABLE emp (
    empno    NUMBER(4)     PRIMARY KEY,
    ename    VARCHAR2(20)  NOT NULL,
    job      VARCHAR2(30),
    salary   NUMBER(10,2),
    memo     VARCHAR2(100)  -- 안 쓰는 컬럼
);

(1) SET UNUSED : 컬럼 사용 불가 표시

• 컬럼에 표시해서 사용하지 못하게 함
• 한 번 표시하면 되돌릴 수 없음
• 실제 데이터는 아직 디스크에 존재
• 컬럼을 삭제하는데 오래걸리기 때문에 일단 사용하지 못하게 하고 나중에 삭제할때 주로 사용

-- 단일 컬럼 표시
ALTER TABLE emp SET UNUSED COLUMN memo;

-- 여러 컬럼 동시 표시
ALTER TABLE emp SET UNUSED (job, memo);

 

(2) DROP UNUSED COLUMNS : 표시된 컬럼 실제 삭제

• SET UNUSED로 사용하지 않는다고 표시한 컬럼 삭제
• 디스크에서 실제로 제거됨

ALTER TABLE emp DROP UNUSED COLUMNS;

 

구분	SET UNUSED	DROP COLUMN
속도	빠름	느림
실제 삭제	❌	✅
되돌리기	❌ 불가	❌ 불가
사용 시점	운영 중 즉시	여유 있을 때

 

4. RENAME

• 테이블명, 뷰, 시퀀스 또는 동의어 이름 변경

RENAME 기존이름 TO 새이름

⚠️ VSCODE의 확장 프로그램인 ORACLE SQL DEVELOPER에서는 안됨(아직 개발중이라 그런가..)

 

5. DROP

1) 기본 구문

 

• 테이블 자체를 완전히 삭제
• 구조, 데이터, 제약조건, 인덱스 모두 삭제

 

DROP TABLE 테이블명

 

 

2) CASCADE CONSTRAINTS

• 해당 테이블을 참조하고 있는 FK 제약조건까지 함께 삭제
• 테이블 간 관계가 있을 때 사용

DROP TABLE 테이블명 CASCADE CONSTRAINTS;

 

-- dept 테이블을 참조하는 FK가 emp에 있음
CREATE TABLE dept (
    deptno NUMBER PRIMARY KEY
);

CREATE TABLE emp (
    empno  NUMBER PRIMARY KEY,
    deptno NUMBER REFERENCES dept(deptno)  -- FK
);

-- ❌ 오류 발생 : emp가 dept를 참조하고 있어서 삭제 불가
DROP TABLE dept;

-- ✅ 정상 삭제 : emp의 FK 제약조건도 함께 삭제
DROP TABLE dept CASCADE CONSTRAINTS;

 

구분	DROP	TRUNCATE	DELETE
대상	테이블 자체 제거	테이블의 모든 행 제거	조건에 맞는 행 제거
구조	삭제	유지	유지
데이터	삭제	삭제	삭제
WHERE 사용	불가	불가	가능
롤백	불가	불가	가능
속도	가장 빠름	빠름	느림

⚠️ DELETE로 조건없이 지워도 TRUNCATE가 빠름