#22. DDL(CREATE)

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

오라클에서 제공하는 객체(OBJECT)를 새롭게 만들고(CREATE), 구조를 변경하고(ALTER), 구조 자체를 삭제하는(DROP)하는 명령문이다.

DDL은 구조자체를 정의하는 언어로 주로 DB관리자, 설계자가 사용한다.

 

22_1. CREATE TABLE

# 자료형

- CHAR(바이트수) : 최대 2000BYTE까지 지정 가능, 고정길이

- VARCHAR2(바이트수) : 최대 4000BYTE까지 지정 가능, 가변길이

    (숫자, 영문자, 특수문자 => 1글자당 1BYTE / 한글 => 1글자당 3BYTE)

- 숫자 (NUMBER) : 정수 / 실수 상관없이 NUMBER!

- 날짜 (DATE) 

 

1. 테이블 생성

CREATE TABLE 테이블명 (컬럼명 자료형);

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_DATE DATE
);

2. 컬럼에 주석 달기

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

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

 

만든 테이블이 계정에 잘 생성되었는지 조회해보자.

 

현재 이 계정이 가지고 있는 전반적인 구조를 확인할 수 있는 데이터 딕셔너리

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

SELECT * FROM USER_TABLES;

현재 이 계정이 가지고 있는 테이블들의 모든 컬럼의 정보를 조회할 수 있는 데이터 딕셔너리

SELECT * FROM USER_TAB_COLUMNS;

 

INSERT INTO 테이블명 VALUES('컬럼값', '컬럼값'...'컬럼값');

 

INSERT를 할 때,

원하는 데이터 값만 유지하기 위해서(보관하기 위해서) 특정 컬럼마다 제약을 설정해 준다.

 

22_2. 제약조건

1. NOT NULL 제약조건 : NULL값을 허용하지 않는 제약조건

컬럼레벨 방식만 가능! => 각 컬럼에 바로 기술

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
);

2. UNIQUE 제약조건 : 컬럼에 중복값을 제한하는 제약조건

1) 컬럼라벨 방식

CREATE TABLE MEM_UNIQUE(
    MEM_ID VARCHAR(20) NOT NULL UNIQUE
);

2) 테이블라벨 방식

CREATE TABLE MEM_UNIQUE(
    MEM_ID VARCHAR2(20) NOT NULL,
    UNIQUE (MEM_ID)
);

제약조건 위반시 오류 -

ORA-0001 : unique constraint (DDL.SYS0010059) violated

 

#제약조건 부여시 제약조건명도 지정하는 방법 : 제약조건 앞에 'CONSTRAINT 제약조건명'을 붙이면 된다.

CREATE TABLE MEM_CON_NM(
    MEM_ID VARCHAR(20) NOT NULL,
    CONSTRAINT MEM_ID_UQ UNIQUE (MEM_ID)
);

제약조건명을 지정한 후의 오류 내용(어떤 컬럼에 문제가 있는지 해당 컬럼의 제약조건명으로 알려줌)

ORA-00001 : unique constraint(DDL.MEM_ID_UQ) violated

 

3. CHECK 제약조건 : 컬럼에 기록될 수 있는 값에 대한 조건을 설정해 두는 제약조건

1) 컬럼라벨 방식

CREATE TABLE MEM_CHECK(
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여'))
);

2) 테이블라벨 방식

CREATE TABLE MEM_CHECK(
    GENDER CHAR(3) 
    CHECK(GENDER IN ('남', '여'))
);

CHECK 제약조건을 건다고 해서 NULL값이 못 들어가는 건 아니다.

 

새롭게 컬럼이 만들어지고 지정이 안된 컬럼에는 기본적으로 NULL값이 들어가지만

만일 DEFAULT값이 부여되어있다면 NULL값 대신 DEFAULT로 지정해놓은 값이 들어가게 된다!

CREATE TABLE MEM_CHECK(
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

 

4. PRIMARY KEY(기본키) 제약조건 : 테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 컬럼에 부여하는 제약조건

중복되지 않고 값이 존재해야만 하는 컬럼에 PRIMERY KEY부여(NOT NULL + UNIQUE)

단, 한 테이블 당 한 번만 설정 가능

1) 컬럼라벨 방식

CREATE TABLE MEM_PRIMARYKEY1(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여'))
);

2) 테이블라벨 방식

CREATE TABLE MEM_PRIMARYKEY1(
    MEM_NO NUMBER
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    CONSTRAINT MEM_PK PRIMARY KEY(MEM_NO) -- 테이블레벨 방식
);

 

컬럼을 묶어서 PRIMARY KEY 하나로 설정할 수 있다.  => 복합키

CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20),
    PRIMARY KEY (MEM_NO, MEM_ID)
);

 

5. FOREIGN KEY(외래키) 제약조건 : 해당 컬럼에 다른 테이블에 존재하는 값만 들어와야하는 컬럼에 부여하는 제약조건!

(부모테이블 - 자식테이블)

1) 컬럼라벨 방식

CREATE TABLE MEM(
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) -- 컬럼레벨방식
);

2) 테이블라벨 방식

CREATE TABLE MEM(
    GRADE_ID CHAR(2)
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);

참조할 컬럼명은 생략할 경우 기본적으로 참조할 테이블의 PRIMARY KEY 컬럼으로 참조할 컬럼명으로 잡힌다.

오류 시 : ORA-02291 : integrity constraint violated - parent key not found

 

참조할 때, 부모테이블에 RPIMARY KEY나 UNIQUE가 없으면 참조되지 않는다.

자식테이블에서 부모테이블에 존재하는 값을 사용하고 있을 경우 삭제가 안되는 "삭제 제한 옵션"이 걸려있음

 

 

23_3. 부모테이블 / 자식테이블

기본값 : ON DELETE RESTRICTED(삭제 제한)  --> 옵션을 바꿀 수 있음

1. ON DELETE SET NULL : 부모데이터를 삭제 시 해당 데이터를 사용하고 있는 자식컬럼을 NULL값으로 변경시키는 옵션

 

2) 테이블라벨 방식

CREATE TABLE MEM(
    GRADE_ID CHAR(2), 
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL
);

 

2. ON DELETE CASCADE : 부모데이터 삭제 시 해당 데이터를 사용하고 있는 자식데이터도 같이 삭제해버리는 옵션

2) 테이블라벨 방식

CREATE TABLE MEM(
    GRADE_ID CHAR(2),
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE
);

 

23_4. CREATE TABLE ... AS 서브쿼리

컬럼들, 조회결과의 데이터값들 복사. 제약조건은 NOT NULL만 복사 됨

조건에 맞게 복사해 올 수 있다. 여기서 컬럼의 구조만 복사하고 싶다면 WHERE(조건절)을 FALSE로 만들어주면 된다.

CREATE TABLE EMPLOYEE_COPY2 AS
SELECT *
  FROM EMPLOYEE
 WHERE 1 = 0;

서브쿼리의 SELECT절 산술연산 또는 함수식이 기술된 경우 반드시 별칭을 부여해야한다.

CREATE TABLE EMPLOYEE_COPY4 AS
SELECT EMP_ID, EMP_NAME, SALARY, SALARY * 12 "연봉"
  FROM EMPLOYEE;

여기서 복사한 테이블에 다시 제약조건을 걸고 싶다면?

 

23_5. ALTER TABLE ... ADD 

테이블이 생성된 후 제약조건 추가

ALTER TABLE EMPLOYEE_COPY ADD PRIMARY KEY(EMP_ID);

다른 테이블을 참조하여 걸 수도 있다.

-- EMPLOYEE_COPY 테이블에 DEPT_CODE컬럼에 외래키 제약조건 추가(DEPARTMENT의 DEPT_ID를 참조)
ALTER TABLE EMPLOYEE_COPY ADD FOREIGN KEY(DEPT_CODE) REFERENCES DEPARTMENT(DEPT_ID);

-- EMPLOYEE_COPY 테이블에 JOB_CODE컬럼에 외래키 제약조건 추가(JOB의 JOB_CODE를 참조)
ALTER TABLE EMPLOYEE_COPY ADD FOREIGN KEY(JOB_CODE) REFERENCES JOB(JOB_CODE);

-- DEPARTMENT 테이블에 LOCATION_ID에 외래키 제약조건 추가(LOCATION의 LOCAL_CODE 참조)
ALTER TABLE DEPARTMENT ADD FOREIGN KEY(LOCATION_ID) REFERENCES LOCATION(LOCAL_CODE);

 

 

-----------------------------------------------------------------------------------------------------------------

나홀로 실습)

DDL 계정에 'STUDENT'테이블을 만들어, 이름, 학번, 학년, 성별, 입학날짜 컬럼을 만들고

각 컬럼에 제약조건을 거시오.

하나는 컬럼라벨방식, 하나는 테이블라벨방식

STUDENT는 'LEVEL'(LEVEL_CODE, LEVEL_NAME)라는 테이블을 참조하여 학년 컬럼에 제약조건을 건다.

컬럼에 값을 넣고 조회 후, DELETE까지 진행.

여기서 학년이 3학년인 학생들만 조회하여 테이블을 복사하고

복사한 테이블에 제약조건을 다시 추가해주자.

 

-----------------------------------------------------------------------------------------------------------------------