06. DDL(CREATE)

JAVA 웹 개발/2. Oracle

06. DDL(CREATE)

코딩 펭귄 2022. 8. 13. 15:00

📅 2021.09.14 ~ 2021.09.16

DDL(Data Definition Language)

데이터 정의 언어로 객체(Object)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말한다.
오라클 객체 종류: 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 프로시저(PROCEDUAL), 함수(FUNCTION), 트리거(TRIGGER), 동의어(SYNONYM), 사용자(USER)

CREATE

테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문 말하며, 다음과 같은 형태로 작성한다.
CREATE TABLE 테이블명(컬럼명 자료형(크기),
컬럼명 자료형(크기),
...);
오라클 데이터형
- CHAR(크기) : 고정 길이 문자 데이터
- VARCHAR2(크기) : 가변 길이 문자 데이터(최대 2000Byte), 기본적으로 바이트 단위를 사용하며, 문자 기준 단위로 사용할 경우 VARCHAR2(10CHAR)와 같이 표기해주어야 함
- NVARCHAR : 기본 단위가 문자이기 때문에 문자열의 크기를 문자 개수로 취급함, 한글 같은 유니코드 사용에 유리함
- NUMBER : 숫자 데이터(최대 40자리)
- NUMBER(길이) : 숫자 데이터로 길이 지정 가능(최대 38자리)
- DATE : 날짜 데이터(BC 4712년 1월 1일 ~ AD 4712년 12월 31일)
- LONG : 가변 길이 문자형 데이터(최대 2GB), VARCHAR2나 CHAR로 감당할 수 없을 때 더 많은 문자열을 저장하기 위하여 사용
- LOB : 2GB까지의 가변 길이 바이너리 데이터 저장 가능(이미지, 실행 파일 등 저장 가능), VARCHAR2나 CHAR로 감당할 수 없을 때 더 많은 문자열을 저장하기 위하여 사용, CLOB이 많이 사용됨
- ROWID : DB에 저장되지 않은 행을 식별할 수 있는 고유 값
- BFILE : 대용량의 바이너리 데이터 저장 가능(최대 4GB)
- TIMESTAMP : DATE형의 확장된 형태, 시간에 대한 데이터는 DATE로 제대로 저장되지 않는 경우가 있는데, 그런 경우에 TIMESTAMP를 사용하여 시간 저장
- INTERVAL YEAR TO MONTH : 년과 월을 이용하여 기간 저장
- INTERVAL DAY TO SECOND : 일, 시, 분, 초를 이용하여 기간 저장
컬럼 주석 : 테이블의 컬럼에 주석을 다는 구문으로 다음과 같이 사용한다.
COMMENT ON COLUMN 테이블명.컬럼명 IS '주석 내용';
USER_TABLES : 사용자가 작성한 테이블을 확인하는 뷰이며, DB가 가지고 있다. 따라서 공용동의어에서 확인 가능하다.
USER_TAB_COLUMNS : 컬럼과 관련된 정보를 가지고 있는 뷰이다.
USER_CONS_COLUMNS : 제약 조건이 걸려 있는 컬럼을 확인하는 뷰이다.
DESC 테이블명;을 이용하여 해당 테이블 구조를 확인할 수 있다.
다음과 같이 테이블을 만들고 복사할 때 WHERE 절에 항상 FALSE인 조건을 넣는 경우 아무 행도 가지고 오지 않는다. 따라서 컬럼만 생성할 수 있다.
```
CREATE TABLE EMP_DEPT_D1 
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE 
   FROM EMPLOYEE 
   WHERE 1 = 0
```

제약 조건(CONSTRAINTS)

테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약 조건을 설정할 수 있다. 제약 조건을 설정하는 목적은 데이터의 무결성을 보장하기 위해서이다. 데이터 무결성이란 데이터의 정확성, 일관성, 유효성을 보장하는 것을 말한다.
제약 조건은 테이블 생성 시 지정 가능하며, 생성 이후에도 지정이 가능하다.
제약 조건의 종류
- NOT NULL : 데이터에 NULL을 허용하지 않는다는 뜻으로 반드시 값이 기록되어야 하는 경우에 사용한다. 해당 컬럼에 값을 저장/수정하는 경우 NULL을 허용하지 않도록 컬럼 레벨에서 제한한다.
- UNIQUE : 중복을 제한하는 제약 조건으로 컬럼 레벨과 테이블 레벨에 설정 가능하다.
- PRIMARY KEY : NULL과 중복 값을 모두 허용하지 않는 제약 조건으로 NOT NULL + UNIQUE의 의미이다. 테이블의 한 행의 정보를 구분하기 위한 고유 식별자로 사용하며, 한 테이블 당 하나만 설정 가능하다. 컬럼 레벨과 테이블 레벨에 설정 가능하다.
- FOREIGN KEY : 참조 무결성을 위한 제한 조건으로 참조된 다른 테이블이 제공한 값 또는 NULL만 사용 가능하도록 하는 제약 조건이다. 다른 테이블을 참조하기 위한 다리 역할을 하며, 참조할 테이블의 참조할 컬럼 명을 생략할 경우 PRIMARY KEY로 설정한 컬럼이 자동으로 참조할 컬럼이 된다.
- CHECK : 저장 가능한 데이터의 범위나 조건을 지정하여 설정한 조건에 맞는 값만 허용하는 제약 조건이다. 비교 연산자를 이용하여 조건을 설정하며, 비교 값은 리터럴만 허용하고, 변하는 값이나 함수 사용은 불가능하다.
제약 조건의 이름은 중복될 수 없다.
컬럼 레벨에서의 제약 조건 설정 : 테이블을 생성하는 경우 컬럼 명 옆에 제약 조건을 붙이면 컬럼 레벨에서의 제약 조건 설정이 된다.
테이블 레벨에서의 제약 조건 설정 : 컬럼명을 모두 작성한 후 아래에 제약 조건을 작성하면 테이블 레벨에서의 제약 조건 설정이 된다.
제약 조건 이름 설정 : 기본으로 설정되는 제약 조건 이름은 해당 테이블에서 제약 조건을 찾지 않는다면 어떤 제약 조건을 위배하였는지 명확하게 식별하기 어렵다. 따라서 오류만 보고 어떤 제약 조건을 위배했는지 파악하기 위해 제약 조건의 이름을 설정할 수 있는데, 제약 조건의 이름은 관례 상 다음과 같이 작성한다.
어떤 제약 조건인지(ex. NOT NULL = NN)_테이블 이름 약자_컬럼 명 약자
UNIQUE = UK = UQ
CHECK = CH = CK
ex. NN_CN_TD1

제약 조건은 다음과 같은 방법으로 설정한다.

CREATE TABLE CONS_NAME(
	TEST_DATA1 VARCHAR2(20) CONSTRAINT NN_CN_TD1 NOT NULL
);

테이블을 만든 후 제약 조건을 넣는 경우 아래와 같이 수정 명령어인 ALTER를 사용하면 된다.
```
ALTER TABLE USER_GRADE4 ADD CONSTRAINT PK_UG4_GC PRIMARY KEY(GRADE_CODE);
```
단 NOT NULL의 경우 ADD가 아닌 MODIFY를 사용하여 추가한다.
```
ALTER TABLE USER_FOREIGNKEY4 MODIFY USER_PWD NOT NULL;
```

NOT 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) NOT NULL,
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

UNIQUE

컬럼 값에 중복을 제한하는 제약 조건으로 컬럼 레벨과 테이블 레벨 모두에서 설정 가능하다.
테이블 레벨에서 설정하는 경우 컬럼을 모두 입력한 후 그 아래에 작성한다.

CREATE TABLE USER_UNIQUE(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE, -- 컬럼 레벨
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    UNIQUE(USER_NO) -- 테이블 레벨
);

만약 테이블 레벨에서 제약 조건을 설정할 때 여러 컬럼을 한 번에 입력하면 각각 컬럼에 제약 조건이 생기는 것이 아니라, 해당 컬럼들을 모두 묶은 제약 조건이 생기는 것이다.
예를 들어 UNIQUE(USER_NO, USER_ID)라고 작성하는 경우, USER_ID VARCHAR2(20) UNIQUE, UNIQUE(USER_NO, USER_ID)와 같은 뜻이 되는 것이 아니라, USER_NO와 USER_ID를 묶어 새로운 제약 조건을 만드는 것이다.
따라서 UNIQUE(USER_NO, USER_ID)라고 작성된 경우에는 USER_NO와 USER_ID가 모두 같은 경우에만 행을 추가할 수 없다.

PRIMARY KEY

데이터의 값이 중복되지 않는 값으로 존재하여야 하는 제약 조건이다. (UNIQUE + NOT NULL) 컬럼 레벨, 테이블 레벨 모두 설정 가능하다.
행을 구분할 수 있는 식별자 역할을 하기 때문에 다른 제약 조건과는 다르게 하나의 컬럼에만 설정할 수 있다.
PRIMARY KEY의 경우 오류가 발생하면 해당 오류가 UNIQUE를 위반하였는지, NOT NULL을 위반하였는지에 따라 오류 메시지가 다르게 출력된다. 따라서 PRIMARY KEY를 위반하였다는 오류 메시지가 아니라 UNIQUE 혹은 NOT NULL과 같은 오류 메시지가 출력된다.
PRIMARY KEY 또한 테이블 레벨에서 두 개 이상의 컬럼을 묶어서 설정할 경우 해당 컬럼을 세트로 취급하여 모두 같은 경우가 있는지 확인하나, NULL의 경우 해당 컬럼 중 어디에든 존재하면 에러가 발생한다.

FOREIGN KEY

참조된 테이블이 제공한 값만 사용이 가능한 제약 조건으로 참조할 테이블이 존재하여야 한다. 컬럼 레벨, 테이블 레벨 모두 설정 가능하다.
FOREIGN KEY 제약 조건으로 테이블과 테이블 간의 관계를 형성할 수 있다.
FOREIGN KEY의 경우 참조할 테이블이 꼭 존재하여야 하기 때문에 제약 조건을 추가하는 경우 참조할 테이블 명을 꼭 적어 주어야 하며, 참조할 컬럼은 꼭 적어 주어야 하는 것은 아니다. 만약 참조할 컬럼 명을 적지 않는 경우에는 부모 테이블의 기본 키를 디폴트로 연결한다.

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 CONSTRAINT FK_UF_GC REFERENCES USER_GRADE(GRADE_CODE)
);

참조하는 경우 참조하는 컬럼과 참조 당하는 컬럼의 이름이 꼭 같아야 하는 것은 아니다.
FOREIGN KEY를 만든 후 JOIN으로 테이블 연결이 가능하나, 꼭 FOREIGN KEY가 있는 경우에만 연결 가능한 것은 아니다. FOREIGN KEY가 없는 경우에도 JOIN으로 연결이 가능하다.
삭제 옵션: 부모 테이블에 있는 데이터를 삭제 시 자식 테이블의 데이터를 어떻게 처리할지 설정하는 것이다. 만약 삭제 옵션을 설정하지 않는 경우 참조가 되고 있는 데이터는 삭제할 수 없다.
1. ON DELETE SET NULL: 부모 키 삭제 시 자식 키를 NULL로 변경한다.
```
CONSTRAINT FK_UF2_GC FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE2(GRADE_CODE) ON DELETE SET NULL
```
2. ON DELETE CASCADE: 부모 키 삭제 시 자식 키도 함께 삭제된다.
```
CONSTRAINT FK_UF3_GC FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE3(GRADE_CODE) ON DELETE CASCADE
```
제약 조건 작성 시 테이블 레벨에서 설정 가능한 제약 조건들은 아래에 묶어 작성하는 것이 좋다.

CHECK

컬럼에 기록되는 값에 조건을 설정하는 제약 조건으로 컬럼 레벨, 테이블 레벨 모두 설정 가능하다.

CREATE TABLE USER_CHECK(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10) CHECK(GENDER IN('남', '여')), -- 컬럼 레벨
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
	PRICE NUMBER, 
	CHECK(PRICE >= 1 AND PRICE <= 99999) -- 테이블 레벨
);

SUBQUERY를 사용한 테이블 생성

SUBQUERY를 이용하여 다음과 같이 테이블 복사, 생성이 가능하다

-- 전체 테이블 복사
CREATE TABLE EMPLOYEE_COPY
AS SELECT * FROM EMPLOYEE;

-- 원하는 컬럼만 복사
CREATE TABLE EMPLOYEE_COPY2
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
   FROM EMPLOYEE
        JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
        JOIN JOB USING(JOB_CODE);

복사를 하는 경우 모든 조건이 복사되는 것은 아니며, 컬럼명, 데이터 타입, 데이터, NOT NULL 제약 조건만 복사가 가능하다. 이외의 제약 조건은 복사되지 않는다.

저작자표시 비영리 동일조건 (새창열림)