오라클 DB 용어, 명령문 정리05- CREATE 명령어, CONSTRAINT 제약조건

2019년 05월 23일 필기

<데이터 정의어 DDL>

---------------------------------------------기본 개념---------------------------------------------

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

데이터베이스 객체를 관리하기 위한 명령

※ 데이터베이스 객체 : 테이블, 뷰, 인덱스, 시퀀스, 동의어, 사용자 등

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

 

<데이터베이스 객체 이름(식별자) 작성규칙>
- 테이블 및 컬럼명은 문자로 시작 / 1~30문자 이내 작성 (한글 등 기타언어도 가능하지만 권장하지는 않음) 
- 테이블 및 컬럼명에는 A~Z, a~z, 0~9, _, $, #가 들어갈 수 있다.
- 동일한 사용자의 다른 객체와 이름이 중복되지 않도록 한다.
- ORACLE 예약어는 사용 불가
- 대소문자 구별 안함

 

 

< CREATE 명령어 - 테이블 생성 >

※ 테이블 : 데이터베이스에서 데이터를 저장하기 위한 가장 기본적인 객체

 

 

[형식] CREATE TABLE(column datatype [(size)][DEFAULT default] [constraint]

                             ,  column datatype [(size)][DEFAULT default] [constraint], ... [, *constraint])

--- () 안에 선언된 컬럼 순서대로 나열된 속성의 테이블이 생성

--- [*마지막 제약조건]은 테이블 수준 제약조건이다.(column, datatype이 적혀있지 않음)

 

 

< 많이 쓰는 자료형 DATATYPE >

: 컬럼에 저장 가능한 상수를 표현하는 키워드

1. 숫자형 : NUMBER[자릿수] -- 소수점을 표현하고 싶으면 [n1, n2] 로 나타냄

2. 문자형 : CHAR(크기) -- 고정형 1~2000(byte)

              VARCHAR2(크기) -- 가변형 1~4000(byte)

              LONG -- 가변형 2gbyte -- 테이블에 하나의 컬럼에만 부여 가능, 정렬 불가능

              CLOB -- 가변형 4gbyte (문자데이터) -- 텍스트 파일 저장

              BLOB -- 가변형 4gbyte (원시데이터) -- 바이너리 파일 저장(이미지, 동영상 파일 등)

3. 날짜형 : DATE -- 날짜와 시간

              TIMESTAMP -- 초단위 시간

 

 

 

< 제약조건(CONSTRAINT) >

: 잘못된 컬럼값이 저장되는 것을 방지하기 위한 기능 >> 데이터 무결성 유지

※ 데이터 무결성 : 테이블에 정상적인 데이터만을 저장하여 검색

※ 컬럼 수준의 제약조건 : 컬럼 선언시 제약을 걸 조건 지정

※ 테이블 수준의 제약조건 : 테이블 생성시 제약을 걸 조건 지정

                                     ---  컬럼선언 후 제약조건 지정 --- 컬럼 참조 가능

                                     --- 복잡한 제약조건은 테이블 수준으로 주면 좋다.

-- 제약조건을 지정할 경우 제약조건에 명칭(제약조건명)을 부여하는 것을 권장 - 제약조건 제거시 용이함

[형식] 컬럼명 자료형 CONSTRAINT 제약조건명 제약조건 -- 컬럼수준의 제약조건

 

NOT NULL

: 컬럼값으로 NULL 저장을 허용하지 않도록 지정하는 제약조건

-- 컬럼 수준의 제약조건으로만 선언 가능

-- NOT NULL 제약조건이 지정되지 않은 칼럼은 NULL 저장 허용

 

CONSTRAINT_NAME(제약조건명)  : 제약조건의 관리를 위해 적절한 이름을 붙여 사용

                   --- 제약조건의 이름을 설정하지 않으면 SYS_XXXXXX로 자동 설정

CONSTRAINT_TYPE(제약조건 종류) : C(CHECK), U(UNIQUE), P(PRIMARY KEY), R(FOREIGN KEY)

CHECK : 컬럼값 저장조건을 제공하여 조건에 맞는 값만이 저장되도록 지정하는 제약조건

UNIQUE : 중복된 컬럼값 저장을 방지하기 위해 지정하는 제약조건.

              이미 테이블에 있는 값과 중복되면 저장시키지 않음

                   --- null은 중복값으로 인식 X

                   --- 테이블에 여러 개의 UNIQUE KEY  제약조건 지정 가능

PRIMARY KEY(기본키) * : 중복된 컬럼값 저장을 방지하기 위해 지정하는 제약조건

                   중복값 저장 X / null값 저장 X

                   --- 테이블에 하나의 PRIMARY KEY 제약조건 지정 가능

                   --- 컬럼에 NOT NULL 제약조건이 자동 설정됨

FOREIGN KEY(외래키) : 다른 테이블의 컬럼값을 참조하여 잘못된 컬럼값 저장을 방지하기 위한 제약조건

                   즉, 테이블끼리 부모-자식 상속관계로 묶여 다른 테이블과 연결시켜주는 연결고리이다.

[형식]CONSTRAINT constraint_name FOREIGN KEY(column_child) REFERENCES table_parent(column_parent));

                   --- 테이블 조인에서 사용되는 조인조건의 컬럼값이 잘못된 경우를 방지하기 위해 설정

                   --- 부모테이블(참조되는 테이블)의 컬럼에 반드시 PK 제약조건이 부여되어야만(고유값 제한을 줘야지만)

                       자식테이블(참조하고자 하는 테이블)에서 FK 제약조건을 이용해 참조 가능

                   --- 제약조건에는 R로 나오는데, Reference에서 온 R이다.

 

이러한 제약조건 위반시 (type) constraint (USER_NAME.CONSTRAINT_NAME) violated 이라는 오류 메시지가 뜨게 된다.

 

 

< FOREIGN KEY를 이용한 부모-자식 상속 테이블의 관계 > 

- 자식테이블에 행을 삽입, 변경할 경우 컬럼값이 부모테이블의 참조컬럼에 존재하지 않는 값이면 에러 발생

- 자식테이블의 행이 참조하는 부모테이블의 참조컬럼을 가진 행은 삭제 불가

- ON DELETE CASCADE : 부모테이블 참조컬럼에 있는 행을 삭제할 때 자식테이블 참조행도 같이 삭제
- ON DELETE SET NULL : 부모테이블 참조컬럼에 있는 행을 삭제할 때 자식테이블 참조행의 컬럼값을 null로 변경

 

 

< 테이블 수준 제약조건 형식 >

CREATE TABLE(column datatype [(size)][DEFAULT default] [constraint]

                     ,  column datatype [(size)][DEFAULT default] [constraint], ... [, *constraint(column)])

--- 주의)테이블 수준 제약조건에서

           CONSTRAINT constraintname UNIQUE (column1, column2) 라고

           두 개의 컬럼명을 적어주게 되면,

            column1 AND column2, 즉 두 개의 컬럼 '모두' 중복값이 있을 때 에러 발생.(교집합)

 

 

< 서브쿼리를 이용하여 테이블 생성 >

 : 기존 테이블을 이용하여 새로운 테이블 생성 - 행 복사

-- 서브쿼리의 검색결과와 동일한 구조의 테이블 생성하여 검색행을 복사하여 삽입 
-- 컬럼명이 생략된 경우 서브쿼리의 컬럼명을 이용하여 테이블 생성 
-- 기존 테이블의 컬럼 자료형까지는 복사되지만 제약조건은 복사되지 않는다.

-- 서브쿼리 조건식 결과가 거짓인 경우, 에러가 떨어지지는 않지만 행 복사가 되지 않음

>> [형식] CREATE TABLE table_new[(column, ...)] AS SELECT 검색대상 FROM table_original [WHERE condition];

 

 

< 딕셔너리 DICTIONARY>

※ 딕셔너리(DICTIONARY) : 시스템 정보를 확인할 수 있는 가상의 테이블

 

딕셔너리 뷰(DICTIONARY VIEW)

: 데이터 딕셔너리의 내용을 사람이 이해할 수 있는 내용으로 변환하여 제공

 

USER_DICTIONARY(일반사용자)

-- USER_OBJECTS : 현재 접속 사용자의 객체 정보를 제공하는 딕셔너리

-- USER_TABLES : 현재 접속 사용자의 테이블 정보를 제공하는 딕셔너리

                       --USER_TABLES 대신 TABS 라고 표현해도 OK(동의어)

-- USER_TAB_COLUMNS : 현재 접속 사용자에게 테이블의 컬럼정보를 제공하는 딕셔너리

                       -- 테이블의 컬럼 기본값 확인 

-- USER_CONSTRAINT : 테이블에 지정된 제약조건 확인

 

DBA_DICTIONARY(관리자)

 

ALL_DICTIONARY(모든 사용자)