[DB/Oracle] DDL이란?

DDL이란?

DDL은 Data Definition Language의 약자로 데이터 정의어이다. 오라클에서 제공하는 객체를 만들고(CREATE), 변경하고(ALTER), 삭제하는(DROP) 등 실제 데이터 값이 아닌 데이터의 구조 자체를 정의하는 언어로 DB 관리자, 설계자가 주로 사용한다.

 

오라클에서의 객체는 테이블, 뷰, 시퀀스, 인덱스, 트리거, 프로시져, 함수, 사용자 등이 있다.

 

DDL 명령어 종류에는 CREATE, ALTER, DROP 등이 있다. DDL 명령어 실행 후에는 COMMIT할 필요가 없다.

 

DDL의 종류와 사용에 대해 알아보자

 

---

 

1. CREATE

데이터베이스의 객체를 생성하는 구문이다. Oracle 데이터베이스 객체의 대표적인 예는 테이블(TABLE)이다.

 

테이블은 행과 열로 구성되는 가장 기본적인 데이터베이스 객체로 데이터베이스 내에서 모든 데이터는 테이블에 저장된다.

 

CREATE의 문법

<테이블 생성>
        [문법]
            CREATE TABLE 테이블명 (
                칼럼명 자료형(크기) [DEFAULT 기본값] [제약조건],
                칼럼명 자료형(크기) [DEFAULT 기본값] [제약조건],
                ...
            );

 

예시 (DEFAULT, 제약조건 누락)

CREATE TABLE MEMBER(
    MEM_NO          NUMBER
  , ID              VARCHAR2(100)
  , PWD             VARCHAR2(100)
  , NICK            VARCHAR2(100)
  , QUIT_YN         CHAR(1)
  , ENROLL_DATE     TIMESTAMP
  , CONSTRAINT MEM_NICK_UQ_ABCDE UNIQUE(NICK)
);

 

• MEM_NO
• ID
• PWD
• NICK
• QUIT_YN
• ENROLL_DATE

위의 칼럼명을 가진 'MEMBER'란 이름의 테이블 만들었다. 위에 예시에는 [DEFAULT 기본값]과 [제약조건]이 들어가 있지 않다. MEMBER 테이블에 들어가면 아래와 같은 테이블을 확인할 수 있다.

< MEMBER 테이블 >

 

---

 

제약조건이란?

제약조건(CONSTRAINT)은 사용자가 원하는 조건의 데이터만 유지하기 위해서 설정하는 조건이다. 테이블 작성 시 각 칼럼에 대해 저장될 값에 대한 제약조건을 설정할 수 있다.

 

제약조건은 데이터 무결성 보장을 목적으로 한다. 데이터 무결성 보장이란 데이터의 정확성과 일관성을 유지시키는 것이다.

 

제약조건 종류

• NOT NULL
  • 해당 칼럼에 반드시 값이 있어야만 하는 경우 사용한다.
  • 삽입/수정 시 NULL 값을 허용하지 않도록 제한한다.
• UNIQUE
  • 칼럼의 입력 값에 중복 값을 제한한다.
  • 데이터를 삽입/수정 시, 기존에 있는 데이터 값 중에 중복되는 값이 있을 경우 삽입/수정되지 않는다.
  • 제약조건 지정 방식으로 칼럼 레벨, 테이블 레벨 방식 모두 사용 가능하다.
• CHECK
  • 칼럼에 기록되는 값에 조건을 설정하고 조건을 만족하는 값만 기록할 수 있다.
  • 비교 값은 변하는 값이나 함수 사용하지 못하고, 리터럴만 사용 가능하다.

[문법]
            CHECK(비교연산자)
                CHECK(칼럼 [NOT] IN(값, 값, ...))
                CHECK(칼럼 = 값)
                CHECK(칼럼 BETWEEN 값 AND 값)
                CHECK(칼럼 LIKE '_문자' OR 칼럼 LIKE '문자%')
                ...

 

• PRIMARY KEY (기본 키)
  • 기본 키의 제약조건
    • NOT NULL 제약조건 만들어짐
    • UNIQUE 제약조건 만들어짐
    • INDEX 만들어줌
    • 테이블 대표칼럼에 설정 (보통 인위적 식별자로 만듦)
    • 한 테이블에 한 개만 설정할 수 있다.  (단, 한 개 이상의 칼럼을 묶어서 PRIMARY KEY로 제약조건을 설정할 수 있다.)
  • 테이블에서 한 행의 정보를 식별하기 위해 사용할 칼럼에 부여하는 제약조건이다.
  • 각 행들을 구분할 수 있는 식별자 역할 (사번, 부서 코드, 직급 코드, ..)
  • 기본 키 제약조건을 설정하게 되면 자동으로 해당 칼럼에 NOT NULL + UNIQUE 제약조건이 설정된다.
  • 칼럼 레벨, 테이블 레벨 방식 모두 설정 가능하다.
• FOREIGN KEY (외래 키)
  •  다른 테이블에 존재하는 값만을 가져야 하는 칼럼에 부여하는 제약조건이다. (단, NULL 값도 가질 수 있다.)
  • 즉, 참조된 다른 테이블이 제공하는 값만 기록할 수 있다. (FOREIGN KEY 제약조건에 의해서 테이블 간에 관계가 형성된다.)

[문법]
            1) 칼럼 레벨
                칼럼명 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조할테이블명 [(기본키)] [삭제룰]
                
            2) 테이블 레벨
                [CONSTRAINT 제약조건명] FOREIGN KEY(칼럼명) REFERENCES 참조할테이블명 [(기본키)] [삭제룰]

[삭제룰]
            부모 테이블의 데이터가 삭제됐을 때의 옵션을 지정해 놓을 수 있다.
            1) ON DELETE RESTRICT : 자식 테이블의 참조 키가 부모 테이블의 키 값을 참조하는 경우 부모 테이블의 행을 삭제할 수 없다. (기본적으로 적용되는 옵션)
            2) ON DELETE SET NULL : 부모 테이블의 데이터가 삭제 시 참조하고 있는 자식 테이블의 컬럼 값이 NULL로 변경된다.
            3) ON DELETE CASCADE  : 부모 테이블의 데이터가 삭제 시 참조하고 있는 자식 테이블의 컬럼 값이 존재하는 행 전체가 삭제된다.

 

제약조건 문법

문법]
            1) 칼럼 레벨
                CRATE TABLE 테이블명 (
                    칼럼명 자료형(크기) [CONSTRAINT 제약조건명] 제약조건,
                    ...
                );
            
            2) 테이블 레벨
                CRATE TABLE 테이블명 (
                    칼럼명 자료형(크기),
                    ...,
                    [CONSTRAINT 제약조건명] 제약조건(칼럼명)
                );

 

예시 (DEFAULT, 제약조건 포함)

CREATE TABLE MEMBER(
    MEM_NO          NUMBER PRIMARY KEY
  , ID              VARCHAR2(100)   CONSTRAINT MEM_ID NOT NULL
  , PWD             VARCHAR2(100)
  , NICK            VARCHAR2(100)
  , QUIT_YN         CHAR(1)         DEFAULT 'N' CONSTRAINT QUIT CHECK(QUIT_YN IN('Y','N'))
  , ENROLL_DATE     TIMESTAMP
  , CONSTRAINT MEM_NICK_UQ_ABCDE UNIQUE(NICK)
);

• MEM_NO 칼럼을 PRIMARY KEY
• ID 칼럼에 NOT NULL이라는 제약조건
• QUIT_YN 칼럼에 DEFAULT 값 'N'과 CHECK라는 제약조건

따라서 ID 칼럼에는 NULL 값이 들어오지 못하고, QUIT_YN에는 디폴트로 'N' 값이 들어온다. 또한 QUIT_YN에는 'Y'와 'N' 값 외에는 다른 값을 입력할 수 없다.

 

그리고 PRIMARY KEY는 NOT NULL과 UNIQUE의 제약조건이 포함되어 있다. MEMBER 테이블의 '제약조건'에 들어가면 아래와 같은 결과를 확인할 수 있다.

< MEMBER 테이블 >

 

MEM_ID는 PRIMARY KEY로 NOT NULL 조건이 들어가 있고, QUIT_YN에는 'Y', 'N'만 들어갈 수 있게 CHECK한다.

 

< MEMBER 테이블 >

 

QUIT_YN이 'N'의 디폴트 값을 갖는 것 또한 확인할 수 있다.

 

---

 

칼럼에 주석 달기

[문법]
            COMMENT ON COLUMN 테이블명.칼럼명 IS '주석내용';

 

칼럼에 주석 다는 문법은 위와 같다.

COMMENT ON COLUMN MEMBER.ID IS '아이디';
COMMENT ON COLUMN MEMBER.PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.NICK IS '닉네임';
COMMENT ON COLUMN MEMBER.QUIT_YN IS 'Y: 탈퇴회원 , N: 일반회원';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS '가입일';

 

해당 문법을 사용하여 MEMBER 테이블에 위와 같이 주석을 달아보면

 

위와 같이 COMMENTS 부분에 해당 주석들이 달린 것을 확인할 수 있다.

 

---

 

2. DROP

DROP은 오라클 데이터베이스 객체를 삭제할 수 있게 한다. 사용법이 간단하기에 바로 예시를 보자.

 

예시

DROP TABLE MEMBER;

 

해당 쿼리를 실행하고, 

SELECT *
FROM MEMBER
;

 

위에 SELECT문을 실행해보면,

 

MEMBER 테이블이 삭제된 것을 확인할 수 있다.

 

---

 

3. 데이터 딕셔너리란?

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

 

데이터에 관한 데이터가 저장되어 있다고 해서 메타 데이터라고도 한다.

 

• 데이터 딕셔너리 종류
  • USER_TABLES
    • 사용자가 가지고 있는 테이블들의 전반적인 구조를 확인하는 뷰 테이블이다.
  • USER_TAB_COLUMNS
    • 테이블, 뷰의 칼럼과 관련된 정보를 조회하는 뷰 테이블이다.

 

예시

SELECT *
FROM USER_TAB_COLUMNS
;

< USER_TAB_COLUMNS >

 

위에 USER_TAB_COLUMNS를 보면, 해당 계정에 들어있는 전체 테이블의 모든 칼럼을 조회할 수 있다.

SELECT *
FROM USER_TABLES
;

< USER_TABLES >

 

위에 USER_TABLES를 보면, 해당 계정에 들어있는 전체 테이블을 조회할 수 있다.