DDL (Data Definition Language) (CREATE)

▶ DDL(Data Definition Language)

데이터 정의 언어로 객체(OBJECT)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말함

✓ 오라클 객체 종류
테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 프로시저(PROCEDUAL), 함수(FUNCTION), 트리거(TRIGGER), 동의어(SYNONYM), 사용자(USER)

▶ CREATE

테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문

✓ 표현식

CREATE TABLE 테이블명(컬럼명 자료형(크기), 컬럼명 자료형(크기), ...);
CREATE TABLE MEMBER(
	MEMBER_ID VARCHAR2(20),
	MEMBER_PWD VARCHAR2(20),
	MEMBER_NAME VARCHAR2(20)
);

▶ 오라클의 데이터형

▶ CHARACTER

✓ CHAR

CHAR( SIZE [ ( byte | char ) ]

＊ SIZE : 포함될 문자(열)의 크기
＊ 지정한 크기보다 작은 문자(열)가 입력되면 남는 공간은 공백으로 채움
＊ 데이터는 ‘’를 사용하여 표기하고 대·소문자를 구분함

✓ VARCHAR2

VARCHAR2( SIZE [ ( byte | char ) ]

＊ SIZE : 포함될 문자(열)의 크기
＊ 크기가 0인 값은 NULL로 인식
＊ 데이터는 ‘’를 사용하여 표기하고 대·소문자를 구분함

▶ NUMBER

NUMBER[ ( P [ , S ] ) ]

＊ P : 표현할 수 있는 전체 숫자 자리 수 (1 ~ 38)
＊ S : 소수점 이하 자리 수 (-84 ~ 127)

▶ DATE

DATE

＊ 일자(세기/년/월/일) 및 시간(시/분/초) 정보 관리
＊ 기본적으로 화면에 년/월/일 정보만 표기
＊ 날짜 연산 및 비교 가능

▶ 컬럼 주석

테이블의 컬럼에 주석을 다는 구문

✓ 표현식

COMMENT ON COLUMN 테이블명.컬럼명 IS ‘주석 내용';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS ‘회원아이디’;
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS ‘비밀번호’;
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS ‘회원이름’;

▶ 제약 조건(CONSTRAINTS)

테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약조건을 설정할 수 있는 데, 이는 데이터 무결성 보장을 주 목적으로 함
입력 데이터에 문제가 없는지에 대한 검사와 데이터의 수정/삭제 가능 여부 검사 등을 위해 사용

✓ 제약조건 확인

✓ NOT NULL
해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용
특정 컬럼에 값을 저장하거나 수정할 때 NULL 값을 허용하지 않도록 컬럼 레벨에서 제한

✓ 예시

CREATE TABLE USER_NOTNULL(
            USER_NO NUMBER NOT NULL,
            USER_ID VARCHAR2(20) NOT NULL,
            USER_PWD VARCHAR2(30) NOT NULL,
            USER_NAME VARCHAR2(30),
            GENDER VARCHAR2(10),
            PHONE VARCHAR2(30),
            EMAIL VARCHAR2(50)
);
INSERT INTO USER_NOTNULL VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남’, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_NOTNULL VALUES(2, NULL, NULL, NULL, NULL, ‘010-1234-5678’, ‘hong123@kh.or.kr’);

＊ NOT NULL 제약조건이 설정된 컬럼에 NULL값이 입력되면, 행 자체를 삽입하지 않음

✓ UNIQUE
컬럼 입력 값에 대해 중복을 제한하는 제약조건으로 컬럼 레벨과 테이블 레벨에 설정 가능

✓ UNIQUE 예시1

CREATE TABLE USER_UNIQUE(
          USER_NO NUMBER,
          USER_ID VARCHAR2(20) UNIQUE,
          USER_PWD VARCHAR2(30) NOT NULL,
          USER_NAME VARCHAR2(30),
          GENDER VARCHAR2(10),
          PHONE VARCHAR2(30),
          EMAIL VARCHAR2(50)
);
INSERT INTO USER_UNIQUE VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남‘, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_UNIQUE VALUES(1, ‘user01’, ‘pass01’, NULL, NULL, ‘010-1234-5678’, ‘hong123@kh.or.kr’);

✓ UNIQUE 예시2

CREATE TABLE USER_UNIQUE2(
          USER_NO NUMBER,
          USER_ID VARCHAR2(20),
          USER_PWD VARCHAR2(30) NOT NULL,
          USER_NAME VARCHAR2(30),
          GENDER VARCHAR2(10),
          PHONE VARCHAR2(30),
          EMAIL VARCHAR2(50),
          UNIQUE (USER_ID) --테이블 레벨
);
INSERT INTO USER_UNIQUE2 VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남’, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_UNIQUE2 VALUES(1, ‘user01’, ‘pass01’, NULL, NULL, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_UNIQUE2 VALUES(1, NULL, ‘pass01’, ‘홍길동’, ‘남’, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_UNIQUE2 VALUES(1, NULL, ‘pass01’, ‘홍길동’, ‘남’, ‘010-1234-5678’, ‘hong123@kh.or.kr’);

＊ 중복 값이 있는 경우 UNIQUE 제약 조건에 의해 행이 삽입되지 않음
＊ UNIQUE 제약조건은 NULL 값 중복은 가능  테이블 생성 시 컬럼 레벨에 NOT NULL 지정하면 해결

✓ UNIQUE 예시3

CREATE TABLE USER_UNIQUE3(
          USER_NO NUMBER,
          USER_ID VARCHAR2(20),
          USER_PWD VARCHAR2(30) NOT NULL,
          USER_NAME VARCHAR2(30),
          GENDER VARCHAR2(10),
          PHONE VARCHAR2(30),
          EMAIL VARCHAR2(50),
          UNIQUE (USER_NO, USER_ID) --두 개의 컬럼을 묶어 하나의 UNIQUE제약조건 설정
);
INSERT INTO USER_UNIQUE3 VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남’, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_UNIQUE3 VALUES(2, ‘user01’, ‘pass01’, NULL, NULL, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_UNIQUE3 VALUES(2, ‘user02’, ‘pass02’, NULL, NULL, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_UNIQUE3 VALUES(1, ‘user01’, ‘pass01’, NULL, NULL, ‘010-1234-5678’, ‘hong123@kh.or.kr’);

✓ PRIMARY KEY
테이블에서 한 행의 정보를 구분하기 위한 고유 식별자(Identifier)역할
NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며 한 테이블 당 하나만 설정 가능
컬럼 레벨과 테이블 레벨 둘 다 지정 가능

✓ PRIMARY KEY 예시1

CREATE TABLE USER_PRIMARYKEY(
	USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);
또는
CREATE TABLE USER_PRIMARYKEY(
	USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    PRIMARY KEY (USER_NO)
);
INSERT INTO USER_PRIMARYKEY VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남’, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_PRIMARYKEY VALUES(1, ‘user02’, ‘pass02’, ‘이순신‘, ‘남’, ‘010-5678-9012’, ‘lee123@kh.or.kr’);
INSERT INTO USER_PRIMARYKEY VALUES(NULL, ‘user03’, ‘pass03’, ‘유관순‘, ‘여’, ‘010-3131-3131’, ‘yoo123@kh.or.kr’);

✓ PRIMARY KEY 예시2

CREATE TABLE USER_PRIMARYKEY2(
USER_NO NUMBER,
USER_ID VARCHAR2(20),
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
PRIMARY KEY (USER_NO, USER_ID) --두 개의 컬럼을 묶어서 하나의 PRIMARY KEY 제약조건 설정

);
INSERT INTO USER_PRIMARYKEY2 VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남’, ‘010-1234-5678’, ‘hong123@kh.or.kr’);
INSERT INTO USER_PRIMARYKEY2 VALUES(1, ‘user02’, ‘pass02’, ‘이순신‘, ‘남’, ‘010-5678-9012’, ‘lee123@kh.or.kr’);
INSERT INTO USER_PRIMARYKEY2 VALUES(2, ‘user01’, ‘pass01’, ‘유관순‘, ‘여’, ‘010-3131-3131’, ‘yoo123@kh.or.kr’);
INSERT INTO USER_PRIMARYKEY2 VALUES(1, ‘user01’, ‘pass01’, ‘신사임당‘, ‘여’, ‘010-1111-1111’, ‘shin123@kh.or.kr’);

✓ FOREIGN KEY
참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는 것
참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성 되는데 참조되는 값은 제공되는 값 외에 NULL을 사용 가능하며 참조할 테이블의 참조할 컬럼명을 생략할 경우 PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 됨

✓ FOREIGN KEY 예시1

CREATE TABLE USER_GRADE(
        GRADE_CODE NUMBER PRIMARY KEY,
        GRADE_NAME VARCHAR2(30) NOT NULL
);
INSERT INTO USER_GRADE VALUES(10, ‘일반회원‘);
INSERT INTO USER_GRADE VALUES(20, ‘우수회원‘);
INSERT INTO USER_GRADE VALUES(30, ‘특별회원‘);
SELECT * FROM USER_GRADE;

CREATE TABLE USER_FOREIGNKEY(
          USER_NO NUMBER PRIMARY KEY,
          USER_ID VARCHAR2(20) UNIQUE,
          USER_PWD VARCHAR2(30) NOT NULL,
          USER_NAME VARCHAR2(30),
          GENDER VARCHAR2(10),
          PHONE VARCHAR2(30),
          EMAIL VARCHAR2(50),
          GRADE_CODE NUMBER,
          FOREIGN KEY (GRADE_CODE) REFERENCES USER_GRADE (GRADE_CODE)
);

또는

CREATE TABLE USER_FOREIGNKEY(
          USER_NO NUMBER PRIMARY KEY,
          USER_ID VARCHAR2(20) UNIQUE,
          USER_PWD VARCHAR2(30) NOT NULL,
          USER_NAME VARCHAR2(30),
          GENDER VARCHAR2(10),
          PHONE VARCHAR2(30),
          EMAIL VARCHAR2(50),
          GRADE_CODE NUMBER REFERENCES USER_GRADE (GRADE_CODE)
);

INSERT INTO USER_FOREIGNKEY
VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남‘, ‘010-1234-5678’, ‘hong123@kh.or.kr’, 10);
INSERT INTO USER_FOREIGNKEY
VALUES(2, ‘user02’, ‘pass02’, ‘이순신’, ‘남‘, ‘010-9012-3456’, ‘lee123@kh.or.kr’, 20);
INSERT INTO USER_FOREIGNKEY
VALUES(3, ‘user03’, ‘pass03’, ‘유관순’, ‘여‘, ‘010-3131-3131’, ‘yoo123@kh.or.kr’, 30);
INSERT INTO USER_FOREIGNKEY
VALUES(4, ‘user04’, ‘pass04’, ‘신사임당’, ‘여‘, ‘010-1111-1111’, ‘shin123@kh.or.kr’, NULL);
INSERT INTO USER_FOREIGNKEY
VALUES(5, ‘user05’, ‘pass05’, ‘안중근’, ‘남‘, ‘010-4444-4444’, ‘ahn123@kh.or.kr’, 50);

＊ FOREIGN KEY 제약조건으로 USER_GRADE TABLE의 GRADE_CODE 컬럼 참조
＊ USER_GRADE 테이블을 USER_FOREIGNKEY 테이블에서 참조하고 있기 때문에
USER_GRADE 테이블의 데이터 삭제 시 참조 무결성에 위배되어 삭제 불가능
→ 부모테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤 방식으로 처리할지에 대한 내용을
제약조건 설정 시 옵션으로 지정 가능
기본 삭제 옵션은 ON DELETE RESTRICTED로 지정되어 있음

✓ FOREIGN KEY 옵션(ON DELETE SET NULL)

CREATE TABLE USER_FOREIGNKEY(
        USER_NO NUMBER PRIMARY KEY,
        USER_ID VARCHAR2(20) UNIQUE,
        USER_PWD VARCHAR2(30) NOT NULL,
        USER_NAME VARCHAR2(30),
        GENDER VARCHAR2(10),
        PHONE VARCHAR2(30),
        EMAIL VARCHAR2(50),
        GRADE_CODE NUMBER REFERENCES USER_GRADE (GRADE_CODE) ON DELETE SET NULL
);
DELETE FROM USER_GRADE WHERE GRADE_CODE = 10;

＊ 부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 NULL로 변경됨

✓ FOREIGN KEY 옵션(ON DELETE CASCADE)

CREATE TABLE USER_FOREIGNKEY(
        USER_NO NUMBER PRIMARY KEY,
        USER_ID VARCHAR2(20) UNIQUE,
        USER_PWD VARCHAR2(30) NOT NULL,
        USER_NAME VARCHAR2(30),
        GENDER VARCHAR2(10),
        PHONE VARCHAR2(30),
        EMAIL VARCHAR2(50),
        GRADE_CODE NUMBER REFERENCES USER_GRADE (GRADE_CODE) ON DELETE CASCADE
);
DELETE FROM USER_GRADE WHERE GRADE_CODE = 10;

＊ 부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 존재하던 행 전체 삭제

✓ CHECK
해당 컬럼에 입력 되거나 수정되는 값을 체크하여 설정된 값 이외의 값이면 에러 발생
비교 연산자를 이용하여 조건을 설정하며 비교 값을 리터럴만 사용 가능하고 변하는 값이나 함수 사용은 불가능

✓ UNIQUE 예시1

CREATE TABLE USER_CHECK(
        USER_NO NUMBER PRIMARY KEY,
        USER_ID VARCHAR2(20) UNIQUE,
        USER_PWD VARCHAR2(30) NOT NULL,
        USER_NAME VARCHAR2(30),
        GENDER VARCHAR2(10) CHECK (GENDER IN (‘남’, ‘여‘)),
        PHONE VARCHAR2(30),
        EMAIL VARCHAR2(50)
);
INSERT INTO USER_CHECK VALUES(1, ‘user01’, ‘pass01’, ‘홍길동’, ‘남자‘, ‘010-1234-5678’, ‘hong123@kh.or.kr’);

▶ SUBQUERY를 이용한 CREATE TABLE

서브 쿼리를 이용해서 SELECT의 조회 결과로 테이블을 생성하는 방법으로 컬럼명과 데이터 타입, 값이 복사 되고 제약 조건은 NOT NULL만 복사됨

✓ 예시

CREATE TABLE EMPLOYEE_COPY
 AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
    FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    LEFT JOIN JOB USING(JOB_CODE);