DDL(Data Definition Language) 명령어인 CREATE, DROP, ALTER 등은 데이터베이스 구조를 정의하는 명령어로, 실행 즉시 자동으로 커밋되어 롤백이 불가능하다는 특징이 있다. 그만큼 신중하게 사용해야 하며, 특히 운영 환경에서는 충분한 검토와 테스트가 필요하다. 이번 포스트에서 실질적인 DDL 구문 사용법을 한 번 알아보고자 한다.
1. CREATE 구문: 객체 생성하기
1.1 테이블 생성 (CREATE TABLE)
테이블은 데이터베이스의 가장 기본적인 객체로, 데이터를 저장하는 공간이다.
CREATE TABLE table_name (
column1 datatype [NULL | NOT NULL] [DEFAULT value] [CONSTRAINT constraint_name],
column2 datatype [NULL | NOT NULL] [DEFAULT value],
...
[CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)],
[CONSTRAINT constraint_name FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)],
[CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n)],
[CONSTRAINT constraint_name CHECK (condition)]
);
간단한 예시를 살펴보자.
CREATE TABLE employees (
employee_id NUMBER(6) NOT NULL,
first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) NOT NULL,
hire_date DATE NOT NULL,
salary NUMBER(8,2),
department_id NUMBER(4),
CONSTRAINT emp_pk PRIMARY KEY (employee_id),
CONSTRAINT emp_email_uk UNIQUE (email),
CONSTRAINT emp_salary_min CHECK (salary > 0),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id)
);
이 예시에서 employees는 테이블 이름이고, 각 컬럼의 이름과 데이터 타입, 제약 조건 등을 정의했다. employee_id나 department_id와 같은 이름은 각자의 환경에 맞게 변경하면 된다.
1.2 인덱스 생성 (CREATE INDEX)
인덱스는 데이터 검색 속도를 향상시키는 객체로, 자주 조회되는 컬럼에 생성하면 쿼리 성능이 크게 개선될 수 있다.
CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [ASC | DESC], column2 [ASC | DESC], ... column_n [ASC | DESC]);
예시:
-- 단일 컬럼 인덱스
CREATE INDEX emp_last_name_idx ON employees (last_name);
-- 복합 컬럼 인덱스
CREATE INDEX emp_name_idx ON employees (last_name, first_name);
-- 고유 인덱스
CREATE UNIQUE INDEX emp_email_idx ON employees (email);
-- 함수 기반 인덱스
CREATE INDEX emp_upper_last_name_idx ON employees (UPPER(last_name));
인덱스 이름(emp_last_name_idx 등)과 테이블 이름(employees), 컬럼 이름(last_name 등)은 환경에 맞게 바꾸면 된다.
1.3 뷰 생성 (CREATE VIEW)
뷰는 하나 이상의 테이블에서 데이터를 선택적으로 보여주는 가상 테이블이다.
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
AS select_statement
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]];
예시:
-- 단순 뷰
CREATE VIEW emp_details_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.phone_number,
e.hire_date, j.job_title, e.salary, d.department_name
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.department_id = d.department_id;
-- 읽기 전용 뷰
CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000
WITH READ ONLY;
emp_details_view와 high_salary_employees는 뷰 이름이고, employees, jobs, departments는 테이블 이름이다. 이 부분은 실제 환경에 맞게 변경해야 한다.
1.4 시퀀스 생성 (CREATE SEQUENCE)
시퀀스는 순차적인 숫자를 자동으로 생성해주는 객체로, 주로 기본 키 값을 생성할 때 사용한다.
CREATE SEQUENCE sequence_name
[START WITH n]
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
예시:
-- 기본 시퀀스
CREATE SEQUENCE emp_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- 특정 범위를 순환하는 시퀀스
CREATE SEQUENCE order_number_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 9999
CYCLE
CACHE 20;
시퀀스 이름(emp_seq, order_number_seq)은 환경에 맞게 변경해야 한다.
1.5 트리거 생성 (CREATE TRIGGER)
트리거는 특정 이벤트(INSERT, UPDATE, DELETE 등)가 발생했을 때 자동으로 실행되는 프로시저다.
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
[OF column1, column2, ... column_n]
ON table_name
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
-- PL/SQL block
END;
예시:
-- 로그 테이블에 기록하는 트리거
CREATE OR REPLACE TRIGGER employees_audit_trg
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO employees_audit (employee_id, action_type, action_date)
VALUES (:new.employee_id, 'INSERT', SYSDATE);
ELSIF UPDATING THEN
INSERT INTO employees_audit (employee_id, action_type, action_date)
VALUES (:old.employee_id, 'UPDATE', SYSDATE);
ELSIF DELETING THEN
INSERT INTO employees_audit (employee_id, action_type, action_date)
VALUES (:old.employee_id, 'DELETE', SYSDATE);
END IF;
END;
/
employees_audit_trg는 트리거 이름이고, employees와 employees_audit은 테이블 이름이다. 이 부분은 실제 환경에 맞게 변경해야 한다.
2. ALTER 구문: 객체 변경하기
2.1 테이블 변경 (ALTER TABLE)
테이블 구조를 변경할 때 ALTER TABLE 명령어를 사용한다.
-- 열 추가
ALTER TABLE table_name
ADD (column_name datatype [DEFAULT value] [NOT NULL], ...);
-- 열 수정
ALTER TABLE table_name
MODIFY (column_name datatype [DEFAULT value] [NOT NULL], ...);
-- 열 삭제
ALTER TABLE table_name
DROP COLUMN column_name [CASCADE CONSTRAINTS];
-- 열 이름 변경
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
-- 제약 조건 추가
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column1, column2, ...);
-- 제약 조건 삭제
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
-- 제약 조건 활성화/비활성화
ALTER TABLE table_name
ENABLE | DISABLE CONSTRAINT constraint_name;
예시:
-- 열 추가
ALTER TABLE employees
ADD (commission_pct NUMBER(2,2), manager_id NUMBER(6));
-- 열 수정 (데이터 타입 변경)
ALTER TABLE employees
MODIFY (last_name VARCHAR2(50), salary NUMBER(10,2));
-- 제약 조건 추가
ALTER TABLE employees
ADD CONSTRAINT emp_mgr_fk FOREIGN KEY (manager_id)
REFERENCES employees (employee_id);
-- 제약 조건 비활성화
ALTER TABLE employees
DISABLE CONSTRAINT emp_mgr_fk;
예시에서 employees는 테이블 이름이고, commission_pct, manager_id, last_name, salary 등은 컬럼 이름이다. emp_mgr_fk는 제약 조건 이름이다. 이 부분들은 실제 환경에 맞게 변경해야 한다.
2.2 인덱스 변경 (ALTER INDEX)
인덱스의 물리적 속성을 변경할 때 사용한다.
-- 인덱스 사용 불가/사용 가능 설정
ALTER INDEX index_name UNUSABLE | REBUILD;
-- 인덱스 저장 파라미터 변경
ALTER INDEX index_name STORAGE (storage_parameters);
-- 인덱스 이름 변경
ALTER INDEX old_index_name RENAME TO new_index_name;
예시:
-- 인덱스 재구성
ALTER INDEX emp_last_name_idx REBUILD;
-- 인덱스 이름 변경
ALTER INDEX emp_last_name_idx RENAME TO employees_last_name_idx;
emp_last_name_idx와 employees_last_name_idx는 인덱스 이름으로, 실제 환경에 맞게 변경해야 한다.
2.3 시퀀스 변경 (ALTER SEQUENCE)
시퀀스의 속성을 변경할 때 사용한다.
ALTER SEQUENCE sequence_name
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
예시:
ALTER SEQUENCE emp_seq
INCREMENT BY 10
MAXVALUE 1000000
CACHE 50;
emp_seq는 시퀀스 이름으로, 실제 환경에 맞게 변경해야 한다.
3. DROP 구문: 객체 삭제하기
3.1 테이블 삭제 (DROP TABLE)
테이블을 완전히 삭제할 때 사용한다.
DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE];
CASCADE CONSTRAINTS 옵션은 해당 테이블의 기본 키나 고유 키를 참조하는 다른 테이블의 외래 키 제약 조건도 함께 삭제한다. PURGE 옵션은 휴지통을 건너뛰고 테이블을 완전히 삭제한다.
예시:
-- 단순 삭제 (휴지통으로 이동)
DROP TABLE temp_employees;
-- 제약 조건과 함께 삭제
DROP TABLE employees CASCADE CONSTRAINTS;
-- 휴지통 건너뛰고 완전 삭제
DROP TABLE old_employees PURGE;
temp_employees, employees, old_employees는 테이블 이름으로, 실제 환경에 맞게 변경해야 한다.
3.2 인덱스 삭제 (DROP INDEX)
인덱스를 삭제할 때 사용한다.
DROP INDEX index_name;
예시:
DROP INDEX emp_last_name_idx;
3.3 뷰 삭제 (DROP VIEW)
뷰를 삭제할 때 사용한다.
DROP VIEW view_name;
예시:
DROP VIEW emp_details_view;
3.4 시퀀스 삭제 (DROP SEQUENCE)
시퀀스를 삭제할 때 사용한다.
DROP SEQUENCE sequence_name;
예시:
DROP SEQUENCE emp_seq;
3.5 트리거 삭제 (DROP TRIGGER)
트리거를 삭제할 때 사용한다.
DROP TRIGGER trigger_name;
예시:
DROP TRIGGER employees_audit_trg;
4. TRUNCATE 구문: 테이블 데이터 삭제
TRUNCATE는 테이블의 모든 데이터를 빠르게 삭제하는 명령어다. DELETE와 달리 트랜잭션 로그를 생성하지 않아 성능이 더 좋지만, 롤백이 불가능하다.
TRUNCATE TABLE table_name [DROP | REUSE STORAGE];
예시:
TRUNCATE TABLE temp_data;
temp_data는 테이블 이름으로, 실제 환경에 맞게 변경해야 한다.
5. RENAME 구문: 객체 이름 변경
RENAME old_name TO new_name;
예시:
RENAME employees_backup TO employees_archive;
employees_backup와 employees_archive는 테이블 이름으로, 실제 환경에 맞게 변경해야 한다.
6. COMMENT 구문: 주석 추가
테이블이나 컬럼에 주석을 추가할 때 사용한다. 주석은 데이터 사전에 저장되어 문서화에 도움이 된다.
-- 테이블 주석
COMMENT ON TABLE table_name IS 'comment_text';
-- 컬럼 주석
COMMENT ON COLUMN table_name.column_name IS 'comment_text';
예시:
-- 테이블 주석
COMMENT ON TABLE employees IS '직원 정보를 저장하는 테이블';
-- 컬럼 주석
COMMENT ON COLUMN employees.employee_id IS '직원 고유 식별자';
COMMENT ON COLUMN employees.salary IS '직원 월급 (단위: 원)';
이번 포스트에서 오라클 데이터베이스의 주요 DDL 구문(CREATE, ALTER, DROP 등)의 사용법과 실무에서 활용할 수 있는 팁들을 알아봤다. DDL 구문은 그 영향력이 크기 때문에 신중하게 사용해야 할 필요성이 있다. 특히, 작업전에 꼭 백업을 해두어야 한다.
관련 태그 및 키워드: