[SQLD] DDL

DDL

DDL(Data Definition Language)

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

CREATE

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

CREATE문 문장1

CREATE문 문장2

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

데이터 타입

• SQL Server 의 경우도 유사, VARCHAR2 -> VARCHAR 사용, NUMBER -> NUMERIC 사용
• SQL Server 의 경우 문자타입도 사이즈 생략 가능(생략 시 1)

테이블 구조 복제 예시

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

항상 거짓인 조건을 SELECT절에 전달하면 데이터는 아무것도 출력되지 않지만 컬럼 정보는 출력된다.

따라서 테이블 내용은 제외하고 구조만 복제할 경우 주로 사용된다.

 

ALTER

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

1. 컬럼 추가

• 새로 추가된 컬럼 위치는 맨 뒤
• 컬럼 추가 시 데이터 타입 명시 필수
  • default 값, 제약조건을 명시할 수 있음
• 여러 컬럼 동시 추가 가능
  • 반드시 괄호 사용
• 테이블 생성 후 데이터가 쌓인 중간에 컬럼을 추가하는 경우, NOT NULL 속성 전달 불가능
  • 컬럼 추가 시 모두 NULL인 값을 가지고 추가됨
  • 단, 컬럼 추가 시 DEFAULT를 선언하면 NOT NULL 속성을 갖는 컬럼 추가 가능

컬럼 추가 문장

NOT NULL 속성을 갖는 컬럼 추가 예시

2. 컬럼(속성) 변경

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

컬럼 수정 문장

괄호는 생략 가능

 

2-1) 컬럼 사이즈 변경

• 컬럼 사이즈 증가는 항상 가능
• 컬럼 사이즈 축소는 데이터 존재 여부에 따라 제한
  • 테이블에 데이터가 존재하는 경우 데이터의 최대 사이즈만큼 축소 가능 (최대 사이즈보다 크거나 같은 사이즈로 제한)
• 동시 변경 가능하며, 반드시 괄호 필수

여러 컬럼 사이즈 수정 예시

2-2) 데이터 타입 변경

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

데이터 타입 변경 예시 (테이블 구조)

데이터 타입 변경 예시 문장

2-3) DEFAULT 값 변경

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

DEFAULT 값 변경 예시

3. 컬럼 이름 변경

• 항상 가능
• 동시에 여러 컬럼 이름 변경 불가
  • 괄호 전달이 불가능함

컬럼 이름 변경 문장

컬럼 이름 변경 예시

4. 컬럼 삭제

• 데이터 존재 여부와 상관 없이 언제나 가능
• RECYCLEBIN에 남지 않음
  • FLASHBACK으로 복구 불가
• 동시 삭제 불가

컬럼 삭제 예시

 

DROP

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

DROP 문장

 

TRUNCATE

• 구조(테이블명, 컬럼 등)만 남기고 데이터만 즉시 삭제
  • AUTO COMMIT으로, 즉시 반영
• RECYCLEBIN에 남지 않음

TRUNCATE 문장

 

DELETE / DROP / TRUNCATE 차이

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

---

제약조건

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

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

컬럼 추가 시 제약조건 생성

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

제약조건 삭제

1. PRIMARY KEY (기본 키)

• 유일한 식별자(각 행을 구별할 수 있는 식별자 기능)
• 중복 허용 X, NULL 허용 X
  • UNIQUE + NOT NULL
• 특정 컬럼에 PRIMARY KEY를 생성하면 NOT NULL 속성도 자동 부여
• 하나의 테이블에 여러 기본키를 생성할 수 없음
• 하나의 기본 키를 여러 컬럼을 결합하여 생성할 수 있음
• PRIMARY KEY 생성 시 자동으로 UNIQUE INDEX 생성

2. UNIQUE

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

3. NOT NULL

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

4. FOREIGN KEY

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

FOREIGN KEY 문장

옵션

생성 시 정의하며, 변경이 불가능하므로 재생성 해야 함.

1. ON DELETE CASCADE
   1. 부모 데이터 삭제 시 자식 데이터와 함께 삭제
2. ON DELETE SET NULL
   1. 부모 데이터 삭제 시 자식 데이터의 참조 값은 NULL로 수정

5. CHECK

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

EMP_TEST1 테이블의 SAL 값은 0이상이어야 한다는 CHECK 제약 조건 예시

---

기타 오브젝트

1. VIEW(뷰)

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

VIEW 문장

VIEW 종류

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

VIEW 특징

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

VIEW 삭제 문장

VIEW 장점

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

VIEW 단점

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

2. SEQUENCE(시퀀스)

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

SEQUENCE 문장

3. SYNONYM(시노님)

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

SYNONYM 문장

• OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우 대체
• PUBLIC : 시노님을 생성한 유저만 사용 가능한 PRIVATE SYNONYM 의 반대(누구나 사용가능)
• PUBLIC 으로 생성한 시노님은 반드시 PUBLIC 으로 삭제