SQL - DDL

데이터 딕셔너리

• 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블
• 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블

USER_TABLES : 사용자가 작성한 테이블을 확인하는 뷰

SELECT *
FROM USER_TABLES;​

DESC : 테이블의 구조 표시

DESC MEMBER;​

 

 

---

 

 

DDL ( Data Definition Language)

• 개념: 오라클의 5가지 객체를 생성, 수정, 삭제, 절삭 가능
• 5가지 객체
  1. 테이블(table) : 기본적인 데이터 저장 단위로 행과 열로 구성된 객체
  2. 인덱스(index) : 테이블에 젖아된 데이터의 검색 성능 향상 목적을 위한 객체
  3. 뷰(view) : 한 개 이상의 테이블의 논리적인 부분 집합을 표시할 수 있는 객체
  4. 시퀀스(sequence) : 테이블의 특정 컬럼값에 숫자 값 자동 생성 목적을 위한 객체
  5. 동의어(synonym) : 객체에 대한 동의어를 설정하기 위한 객체
• 테이블 생성

CTAS

CREATE TABLE
AS
SUBQUERY;​

일반적인 방법 
- 제약조건이 없기 때문에 모든 컬럼에 중복 가능하고 null도 가능하다.
- 저장할 때 컬럼명을 명시하지 않는 컬럼은 자동으로 null 저장됨.

CREATE TABLE 테이블명(
	컬럼명 데이터타입,
    	컬럼명 데이터타입,
    	컬럼명 데이터타입,
    ...
);​

CREATE TABLE 스키마.테이블명(       --원래 맞는 문법
	컬럼명 데이터타입,
    	컬럼명 데이터타입,
    	컬럼명 데이터타입,
    ...
);

- 데이터 타입 : 교안 P.247

  - 오라클에서는 한글 한글자 2byte : AL16UTF8
                                               3byte : AL32UTF8  
  - 한글 byte 확인

select *
from NLS_DATABASE_PARAMETERS
where parameter = 'NLS_CHARACTERSET';

                => AL32UTF8 ( 3byte )




일반적인 방법 2 - default 옵션
  - 컬럼명2에 명시적으로 값을 저장하지 않아도 자동으로 기본값으로 저장된다. ( null 저장 방지 가능 )
  - 날짜, 성별 같은 고정된 값만을 가지는 컬럼에 유용하게 사용

CREATE TABLE 테이블명(
	컬럼명1 데이터타입,
    	컬럼명2 데이터타입 DEFAULT 기본값,
    	컬럼명3 데이터타입,
    ...
);

일반적인 방법 3 - 제약조건 (constrains) ★★★★★
  - 사용자가 원하는 조건의 데이터를 유지하기 위해 특정 컬럼에 사용하는 제약 조건
  - 입력 데이터에 문제가 있는지, 데이터의 수정/삭제 가능 여부 등을 자동으로 검사하기 위해 사용 
  - USER_CONSTRAINTS : 사용자가 작성한 제약 조건을 확인하는 딕셔너리 뷰
  - USER_CONS_COLUMNS : 제약 조건이 걸려 있는 컬럼을 확인하는 딕셔너리 뷰
  - 제약조건 5개 종류 p.250

 

 

 

---

 

 

PRIMARY KEY 제약조건

- 해당 컬럼값이 반드시 유일해야 되는 UNIQUE 제약조건을 갖기 때문에 컬럼값을 중복해서 저장하면 에러가 발생된다.
- NOT NULL 제약조건도 가지고 있기 때문에 NULL값을 저장하려고 하면 에러가 발생된다.
- UNIQUE 제약조건과 NOT NULL 제약을 내부적으로 포함하는 제약조건이다.


- 컬럼레벨 방식
 : 컬럼을 지정할 때 제약 조건을 같이 지정하는 방식
                               5가지 제약조건을 모두 컬럼레벨 방식이 지원된다.

CREATE TABLE 테이블명(
	컬럼명1 데이터타입 CONSTRAINT 제약조건명   제약조건타입,   -- 권장함, 제약조건명으로 삭제 및 비활성화
    	                                                     -- 제약조건명: 테이블명_컬럼명_축약제약조건타입
        컬럼명2 데이터타입 제약조건타입,                     -- 권장안함, 자동으로 생성된 이름은 SYSXXX
        컬럼명3 데이터타입,
    ...
);​

  - 제약조건명 : '테이블명_컬럼명_pk'

- 테이블 레벨 방식
 : 컬럼을 모두 지정한 후에 가장 마지막에 제약조건을 따로 지정하는 방식
                                   4가지 제약조건을 모두 컬럼레벨 방식이 지원된다. ( not null 지원 안됨 )

CREATE TABLE 테이블명(
	컬럼명1 데이터타입 
        컬럼명2 데이터타입,                    
        컬럼명3 데이터타입,
        
    CONSTRAINT 제약조건명 제약조건타입 (컬럼명1)
);​

 

 

---

 

 

UNIQUE 제약 조건

- 하나의 테이블에 UNIQUE 제약조건을 여러 개 지정할 수 있다. (기본 키 제약조건과의 차이점1)

- NULL값을 저장할 수 있다. (기본 키 제약조건과의 차이점2)

컬럼 레벨

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] UNIQUE,     --제약조건명 : 테이블명_컬럼명_uk
  컬럼명 데이터타입,
  ...
  );​

테이블 레벨

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

 

 

 

 

---

 

 

 

NOT NULL 제약 조건

- 해당 컬럼에 널(null)값이 저장되는 것을 방지하는 제약조건

- 반드시 컬럼 레벨 방식으로만 사용이 된다.

컬럼 레벨 방식

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL,
컬럼명 데이터타입,
...
);​

 

 

 

---

 

 

CHECK 제약 조건

- CHECK 제약조건은 해당 컬럼에 저장되는 데이터를 검사하여 조건과 일치하는 데이터만 저장이 가능하도록 처리하는 제약조건

- 조건으로는 데이터의 값의 범위나 특정 값과 일 치하는 숫자 및 문자 데이터를 설정할 수 있다.

- SELECT문의 WHERE절에서 사용했던 IN 연산자, AND/OR 연산자, 비교 연산자등과 함께 사용이 가능하다.

컬럼 레벨 방식

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건식) , 
  컬럼명 데이터타입,
...
);​

테이블 레벨 방식

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, 
  컬럼명 데이터타입,
...,
[CONSTRAINT 제약조건명] CHECK(조건식)
);

 

 

 

---

 

 

FOREIGN KEY 제약 조건

- 해당 테이블에서 다른 테이블을 참조할 때 올바른 데이터값만 참조 가능하도록 제약하는 방법

- 참조 하는 부모 테이블의 컬럼은 반드시 기본 키(PRIMARY KEY) 또는 UNIQUE 키로 제약조건 이 설정된 컬럼 이어야 된다.

컬럼 레벨

CREATE TABLE 테이블명 (
	컬럼명1 데이터타입,
    	컬럼명2 데이터타입 CONSTRAINT 제약조건명 REFERENCES 마스터테이블명(pk컬럼 | uk컬럼명),
    	컬럼명3 데이터타입
);​

테이블 레벨

CREATE TABLE 테이블명 (
	컬럼명1 데이터타입,
    	컬럼명2 데이터타입,
    	컬럼명3 데이터타입
        ...,
CONSTRAINT 제약조건명 제약조건타입(컬럼명1)    
	REFERENCES 부모테이블명(컬럼명)
);​

 

 

 

---

 

* FOREIGN KEY 제약조건의 추가 옵션

   부모 테이블의 행 삭제시 문제가 될 수 있는 자식 테이블의 행 설정방법

 

ON DELETE CASCADE
- 참조되는 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 행도 같이 연쇄적으로 삭제되도록 한다.

CREATE TABLE emp02
( empno NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 ename VARCHAR2(15),
 deptno NUMBER(2) 
CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno) ON DELETE CASCADE
);​

ON DELETE SET NULL
- 참조되는 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 컬럼 값을 널(null)로 설정한다.

CREATE TABLE emp02
( empno NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 ename VARCHAR2(15),
 deptno NUMBER(2) 
CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno) ON DELETE SET NULL
);​