KH 51일차 - DDL / 제약조건 (**)

티스토리 뷰

KH 정보교육원 [ Java ]

KH 51일차 - DDL / 제약조건 (**)

monimoni 2022. 5. 10. 23:07

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

-- 1. DDL (Data Definition Language) - 테이블을 만들때 사용한다.

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

-- 데이터베이스 구조(= 오라클(DB) 객체)를 생성/수정/삭제하는데,

-- 사용하는 SQL문장.

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

-- 가. 자동으로 COMMIT 됨. (즉, DB에 자동 영구 반영됨) (**)

-- 나. 데이터 딕셔너리(Data Dictionary) 에 정보 저장.

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

-- 다. 오라클 객체(= 데이터베이스 구조)의 종류(크게 5가지)

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

-- (1) 테이블(Table) - 목표 : 데이터 저장

-- 기본적인 데이터 저장 단위로, 행과 열로 구성된 객체

-- (2) 인덱스(Index) - 목표 : 데이터 검색성능 향상 (***)

-- 테이블에 저장된 데이터의 검색 성능 향상 목적을 위한 객체

-- (3) 뷰(View) - 목표 : 가상의 테이블

-- 한 개 이상의 테이블의 논리적인 부분 집합을 표시할 수 있는 객체

-- (4) 시퀀스(Sequence) - 목표 : 순차번호 생성기 (인조키, 대리키)

-- 테이블의 특정 컬럼값에 숫자 값 자동 생성 목적을 위한 객체

-- (5) 동의어(Synonym) - 목표 : 테이블의 별칭

-- 객체에 대한 동의어를 설정하기 위한 객체

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

-- 라. SQL문장 종류

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

-- (1) CREATE - DB객체(= Oracle 객체) 생성

-- (2) ALTER - DB객체(= Oracle 객체) 변경

-- (3) DROP - DB객체(= Oracle 객체) 삭제

-- (4) RENAME - DB객체(= Oracle 객체) 이름 변경

-- (5) TRUNCATE - DB객체(= Oracle 객체) 정보 절삭 (데이터 완전 삭제 - 복구 불가능)

-- + DELETE는 복구가 가능하다.

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

-- (1) Table 생성

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

-- 가. DB에서 가장 중요한 객체

-- 나. 관리할 실제 데이터를 저장하는 객체

-- 다. 이름은 의미있고 사용하기 쉽게 설정하는 것이 중요

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

-- Basic Syntax:

-- CREATE TABLE [스키마].테이블명 (

-- 컬럼명 데이터타입 [DEFAULT 값 | 제약조건][,

-- ... ]

-- );

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

-- * 스키마(Schema) ( = 계정 스키마 )

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

-- 가. 사용자가 DB에 접근하여, 생성한 객체들의 대표이름!!

-- 나. By default, 사용자 계정명과 동일하게 부여됨!! (ex. HR, SCOTT)

-- 다. 생성한 객체들의 소유자는 해당 객체를 생성한 사용자 계정

-- 라. 다른 스키마에 속한 객체 접근은 기본적으로 불가 (접근권한필요) (**)

-- 마. 만일 다른 스키마 내의 객체에 대한 접근권한이 있다면,

-- 항상 "스키마.객체" 형식으로 사용해야 됨. (*)

-- "스키마"를 생략하면, 현재 자신의 스키마 내에서 객체 찾음

-- 바. 자신의 스키마 내의 객체 접근 시에는, 스키마 이름 생략가능 (*)

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

SELECT

deptno,

dname,

loc

FROM

SCOTT.dept;

-- SCOTT계정 밑에 있는 dept 테이블을 조회할 수 있다. (하지만 다른 계정에서는 접근권한이 필요하기에 안된다.) (**)

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

-- * Oracle Data Types (컬럼에 저장되는 데이터의 자료형) (***)

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

-- (1) CHAR(size) - 고정길이 문자저장 (1<= size <= 2000, byte)

-- a. 지정길이보다 작은 데이터 입력 -> 나머지 공간은 공백으로 채워짐

-- b. 입력할 데이터 크기가 유동적 -> 공간낭비 초래

-- c. 고정크기 데이터 저장에 사용 -> ex. 주민번호, 우편번호, 전화번호,...

-- (2) VARCHAR2(size) - 가변길이 문자저장 (1<= size <= 4000, byte)

-- a. 지정길이보다 작은 데이터 입력 -> 입력문자열 길이만큼만 공간할당 (**)

-- b. 저장공간을 매우 효율적으로 사용가능

-- (3) NVARCHAR2(size) - 가변길이 문자저장 (1<= size <= 4000, byte)

-- (4) NUMBER(p, s) - 가변길이 숫자저장 (p: 소수점자릿수, S: 전체자릿수 - 자릿수는 정수로 표현) (**)

-- a. p: precision (정밀도), s: scale (스케일)

-- (5) DATE - 날짜 및 시간 저장

-- (6) ROWID - 테이블 행의 고유주소(18문자로 구성) 저장 (*)

-- a. 테이블에 실제 행이 저장되어 있는 논리적인 주소값

-- b. globally Unique value in the database

-- c. 테이블에 새로운 행이 삽입되면, 자동생성됨

-- d. 인덱스(Index) 내에 저장된 데이터임

-- 실제 행이 저장된 주소값을 직접 알 수 있기 때문에, 검색속도가 빠르게 됨.

-- e. 문자구성(*) (- 일정한 체계를 가지고 있음)

-- (7) BLOB - 대용량 이진데이터 저장 (최대 4GB)

-- a. LOB == Large OBject (3가지: BLOB, CLOB, BFILE)

-- b. 대용량의 텍스트/바이너리 데이터 저장 (ex, 이미지, 동영상, 사운드 데이터)

-- (8) CLOB - 대용량 텍스트데이터 저장 (최대 4GB)

-- a. 대용량의 텍스트 데이터 저장(ex, e-book)

-- (9) BFILE - 대용량 이진데이터를 파일형태로 저장 (최대 4GB)

-- + 개발자의 경우 주로 (1) ~ (6)까지가 자주 사용된다.

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

SELECT

rowid,

deptno

FROM

dept;

-- rowid를 통해 각 행(데이터)의 rowid(주소)를 출력할 수 있다.

-- 테이블 내의 특정 행을, ROWID 값으로, 빠르게 조회가 가능하다.

-- 인덱스 내에 ROWID가 저장되어 있기 때문에 인덱스(index)를 사용할 때, 간접적으로 ROWID를 사용한다.

SELECT

FROM

dept

WHERE

ROWID = 'AAASMaAAAAAACVGAAA';

-- rowid를 이용한 조건 검색

-- 이때 rowid는 실제로 있는 rowid를 활용해야 한다.

CREATE TABLE scott.employee(

empno NUMBER(4),

ename VARCHAR2(20),

hiredate DATE,

sal NUMBER(7, 2)

); -- employee 테이블 생성

-- 이 경우에는 4자리 숫자의 empno, 20바이트 크기의 ename, 날짜 타입의 hiredate, 소숫점 7자리까지 표시하는 2크기의 숫자타입의 sal 컬럼을 가지는 employee테이블이 SCOTT계정에 생성된다.

DESC employee;

-- 생성한 테이블의 컬럼의 목록 확인하기

-- 제약조건을 작성하지 않으면 기본적으로 NULL제약조건이 자동으로 들어가게 된다.

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

-- * Default 옵션

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

-- 가. 테이블에 데이터 저장 시, 특정 컬럼에 값을 지정하지 않으면,

-- 자동으로 NULL 값이 저장됨

-- 나. 컬럼에 값을 지정하지 않아도, 자동으로 기본값 입력

-- 다. NULL 값이 입력 방지

-- 라. 현재 날짜/성별 같은, 고정된 값만 가지는 컬럼에 대해 사용

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

CREATE TABLE employee2 (

empno NUMBER(4),

ename VARCHAR2(20),

hiredate DATE DEFAULT sysdate,

sal NUMBER(7, 2)

); -- employee2 테이블 생성 ( DEFAULT 옵션 사용 )

DESC employee2;

-- default 제약조건의 경우 desc로는 나타나지 않는다.

INSERT INTO employee2 (empno, ename, sal)

VALUES ( 10, '홍길동', 3000 );

-- hiredate 컬럼의 데이터는 제외한 컬럼들에게 데이터를 넣어 주었다.

SELECT

FROM

employee2;

-- 데이터가 정상적으로 삽입되었는지 확인해 본다.

-- 데이터를 넣지 않았던 hiredate 칼럼의 경우 default값인 sysdate가 값으로 들어가 있음을 알 수 있다.

commit;

-- commit을 해야지 다른 계정이나 다른 사람이 변경된 데이터를 볼 수 있다.

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

-- * 제약조건(Contraints)

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

-- 가. 테이블에 부적절한 데이터가 저장되는 것을 방지 (*)

-- 나. 테이블 생성시, 각 컬럼에 대해서 정의하는 규칙

-- 다. DB 설계단계에서, 데이터의 무결성을 보장하기 위한 수단 (**)

-- 라. 예: 성별('남','여'), 부서(NULL 값이 없어야 함)

-- 마. 데이터가 저장되기 전에, 무결성을 검사하여,

-- 잘못된 데이터가 저장되는 것을 방지

-- 바. 테이블에 행이 삽입(INSERT)/수정(UPDATE)/삭제(DELETE)

-- 될 때마다 적용

-- 사. 필요 시, 제약조건의 기능을 일시적으로,

-- 활성화(Enable)/비활성화(Disable) 가능 (***)

-- 아. USER_CONSTRAINTS 데이터 딕셔너리에 저장

-- 자. 제약조건의 종류:

-- (1) NOT NULL - Only ( column-level )

-- a. 해당 컬럼값으로 NULL 허용하지 않는다

-- (2) UNIQUE - Both ( column-level ) and ( table-level )

-- a. 해당 컬럼값은 항상 유일한 값을 갖는다

-- b. ( NOT NULL 허용 )

-- (3) PRIMARY KEY - Both ( column-level ) and ( table-level ) (***)

-- a. 해당 컬럼값은 반드시 존재해야 하고 유일해야 한다

-- b. ( NOT NULL + UNIQUE )

-- c. 해당 테이블에서 각 행들을 유일하게 구분해주는 식별기능

-- d. 테이블 당, 반드시 하나만 가질 수 있음

-- e. 단순/복합 컬럼으로 구성 가능

-- f. UNIQUE 성질로 인하여, 자동으로 UNIQUE INDEX 생성됨

-- g. 이 기본키를 이용한 데이터 검색은, 자동생성된 인덱스로 인하여

-- 속도가 매우 빠르다

-- (4) FOREIGN KEY - Both ( column-level ) and ( table-level ) (***)

-- a. 해당 컬럼의 값이, 다른 테이블의 컬럼의 값을 참조해야 한다 (*)

-- b. 참조되는 컬럼에 없는 값은 저장이 불가능하다 (*)

-- c. ( NULL 허용 )

-- (5) CHECK - Both ( column-level ) and ( table-level ) (***)

-- a. 해당 컬럼에 가능한, 데이터 값의 범위나 사용자 조건을 지정한다

-- 차. 제약조건의 이름짓기 (**)

-- a. 명명방법: CONSTRAINT table_column_2자리제약조건종류 (*권장*)

-- - NOT NULL: table_column_nn

-- - UNIQUE: table_column_uk

-- - PRIMARY KEY: table_column_pk

-- - FOREIGN KEY: table_column_fk

-- - CHECK: table_column_ck

-- b. 제약조건명으로, 기능을 활성화/비활성화 할 수 있음

-- c. 제약조건명을 직접 지정하지 않으면, 오라클이 자동으로 명명함

-- d. Oracle의 제약조건명 형식: Prefix(SYS_) + 자동이름

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

-- * Column-level: 테이블 생성시,

-- 가. 각 컬럼을 정의하면서, 같이 제약조건을 지정하는 방법

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

-- Basic Syntax:

-- CREATE TABLE [스키마].테이블명(

-- 컬럼명 데이터타입 [ CONSTRAINT 제약조건명 ] PRIMARY KEY,

-- 컬럼명 데이터타입,

-- ...

-- );

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

DESC user_constraints;

SELECT

FROM

user_constraints

WHERE

table_name = 'DEPARTMENT';

-- 해당 테이블의 제약조건을 출력한다. ( 디벨로퍼에서 실행 )

-- 컬럼 레벨(column-level) 방식의 기본 키(PRIMARY KEY) 설정

CREATE TABLE department(

deptno NUMBER(2) CONSTRAINT department_deptno_pk PRIMARY KEY,

dname VARCHAR2(15),

loc VARCHAR2(15)

);

COMMIT;

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

-- * Table-level: 테이블 생성시,

-- 가. 모든 컬럼을 정의하고, 맨 마지막에 제약조건을 추가하는 방법

-- 나. 하나의 컬럼에 여러 개의 제약조건을 부여할 경우에 사용

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

-- Basic Syntax:

-- CREATE TABLE [스키마].테이블명 (

-- 컬럼명1 데이터타입,

-- ...

-- 컬럼명n 데이터타입,

-- [CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명[,컬럼명2, ...])

-- );

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

-- 테이블 레벨(table-level) 방식의 기본 키(PRIMARY KEY) 설정

CREATE TABLE department2 (

deptno NUMBER(2),

dname VARCHAR2(15),

loc VARCHAR2(15),

CONSTRAINT department2_deptno_pk PRIMARY KEY (deptno)

);

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

-- USER_CONSTRAINTS 데이터 사전 (**)

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

-- 특정 테이블의 제약조건 확인 (어떤 컬럼에 제약조건이 설정되어 있는지 확인불가)

-- 제약조건 타입(CONSTRAINT_TYPE 컬럼):

-- P: PRIMARY KEY, R: FOREIGN KEY, U: UNIQUE,

-- C: NOT NULL, CHECK (NOT NULL 제약조건은, NULL 값을 체크하는 조건으로 처리)

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

DESC USER_CONSTRAINTS;

SELECT

FROM

USER_CONSTRAINTS

WHERE

table_name IN ('DEPARTMENT','DEPARTMENT2');

-- 테이블명은 대문자로 작성해야지 출력이 된다. (***)

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

-- USER_CONS_COLUMNS 데이터 사전 (**)

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

-- 어떤 컬럼에 제약조건이 설정되어 있는지 확인가능

DESC USER_CONS_COLUMNS;

SELECT

FROM

USER_CONS_COLUMNS

WHERE

table_name IN ('DEPARTMENT', 'DEPARTMENT2');

-- 어떤 칼럼에 어떤 제약조건이 설정되어 있는지 출력해 준다.

-- 테이블명은 대문자로 작성해야지 출력이 된다. (***)

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

-- (1) PRIMARY KEY 제약조건

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

-- 컬럼 레벨(column-level) 방식의 기본 키(PRIMARY KEY) 설정

CREATE TABLE department3(

deptno NUMBER(2)

CONSTRAINT

department3_deptno_pk

PRIMARY KEY,

dname VARCHAR2(15),

loc VARCHAR2(15)

);

-- 제약조건의 이름은 중복되면 안된다.

-- 테이블 레벨(table-level) 방식의 복합컬럼 기반의 기본키(PRIMARY KEY) 설정

CREATE TABLE department4(

deptno NUMBER(2),

dname VARCHAR2(15),

loc VARCHAR2(15),

-- table-level 제약조건 정의하는 경우에만, (***)

-- 두 개 이상의 컬럼(복합컬럼)에 제약조건 정의가능

CONSTRAINT department4_deptno_pk PRIMARY KEY(deptno, loc)

);

SELECT

FROM

USER_CONSTRAINTS

WHERE

table_name IN ('DEPARTMENT3','DEPARTMENT4');

DESC USER_CONS_COLUMNS;

SELECT

FROM

USER_CONS_COLUMNS

WHERE

table_name IN ('DEPARTMENT3', 'DEPARTMENT4');

-- 중복으로 지정하였어도 각각의 컬럼에 제약조건이 지정되어 있음을 알 수 있다.

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

-- 중복저장 불가 (Primary Key)

INSERT INTO department (deptno, dname, loc)

VALUES (10, '인사', '서울');

-- ORA-00001: unique constraint (SCOTT.DEPARTMENT_DEPTNO_PK) violated

INSERT INTO department(deptno, dname, loc)

VALUES (10, '개발', '경기');

-- null 값 저장 불가

-- ORA-01400: cannot insert NULL into ("SCOTT"."DEPARTMENT"."DEPTNO")

INSERT INTO department(deptno, dname, loc)

VALUES (NULL, '개발', '경기');

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

-- (2) UNIQUE 제약조건

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

-- 가. 기본키가 아닌 경우에도, 컬럼의 모든 데이터가 유일해야 하는

-- 경우에 지정

-- 나. 자동으로 UNIQUE INDEX 생성 -> 빠른 검색효과

-- 다. 기본키(Primary Key)와의 차이점:

-- a. 하나의 테이블에, 여러개의 UNIQUE 제약조건 지정가능

-- b. null 값도 저장가능***

-- 라. 제약조건명 형식: table_column_uk

-- 마. 단순/복합 컬럼에 대해 지정가능

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

-- Basic Syntax1: column-level

-- CREATE TABLE [스키마].테이블명 (

-- 컬럼명1 데이터타입 [CONSTRAINT 제약조건명] UNIQUE,

-- 컬럼명2 데이터타입,

-- ...

-- );

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

-- column-level

CREATE TABLE department5(

deptno NUMBER(2)

CONSTRAINT department5_deptno_pk PRIMARY KEY,

dname VARCHAR2(15)

CONSTRAINT department5_dname_uk UNIQUE,

loc VARCHAR2(15)

);

-- 제약조건을 확인할 때에는 디벨로퍼를 사용하는 것이 좋다.

-- 중복저장 불가 (Primary Key)

INSERT INTO department5 (deptno, dname, loc)

VALUES (10, '인사', '서울');

-- ORA-00001: unique constraint (SCOTT.DEPARTMENT5_DNAME_UK) violated

INSERT INTO department5 (deptno, dname, loc)

VALUES (20, '인사', '경기');

-- null 값 저장 허용 ( PK와의 차이점 ) (**)

INSERT INTO department5 (deptno, dname, loc)

VALUES (30, NULL, '서울');

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

-- Basic Syntax2: table-level

-- CREATE TABLE [스키마].테이블명 (

-- 컬럼명1 데이터타입,

-- 컬럼명2 데이터타입,

-- ...

-- [CONSTRAINT 제약조건명] UNIQUE(컬럼명1 [,컬럼명2])

-- );

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

-- table-level

CREATE TABLE department6(

deptno NUMBER(2)

CONSTRAINT department6_deptno_pk PRIMARY KEY,

dname VARCHAR2(15),

loc VARCHAR2(15),

CONSTRAINT department6_dname_uk UNIQUE(dname, loc)

);

-- 중복저장 불가 (Primary Key)