DB (6) DDL(CREATE)

▶ DDL

DDL(DATE DEFINITION LANGUAGE) : 데이터 정의 언어

- 오라클에서 제공하는 객체(OBJECT)를 새로이 만들어(CREATE) 구조를 변경하고(ALTER) 구조를 삭제(DROP)하는 명령문이다. 즉, 구조자체를 정의하는 언어로 DB관리자, 설계자가 사용한다.

 

오라클에서의 객체(DB를 이루는 구조물들)

- 테이블(TABLE), 사용자(USER), 함수(FUNCTION), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스 등등..

 

▶ CREATE TABLE

테이블

- 행(ROW), 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체 종류 중 하나이다.
- 모든 데이터는 테이블을 통해서 저장된다.(데이터를 조작하고자 하려면 무조건 테이블을 만들어야 한다.)

[표현법]
CREATE TABLE 테이블명 (
컬럼명 자료형,
컬럼명 자료형,
컬럼명 자료형,
...
)

 

■ 자료형

CHAR(바이트 수)	최대 2000BYTE까지 지정가능
	고정길이(아무리 적은 값이 들어와도 공백으로 채워서 처음 할당한 크기를 유지하겠다.)
	주로 들어온 값의 글자수가 정해져 있을 경우 사용
VARCHAR2(바이트 수)	최대 4000BYTE까지 지정가능
	가변길이(적은 값이 들어올 경우 그 담긴 값에 맞춰서 크기가 줄어든다.)
	주로 들어올 값의 글자수가 정해져있지 않은 경우 사용한다.
숫자 (NUMBER)	정수/실수 상관없이 NUMBER
날짜 (DATE)	년/월/일/시/분/초 형식으로 시간을 지정

 

■ 테이블 및 컬럼 확인 방법

● 회원들의 데이터를 담기 위한 테이블 생성

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20), -- 대소문자 구문 X, 낙타등 표기법을 쓸 수 없다. -> 언더바로 구분한다.
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20),
    MEMBER_BDATE DATE
);

 

● 테이블 확인 방법 1

SELECT *
FROM MEMBER;

 

● 테이블 확인 방법 2 : 데이터 딕셔너리 이용

데이터 딕셔너리   : 다양한 객체들의 정보를 저장하고 있는 시스템 테이블

SELECT *
FROM USER_TABLES;

- USER_TABLES : 현재 이 사용자 계정이 가지고 있는 테이블들의 전반적인 구조를 확인할 수 있는 데이터 딕셔너리

 

● 컬럼들 확인하는 방법

SELECT *
FROM USER_TAB_COLUMNS;

 

▶ 컬럼에 코멘트 달기(컬럼에 대한 설명)

[표현법]
COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

ex)

-- MEMBER_ID : 회원아이디
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';

-- MEMBER_PWD : 회원비밀번호
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원비밀번호';

-- MEMBER_NAME : 회원이름
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';

-- MEMBER_BDATE : 생년월일
COMMENT ON COLUMN MEMBER.MEMBER_BDATE IS '생년월일';

 

▶ 제약조건 CONSTRAINTS

- 원하는 데이터값만 유지하기 위해서(보관하기 위해서) 특정 컬럼마다 설정하는 제약(데이터 무결성 보장을 목적으로)
- 제약조건이 부여된 컬럼에 들어올 데이터에 문제가 있는지 없는지 자동으로 검사할 목적
- 종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY
- 컬럼에 제약조건을 부여하는 방식 : 컬럼레벨방식 / 테이블레벨방식

 

■ NOT NULL 제약조건

- 해당 컬럼에 반드시 값이 존재해야만 할 경우 사용한다.

- 즉 NULL값이 절대 들어와서는 안 되는 컬럼에 부여하는 제약조건, 삽입/수정 시 NULL값을 허용하지 않도록 제한하는 제약조건

- 컬럼레벨 방식으로만 등록이 가능하다.

ex)

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

 

■ UNIQUE 제약조건

- 컬럼에 중복값을 제한하는 제약조건
- 삽입/수정 시 기존에 해당 컬럼값 중에 중복값이 있을 경우 추가, 수정이 되지 않게 제약한다.
- 컬럼/테이블레벨방식 둘 다 가능하다.

ex)

-- 컬럼레벨방식
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, -- 컬럼레벨방식
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

-- 테이블 레벨 방식 : 모든 컬럼을 다 기술하고 그 이후에 제약조건을 나열한다.
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    UNIQUE(MEM_ID) -- 테이블 레벨 방식
);

※ 제약조건시 부여시 직접 제약조건명을 지정해주지 않으면 시스템에서 임의의 제약조건명을 부여해 준다.

 

■ 제약조건 부여 시 제약조건명도 지정하는 표기법

컬럼레벨방식
CREATE TABLE 테이블명(
        컬럼명 자료형 제약조건1 제약조건2,
        컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,
        ...
);

테이블레벨방식
CREATE TABLE 테이블명(
        컬럼명 자료형,
        ...
        CONSTRAINT 제약조건이름 제약조건(컬럼명)
);

- 두 방식 모두 CONSTRAINT 제약조건이름은 생략이 가능했다.(시스템이 랜덤한 이름을 부여해 줌)

ex)

CREATE TABLE MEM_CON_NM(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) CONSTRAINT MEM_NAME_NN NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID) -- 테이블 레벨 방식
);

 

■ CHECK 제약조건

- 컬럼에 기록될 수 있는 값에 대한 조건을 설정할 수 있다.

[표현법]
CHECK (조건식)

ex)

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE NOT NULL,
    UNIQUE(MEM_ID) -- 테이블 레벨 방식
);

 

■ DEFAULT 설정

- 특정 컬럼에 들어올 값에 대한 기본값 설정 기능(제약조건은 아니다)

- 지정이 안된 컬럼에는 기본적으로 NULL값이 들어간다.
- 만약 DEFAULT값이 부여되어 있다면 NULL값이 아닌 DEFAULT값이 들어가게 된다.

ex)

-- 회원가입일을 항상 SYSDATE로 받고 싶은경우
CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 먼저 와야한다.
    UNIQUE(MEM_ID) -- 테이블 레벨 방식
);

 

■ PRIMARY KEY(기본키) 제약조건

- 테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 컬럼에 부여하는 제약조건
   => 각 행들을 구분할 수 있는 식별자의 역할

- 주의사항 : 한 테이블당 한개의 컬럼만 지정이 가능하다.

ex)

CREATE TABLE MEM_PRIMARYKEY1(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY, -- PRIMARY KEY 설정
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
    UNIQUE(MEM_ID)
);

 

- PRIMARY KEY가 한테이블에 2개이상 사용될 수 없다.

- 단, 두 컬럼을 하나로 묶어서 하나의 PRIMARY KEY로는 설정이 가능하다.
- 두가지 컬럼을 묶어서 PRIMARY KEY로 설정했을경우 => 복합키

ex)

CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT MEM_PK2 PRIMARY KEY(MEM_NO, MEM_ID) -- PRIMARY KEY 복합키
);

 

■ FOREIGN KEY(외래키)

- 해당 컬럼에 다른 테이블에 존재하는 값만 들어와야하는 컬럼에 부여하는 제약조건
   => "다른테이블(부모테이블)을 참조한다" 라고 표현

- 즉, 참조된 다른 테이블(부모테이블)이 제공하고 있는 값만 들어올 수 있다.

[표현법]
컬럼레벨방식
컬럼명 자료형 CONSTRAINT 제약조건명 REFERENCES 참조할테이블명(참조할컬럼명)

테이블레벨방식
CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)

// 참조할테이블 == 부모테이블

- 주의사항 : 참조할 컬럼의 타입(부모테이블컬럼), 외래키로 지정할 컬럼타입(자식테이블컬럼)이 같아야 한다.

 

● 자식테이블 생성시 외래키 제약조건을 부여했을 때

- 부모테이블에 데이터가 삭제되었을 때 자식테이블에서는 어떻게 처리할지를 옵션으로 정해둘 수 있다.

FOREIGN KEY 삭제 옵션

- ON DELETE SET NULL : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 NULL로 바꾸겠다.
- ON DELETE CASCADE  : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 같이 삭제하겠다.
- ON DELETE RESTRICTED : 삭제를 제한하겠다.(기본옵션)