오라클 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(모든 사용자)