2-19) DDL

DDL (Data Definition Language)

데이터 정의어
데이터 구조 정의 (객체 생성, 삭제, 변경) 언어
CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제)
AUTO COMMIT (명령어 수행 후 즉시 저장, 원복 불가)

CREATE

테이블, 인덱스와 같은 객체를 생성하는 명령어
테이블 생성 시 테이블명, 컬럼명, 컬럼 순서, 컬럼 크기, 컬럼의 데이터 타입 정의 필수
테이블 생성 시 각 컬럼의 제약 조건 및 기본 값은 생략 가능
테이블 생성 시 소유자 명시 가능 (생략 시 명령어 수행 계정 소유)
숫자 컬럼의 경우 컬럼 사이즈 생략 가능 (날짜 컬럼은 사이즈 명시 X)

** 문법 1

소유자 명시는 테이블명 앞에 소유자명.테이블명 형태로

** 문법 2 (테이블 복제)

** 특징

복제 테이블의 컬럼명과 컬럼의 데이터 타입이 복제됨
SELECT 문에서 컬럼 별칭 사용 시 컬럼 별칭 이름으로 생성
CREATE 문에서 컬럼명 변경 가능
NULL 속성도 복제됨
테이블에 있는 제약조건, INDEX 등은 복제되지 않음
PRIMARY KEY 복제되지 않음

데이터 타입

CHAR(n) : 고정형, 빈자리수는 공백으로 채워짐
VARCHAR2(n) : 가변형, 사이즈보다 작은 수의 입력값 유지 (빈자리수 채우기 X)
NUMBER(p, s) : 소수점 자리 제한 시 s, 총 자리수 p
- 예) NUMBER(5, 2) : 111.23 형태
DATE : 사이즈 전달 불가

SQL Server)

VARCHAR2 → VARCHAR 사용
NUMBER → NUMERIC 사용
문자타입도 사이즈 생략 가능 (생략 시 1)

예1) NUMBER(7,2)의 경우 총 자리수가 7을 초과할 수 없음

예2) MERGE_OLD 테이블 만들기

예3) EMP 테이블 복제하여 TEST 테이블 만들기

예4) EMP 테이블 데이터 없이 구조만 복제

- WHERE 절에 항상 FALSE인 문장을 전달하여 데이터 SELECT 없이 구조만 복제!

예5) 테이블 복제 시 컬럼명 변경 가능

테이블 이름 옆 괄호에 컬럼명 새로 지정

ALTER

테이블 구조 변경 (컬럼명, 컬럼 데이터 타입, 컬럼 사이즈, default 값, 컬럼 삭제, 컬럼 추가, 제약 조건)
컬럼 순서 변경 불가 (재생성으로 해결!)

1. 컬럼 추가

새로 추가된 컬럼 위치는 맨 마지막
절대 중간 위치에 컬럼 추가 불가 → 순서 변경하고 싶으면 테이블 재생성하기
컬럼 추가 시 데이터 타입 필수, default 값, 제약 조건을 명시할 수 있음
여러 컬럼 동시 추가 가능 (반드시 괄호 사용!)

** 문법

예1) 동시에 여러 컬럼 추가 시 반드시 괄호와 함께 전달

각각 괄호 X, ADD (컬럼1 컬럼1데이터 타입, 컬럼2 컬럼2데이터타입) 형태

예2) 컬럼 추가 시 NOT NULL 속성 전달 불가

- 컬럼 추가 시 모두 NULL인 값을 갖고 추가되기 때문

** 컬럼 추가 시 DEFAULT를 선언하면 NOT NULL 속성을 갖는 컬럼 추가 가능

순서 주의 ! : DEFAULT 값 선언 이후 NOT NULL 속성 정의

2. 컬럼 변경

컬럼 사이즈, 데이터 타입, default 값 변경 가능
여러 컬럼 동시 변경 가능

** 문법

하나의 컬럼 변경 시 괄호 생략 가능

1) 컬럼 사이즈 변경

컬럼 사이즈 증가는 항상 가능
컬럼 사이즈 축소는 데이터 존재 여부에 따라 제한
- 데이터가 있는 경우, 데이터의 최대 사이즈만큼 축소 가능
동시 변경 가능 (괄호 필수!)

예) 여러 컬럼 사이즈 수정

최대 길이보다 크거나 같은 사이즈로 변경 가능

2) 데이터 타입 변경

빈 컬럼일 경우 데이터 타입 변경 가능
CHAR, VARCHAR 타입일 경우 데이터가 있어도 서로 변경 가능!

예1) 데이터 타입 변경

DEPTNO는 데이터가 없어서 변경 가능, SAL은 데이터 존재해서 변경 불가

예2) CHAR ↔ VRACHAR 데이터 타입 변경

데이터가 존재하더라도 VARCHAR2과 CHAR 사이 변경은 가능

3) DEFAULT 값 변경

DEFAULT 값이란 특정 컬럼에 값이 생략될 경우 (입력 시 언급 X) 자동으로 부여되는 값
INSERT 시 DEFAULT 값이 선언된 컬럼에 NULL을 직접 입력할 때는 DEFAULT 값이 아닌 NULL이 입력됨
이미 데이터가 존재하는 테이블에 DEFAULT 값 선언 시 기존 데이터 수정 안 됨 (이후 입력된 데이터부터 적용)
DEFAULT 값 해제 시 DEFAULT 값을 NULL로 선언

예) DEFAULT 값 변경 및 적용

NULL로 입력한 PARK는 NULL, SAL값을 입력 시 전달하지 않은 CHOI는 DEFAULT 값으로 설정됨
기존에 존재하던 데이터 (KIM)은 변경 X

4) 컬럼 이름 변경

항상 가능
동시 여러 컬럼 이름 변경 불가! (괄호 전달 불가)
ALTER ... RENAME 명령어로 처리

** 문법

RENAME COLUMN 컬럼명 TO NEW컬럼명

예) 컬럼 이름 변경

RENAME COLUMN ~
괄호 전달 불가
동시 변경 불가

5) 컬럼 삭제

데이터 존재 여부와 상관 없이 언제나 가능
RECYCLEBIN에 남지 X (FLASHBACK으로 복구 불가)
동시 삭제 불가
DROP COLUMN ~ 형태 (COLUMN 붙이기)

예1)

예2) 2개 이상 컬럼 동시 삭제 시도 시 에러 발생

괄호로 묶어서 전달 시에도 불가능

DROP

객체 (테이블, 인덱스 등) 삭제
DROP 후에는 조회 불가

** 문법

PURGE로 테이블 삭제 시 RECYCLEBIN에서 조회 불가

예)

이후 테이블 조회 시 테이블 존재하지 않아 에러 발생

TRUNCATE

구조를 남기고 데이터만 즉시 삭제, 즉시 반영 (AUTO COMMIT)
RECYCLEBIN에 남지 않음!

** 문법

TRUNCATE 이후 바로 테이블명 X
TRUNCATE TABLE 테이블명

예)

삭제 후 데이터 조회 시 구조는 남아있고 데이터는 삭제됨

DELETE / DROP / TRUNCATE 차이

DELETE : 데이터 일부 또는 전체 삭제, 롤백 가능
TRUNCATE : 데이터 전체 삭제만 가능 (일부 삭제 불가), 즉시 반영 (롤백 불가)
DROP : 데이터와 구조를 동시 삭제, 즉시 반영 (롤백 불가)

제약 조건

데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치
테이블 생성 시 정의 가능, 컬럼 추가 시 정의 가능, 이미 생성된 컬럼에 제약 조건만 추가 가능

1. PRIMARY KEY (기본키)

유일한 식별자
중복 허용 X, NULL 허용 X ≫ UNIQUE + NOT NULL
특정 컬럼에 PRIMARY KEY 생성하면 NOT NULL 속성 자동 부여
- CTAS로 테이블 복사 시 PRIMARY KEY는 복사되지 않음
- CTAS란 테이블 생성 시 기존 테이블 복제하여 생성하는 것 (CREATE TABLE T AS SELECT * FROM T2)
하나의 테이블에 여러 기본키 생성 불가
하나의 기본키를 여러 컬럼을 결합하여 생성 가능
PRIMARY KEY 생성 시 자동으로 UNIQUE INDEX 생성

** 문법

1) 테이블 생성 시 제약 조건 생성

CONSTRAINT로 제약조건명 정의 가능 (생략 가능 → 자동 생성)
순서는 DEFAULT 값 정의 먼저 → 제약조건 정의

2) 컬럼 추가 시 제약 조건 생성

3) 이미 생성된 컬럼에 제약 조건만 추가

ADD로 컬럼명 지정 없이 제약 조건 정의

4) 제약 조건 삭제

DROP CONSTRAINT 제약조건명 → 제약조건명으로 삭제 가능

예1) 테이블 생성 시 제약 조건 설정

이름 전달 없이)

CREATE 문 밑에)

CREATE 문 밑에 제약조건 이름과 함께 전달 가능!
PRIMARY KEY를 2개 이상으로 구성할 때에는 각 컬럼에 정의하지 않고 밑에 따로 정의하기

이름과 함께 전달 시)

예2) 컬럼 추가 시 제약 조건 생성

예3) 이미 있는 컬럼에 제약 조건만 생성

제약 조건 이름 생략도 가능

** 제약조건 생성 시에는 어떤 경우에서도 제약조건명 정의하지 않아도 됨

** 제약조건 삭제 시에는 제약조건명으로 삭제!

2. UNIQUE

중복을 허용하지 않음
NULL은 허용
UNIQUE INDEX 자동 생성

예) UNIQUE KEY가 생성된 컬러의 값 입력

NULL 입력은 자유롭지만 중복 값 입력 시 에러 발생

3. NOT NULL

다른 제약 조건과 다르게 컬럼의 특징을 나타냄 ≫ CTAS로 복제 시 따라감!
컬럼 생성 시 NOT NULL을 선언하지 않으면 Nullable 컬럼으로 생성됨 (DEFAULT)
이미 만들어진 컬럼에 NOT NULL 선언 시 제약 조건 생성이 아닌 컬럼 수정 (MODIFY)으로 해결

예)

ADD NOT NULL(컬럼명) 형태 불가
MODIFY 컬럼으로 NOT NULL 속성 추가

4. FOREIGN KEY

참조 테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성
반드시 참조(부모)테이블의 참조 컬럼이 사전에 PK 혹은 UNIQUE KEY를 가져야 함!

** 문법

예)

→ 참조 테이블의 참조대상에 PK 설정, 자식 테이블에 FOREIGN KEY 생성한 상태

TEST1) 자식 테이블에서 10번 부서원 삭제 시도

자식 테이블에서의 삭제는 자유로움

TEST2) 자식 테이블에서 20번 부서원 50번으로 변경 시도 (불가)

참조 테이블의 참조 컬럼에 존재하지 않는 데이터로 자식 테이블에서 수정 불가

TEST3) 자식 테이블에서 50번 부서원 입력 시도 (불가)

참조 테이블의 참조 컬럼에 존재하지 않는 데이터로 자식 테이블에서 입력 불가

TEST4) 부모 테이블에 20번 부서원 삭제 시도 (불가)

20번 부서를 참조하는 데이터가 자식 테이블에 존재하므로 삭제 불가

TEST5) 부모 테이블에 20번 부서원의 부서번호를 60번으로 변경 시도 (불가)

20번 부서를 참조하는 데이터가 자식 테이블에 존재하므로 수정 불가

FOREIGN KEY 옵션

ON DELETE CASECADE : 부모 데이터 삭제 시 자식 데이터 함께 삭제
ON DELETE SET NULL : 부모 데이터 삭제 시 자식 데이터의 참조 값은 NULL로 수정
생성 시 정의, 변경은 불가 ! → 변경하려면 재생성

예) FOREIGN KEY 옵션

부모 데이터 삭제 시 자식 데이터 함께 삭제

부모 데이터 삭제 시 자식 데이터 NULL로 설정

5. CHECK

직접적으로 데이터의 값 제한 (양수, (1,2,3,4) 중 하나 등)

예)

기타 오브젝트

1. 뷰 (VIEW)

저장 공간을 가지지 않지만 테이블처럼 조회 및 수정할 수 있는 객체

** 뷰 (VIEW)의 종류

단순뷰 : 하나의 테이블 조회 뷰
복합뷰 : 둘 이상의 테이블 조인 뷰

** 뷰 (VIEW)의 특징

뷰는 기본 테이블로부터 유도된 테이블
기본 테이블과 같은 형태의 구조를 가지고 있으며, 조작도 기본 테이블과 거의 같음
뷰는 가상의 테이블이기에 물리적으로 구현되어 있지않음
- 저장 공간을 차지하지 않음
데이터를 안전하게 보호 가능
이미 정의되어 있는 뷰는 다른 뷰의 정의에 기초가 될 수 있음
기본 테이블이 삭제되면 그 테이블을 참조하여 만든 뷰 역시 삭제됨

** 뷰 (VIEW)의 장점

논리적 독립성을 제공
데이터의 접근을 제어함으로써 보안 유지
사용자의 데이터 관리 단순화
데이터의 다양한 지원 가능

** 뷰 (VIEW)의 단점

뷰의 정의 변경 불가
삽입, 삭제, 갱신 연산에 제한
인덱스 구성 불가

** 문법

** 뷰 (VIEW)의 삭제

예)

복합뷰

2. 시퀀스 (SEQUENCE)

자동으로 연속적인 숫자 부여해주는 객체

** 문법

CYCLE 설정의 경우 재시작 값 정의 → MINVALUE, MAXVALUE
CACHE : 메모리에 미리 올려놓는 값

3. 시노님 (SYNONYM)

테이블 별칭 생성
예) HR 계정에서 SCOTT.EMP를 EMP로 조회하는 방법

** 문법

OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우 대체
PUBLIC : 시노님을 생성한 유저만 사용가능하지 않고 모두 사용 가능
- PRIVATE SYNONYM : 생성한 유저만 사용 가능
- PUBLIC을 붙이면 PUBLIC SYNONYM이 되는 것, 붙이지 않으면 PRIVATE
PUBLIC으로 생성한 시노님은 반드시 PUBLIC으로 삭제

예)