DDL

DDL(Data Definition Language, 데이터 정의 언어)란 오라클에서 제공하는 객체를 새로 만들고(CREATE), 구조를 변경하고(ALTER), 구조자체를 삭제(DELETE)하는 언어, 즉, 실제 데이터 값이 아닌 규칙 자체를 정의하는 언어를 의미합니다.

오라클에서의 객체(구조)란?
테이블, 뷰, 시퀀스, 인덱스, 패키지, 트리거, 프로시저, 함수, 동의어, 사용자 등 모두

CREATE

객체를 새로 생성합니다.

1. 테이블 생성

테이블

행과 열로 구성되는 가장 기본적인 데이터베이스 객체로, 모든 데이터들은 테이블을 통해 저장됩니다.
이를 생성하는 방법은 아래와 같습니다.

CREATE TABLE 테이블명(
    컬럼명 자료형(크기),
    컬럼명 자료형(크기),
    컬럼명 자료형,
    ...
)

자료형

• 문자(CHAR(바이트크기) | VARCHAR2(바이트크기))

  • CHAR : 최대 2000바이트까지 지정가능, 고정길이
    (지정한 크기보다 더 작은 값이 들어오면 공백으로라도 채워서 지정한 크기로 생성)
  • VARCHAR2 : 최대 4000바이트까지 지정가능, 가변길이
    (담긴 값에 따라서 공간 크기가 맞춰짐)
    

• 숫자(NUMBER)

• 날짜(DATE)

회원에 대한 데이터를 담기위한 테이블 MEMBER 생성을 예시로 보면 아래와 같습니다.

CREATE TABLE MEMBER(
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20),
    MEM_PWD VARCHAR2(20),
    MEM_NAME VARCHAR2(20),
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    MEM_DATE DATE
);

데이터 딕셔너리

다양한 객체들의 정보를 저장하고 있는 시스템 테이블로 사용자가 아닌 시스템 측에서 별도로 관리하는 테이블입니다. 해당 RDBMS 권한에는 어떤 것이 있는지, 기능에는 어떤 것이 있는지 확인 가능합니다.

SELECT * FROM USER_TABLES;
SELECT * FROM USER_TAB_COLUMNS;

2. 칼럼에 주석달기

컬럼에 대한 간단한 설명입니다.

COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

MEMBER 테이블에 대해 각 칼럼에 주석을 달아보면 다음과 같습니다.

COMMENT ON COLUMN MEMBER.MEM_NO IS '회원번호';
COMMENT ON COLUMN MEMBER.MEM_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEM_PWD IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEM_NAME IS '회원명';
COMMENT ON COLUMN MEMBER.GENDER IS '성별(남/여)';
COMMENT ON COLUMN MEMBER.PHONE IS '전화번호';
COMMENT ON COLUMN MEMBER.EMAIL IS '이메일';
COMMENT ON COLUMN MEMBER.MEM_DATE IS '회원가입일';

이 때, 이 테이블에 값을 넣고 빼려면 INSERT와 DELETE와 같은 DML을 사용해야합니다. 이 DML이 무엇인지는 다음 포스팅에서 자세히 설명 예정이지만, 간단히만 정리하겠습니다.

테이블에 데이터를 추가시키는 구문은 INSERT를 사용합니다. 표현 방법은 아래와 같습니다.

INSERT INTO 테이블명 VALUES(값, 값, 값, 값, 값, 값, 값)

INSERT INTO MEMBER
VALUES(1, 'USER1', 'PASS1', '홍길동', '남', '010-1111-2222', 'aaaa@naver.com', '24/02/23');

INSERT INTO MEMBER
VALUES(2, 'USER2', 'PASS2', '홍길동', NULL, NULL, NULL, SYSDATE);

INSERT INTO MEMBER
VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

이렇게 값을 대입할 수 있습나다. 그런데 여기에 자료형만 맞다면 NULL이든 뭐든 다 들어가는 것 같습니다. 제약 조건 거는 방법을 알아보겠습니다.

3. 제약 조건

제약 조건은 원하는 데이터값(유효한 형식의 값)만 유지하기 위해서 특정 컬럼에 설정하는 제약으로 데이터 무결성 보장을 목적으로 합니다.

종류로는 NOT NULL, UNIQUE, CHECK, PRIMARY, FOREIGN KEY 등이 있습니다.

오라클에서는 제약조건을 부여하는 방식은 크게 2가지(컬럼레벨방식 / 테이블레벨방식)가 있습니다. 각 명령어들에 대해서, 그리고 그 방식에 대해서 알아보겠습니다.

NOT NULL

해당 컬럼에 반드시 값이 존재해야만 할 경우(절대 NULL이 들어오면 안되는 경우) 삽입 및 수정시 NULL값을 허용하지 않도록 제한합니다.

그리고 NOT NULL 제약조건은 컬럼레벨방식으로만 가능합니다.

우선 이를 확인하기 위해 새로운 테이블 MEM_NOTNULL을 만들어 보겠습니다.

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(13),
    EMAIL VARCHAR2(50)
);

각 데이터들을 넣어보겠습니다.

INSERT INTO MEM_NOTNULL
VALUES(1, 'USER1', 'PASS1', '홍길동', '남', '010-1111-2222', 'aaaa@naver.com');

INSERT INTO MEM_NOTNULL
VALUES(2, 'USER2', 'PASS2', '홍길순', NULL, NULL, NULL);

지금까지는 제약조건이 없어서 오류가 발생하지 않습니다. 하지만 MEM_ID와 같이 NOT NULL 제약조건이 걸려있으면 달라집니다. 아래 쿼리는 오류가 발생합니다.

INSERT INTO MEM_NOTNULL
VALUES(3, NULL, 'PASS2', '홍길순', NULL, NULL, NULL);
--NOT NULL 제약조건에 위배되어 오류 발생

하지만, 문제가 있는데, 중복인 데이터는 잘 들어가는 걸 볼 수 있습니다. 아래는 아이디가 중복이지만 데이터는 잘 저장이 되기에 문제가 되는 쿼리입니다.

INSERT INTO MEM_NOTNULL
VALUES(3, 'USER2', 'PASS2', '홍길순', NULL, NULL, NULL);

UNIQUE

중복을 제한하는 것이 바로 UNIQUE입니다. 삽입 및 수정시 기존에 있는 데이터 값 중 중복값이 있을 경우 오류를 발생시킵니다.

바로 새로운 테이블 MEM_UNIQUE를 만들어 확인 해보겠습니다.

아래 구문에서는 컬럼레벨방식으로 NOT NULL 바로 오른쪽에 작성하는 방법, 모든 컬럼을 작성 후 아래에 적어주는 테이블레벨방식(제일 아래 주석부분)을 각각 작성해놓았습니다.

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(13),
    EMAIL VARCHAR2(50)
    -- UNIQUE(MEM_ID) -- 테이블레벨방식
);

각 데이터를 넣어보겠습니다.

INSERT INTO MEM_UNIQUE
VALUES(1, 'USER1', 'PASS1', '홍길동', '남', '010-1111-2222', 'aaaa@naver.com');

하지만 같은 아이디가 있는 데이터는 오류가 뜹니다. 즉 아래 쿼리는 오류가 발생합니다.

INSERT INTO MEM_UNIQUE
VALUES(2, 'USER1', 'PASS2', '홍길순', '여', NULL, NULL);

제약 조건명

중복조건으로 오류가 발생하는 바로 전 쿼리는 아래와 같은 결과를 볼 수 있습니다.

오류 보고 -
ORA-00001: unique constraint (XX.SYS_C007078) violated

이 오류 보고를 보면 다음과 같은 특징이 있는 걸 볼 수 있습니다.

• 오류구문을 제약조건명으로 알려줍니다.
• 어떤 컬럼에서 오류가 발생했는지는 쉽게 파악하기는 어렵습니다.
• 제약 조건 부여시 특별히 지정해주지 않으면 시스템에서 이름(SYS_C00...)을 부여합니다.

그럼 칼럼별로 제약 조건명을 만들어주면 오류를 파악하기 쉬울 것입니다. 다음과 같이 생성합니다.

컬럼레벨방식

CREATE TABLE 테이블명(
	컬럼명 자료형 (CONSTRAINTE 제약조건명) 제약조건
)

테이블레벨방식

CREATE TABLE 테이블명(
    컬럼명 자료형,
    컬럼명 자료형,
    (CONSTRAINTE 제약조건명) 제약조건(컬럼명)
)

이를 토대로 다시 MEM_UNIQUE 테이블을 만들어보면 아래와 같습니다. 단, 기존 테이블은 DROP을 사용해 삭제해야됩니다.

DROP TABLE MEM_UNIQUE; -- 기존 테이블 삭제

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER CONSTRAINT MEMNO_NT NOT NULL,
    MEM_ID VARCHAR2(20) CONSTRAINT MEMID_NT NOT NULL, -- 컬럼레벨방식
    MEM_PWD VARCHAR2(20) CONSTRAINT MEMPWD_NT NOT NULL,
    MEM_NAME VARCHAR2(20) CONSTRAINT MEMNAME_NT NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    CONSTRAINT MEMID_UQ UNIQUE(MEM_ID) -- 테이블레벨방식
);

CHECK

CHECK(조건식)과 같은 형태로 해당컬럼에 들어올 수 있는 값에 대한 조건을 제시해둘 수 있습니다. 즉, 해당조건에 만족하는 데이터값만 담길 수 있습니다.

MEM_CHECK 테이블을 만들어보겠습니다.

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(13),
    EMAIL VARCHAR2(50),
    UNIQUE(MEM_ID)
    -- CHECK(GENDER IN ('남', '여'))
);

데이터도 넣어보겠습니다.

INSERT INTO MEM_CHECK
VALUES(1, 'USER1', 'PASS1', '홍길동', '남', '010-1111-2222', 'aaaa@naver.com');

INSERT INTO MEM_CHECK
VALUES(2, 'USER2', 'PASS2', '홍길순', 'ㅇ', NULL, NULL);
-- CHECK 제약조건때문에 에러가 발생
-- 만일 GENDER컬럼에 데이터를 넣고자 한다면 CHECK 제약조건에 만족하는 값을 삽입

INSERT INTO MEM_CHECK
VALUES(2, 'USER2', 'PASS2', '홍길순', NULL, NULL, NULL);
-- NULL은 값이 없다는 뜻이기 때문에 가능

PRIMARY KEY

테이블에서 각 행을 식별하기 위해 사용될 컬럼에 부여하는 제약조건으로 이를 기본키(PRIMARY KEY)라 합니다. 기본적으로 PRIMARY KEY 제약조건을 부여할 경우 NOT NULL + UNIQUE 제약조건이 자동으로 적용됩니다.

주의할 점은 한 테이블당 오직 한개만 설정 가능합니다.

새로운 테이블 MEM_PRI를 생성해보겠습니다.

CREATE TABLE MEM_PRI(
    MEM_NO NUMBER CONSTRAINT MEMNO_PK 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(13),
    EMAIL VARCHAR2(50),
    UNIQUE(MEM_ID)
    -- PRIMARY KEY(MEM_NO)
);

역시 데이터도 넣어보겠습니다. 주석이 달린 쿼리는 에러가 뜨는 쿼리입니다.

INSERT INTO MEM_PRI
VALUES(1, 'USER1', 'PASS1', '홍길동', '남', '010-1111-2222', 'aaaa@naver.com');

INSERT INTO MEM_PRI
VALUES(1, 'USER2', 'PASS2', '홍길순', '여', NULL, NULL);
-- 기본키에 중복값을 담으려고 할 때(UNIQUE 제약조건 위반)

INSERT INTO MEM_PRI
VALUES(NULL, 'USER2', 'PASS2', '홍길순', '여', NULL, NULL);
-- 기본키에 NULL을 담으려고 할 때(NOT NULL 제약조건 위반)

INSERT INTO MEM_PRI
VALUES(2, 'USER2', 'PASS2', '홍길순', '여', NULL, NULL);

그런데 아래와 같은 테이블을 살펴봅시다.

CREATE TABLE MEM_PRI2(
    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(13),
    EMAIL VARCHAR2(50),
    UNIQUE(MEM_ID),
    PRIMARY KEY(MEM_NO, MEM_ID)
);

기본키가 있긴 한데... 항목이 2개가 들어가 있습니다. 이 경우에는 두개의 항목을 서로 붙인 것을 기본키로 지정한다고 생각하시면 됩니다. 이를 복합키라 합니다.

FOREIGN KEY

다른 테이블에 존재하는 값만 들어와야되는 특정 칼럼에 부여하는 제약조건으로 이를 외래키(FOREIGN KEY)라 합니다. 다른 테이블은 참조한다고 표현하고, 주로 FOREIGN KEY 제약조건으로 인해 테이블간 관계가 형성됩니다.

표현 방법은 아래와 같습니다.

컬럼레벨방식

컬럼명 자료형 REFERENCES 참조할테이블명[(참조할 컬럼명)]

테이블 레벨방식

FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명[(참조할 컬럼명)]

참조할 컬럼명 생략시 참조할 테이블에 PRIMARY KEY로 지정된 컬럼이 매칭됩니다.

새로운 테이블 MEM_GRADE를 우선 만들고 데이터를 삽입하겠습니다.

CREATE TABLE MEM_GRADE(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO MEM_GRADE VALUES(10, '일반회원');
INSERT INTO MEM_GRADE VALUES(20, '우수회원');
INSERT INTO MEM_GRADE VALUES(30, '특별회원');

여기서 MEM_GRADE를 참조하는 MEM 테이블을 생성하겠습니다.
외래키의 경우 GRADE_ID로 지정합니다.

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),-- 남, 여
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE)
    -- FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);

데이터를 삽입해보겠습니다.

우선, 외래키 제약 조건이 부여된 컬럼에 NULL도 가능합니다.

INSERT INTO MEM VALUES(1, 'USER1', 'PASS01', '홍길동', '남', NULL, NULL, NULL);
INSERT INTO MEM VALUES(2, 'USER2', 'PASS02', '홍길순', '여', NULL, NULL, 10);

하지만 NULL이 된다고 했지 없는 값은 안됩니다. 위 MEM_GRADE 삽입 데이터를 보면 GRADE_CODE에 10, 20, 30만 가능하므로 이와 연결된 외래키 GRADE_ID에는 10, 20, 30, 없으면 NULL 제외 다른 값이 오는 것은 불가능합니다.

INSERT INTO MEM VALUES(3, 'USER3', 'PASS03', '최개똥', NULL, NULL, NULL, 40);
-- parent key를 찾을 수 없다는 오류 발생

삭제 옵션

자식테이블 생성시 외래키 제약조건 부여할 때 삭제옵션 지정가능합니다. 이 때 삭제 옵션이 의미하는 바는 부모테이블의 데이터 삭제시 그 데이터를 사용하고 있는 자식테이블의 값을 어떻게 할 것인가에 초점이 맞추어져 있습니다.

크게 종류는 아래와 같습니다. 각 설명은 부모데이터 삭제시 진행 작업입니다.

• ON DELETE RESTRICTED(기본값) : 삭제가 아예 안됨(오류발생)

• ON DELETE SET NULL : 해당 데이터를 사용하고 있는 자식데이터의 값을 NULL로 변경

• ON DELETE CASCADE : 해당 데이터를 사용하고 있는 자식데이터도 같이 삭제시키는 옵션

예시를 보겠습니다. RESTRICTED는 따로 조건 지정 안해주면 자동으로 지정되는 기본값이므로 예시는 생략하겠습니다.

참고로 데이터를 지우기 위해서는 DELETE를 사용합니다. 이에 대한 자세한 설명은 DML 포스팅에 올릴 예정입니다.

SET NULL

DROP TABLE MEM;

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),-- 남, 여
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL
    -- FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);

INSERT INTO MEM VALUES(1, 'USER1', 'PASS01', '홍길동', '남', NULL, NULL, NULL);
INSERT INTO MEM VALUES(2, 'USER2', 'PASS02', '홍길순', '여', NULL, NULL, 10);
INSERT INTO MEM VALUES(3, 'USER3', 'PASS3', '김개똥', '여', NULL, NULL, 20);
INSERT INTO MEM VALUES(4, 'USER4', 'PASS4', '최배달', '남', NULL, NULL, 10);

--10번 등급 삭제
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 10;
--> 잘 삭제가 완료됨, 10을 가져다 쓰고있던 자식데이터의 값은 NULL로 변경됨

CASCADE

DROP TABLE MEM;

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),-- 남, 여
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE
    -- FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);

INSERT INTO MEM VALUES(1, 'USER1', 'PASS01', '홍길동', '남', NULL, NULL, NULL);
INSERT INTO MEM VALUES(2, 'USER2', 'PASS02', '홍길순', '여', NULL, NULL, 10);
INSERT INTO MEM VALUES(3, 'USER3', 'PASS3', '김개똥', '여', NULL, NULL, 20);
INSERT INTO MEM VALUES(4, 'USER4', 'PASS4', '최배달', '남', NULL, NULL, 10);

--10번 등급 삭제
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 10;
--> 삭제가 잘됨, 해당데이터를 사용하고 있던 자식데이터도 같이 삭제가 됨

DEFALUT

DEFAULT는 엄밀히 말해서 제약조건은 아닙니다. 기능은 컬럼을 선정하지않고 INSERT시 NULL이 아닌 기본값으로 세팅해둘 수 있는 값을 의미합니다.

표현 방법은 아래와 같습니다.

컬럼명 자료형 DEFAULT 기본값

바로 예시로 넘어가도록 하겠습니다. 이 때, DEFAULT의 경우 기본값을 넣어줄 때 INSERT문에 DEFAULT라고 넣어주어야 합니다.

DROP TABLE MEMBER;

CREATE TABLE MEMBER(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_NAME VARCHAR2(20) NOT NULL,
    MEM_AGE NUMBER,
    HOBBY VARCHAR2(20) DEFAULT '없음',
    ENROLL_DATE DATE DEFAULT SYSDATE
);

--INSERT INTO 테이블명 VALUES(컬럼값, 컬럼값...)
INSERT INTO MEMBER VALUES(1, '빵빵이', 20, '운동', '20/01/01');
INSERT INTO MEMBER VALUES(2, '옥지', 22, NULL, NULL);
INSERT INTO MEMBER VALUES(3, '최지원', 17, DEFAULT, DEFAULT);

SELECT * FROM MEMBER;

--INSERT INTO MEMBER(컬럼1, 컬럼2, ...) VALUES(컬럼1값, 컬럼2값...);
INSERT INTO MEMBER(MEM_NO, MEM_NAME) VALUES(4, '이광인');
-->선택되지 않은 컬럼에는 기본적으로 NULL이 들어감
-->단, 해당 컬럼에 DEFAULT값이 부여되어 있을 경우 NULL이 아닌 DEFAULT값이 들어감.

테이블 복제

서브쿼리를 사용해서 테이블 복제가 가능합니다. 바로 예시로 보겠습니다.

CREATE TABLE EMPLOYEE_COPY
AS (SELECT * FROM EMPLOYEE);