/*- 09_무결성_제약조건.sql -*/

/*
 * # 데이터 무결성 제약조건 ( Data Integrity Constraint Rule )
 * - 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해 테이블을 생성할 때,
 *   각 컬럼에 대해서 정의하는 여러가지 규칙.
 * 
 * # 무결석 제약 조건.
 * - NOT NULL
 *   > NULL 값을 허용하지 않음.
 *
 *   UNIQUE
 *   > 중복된 값을 허용하지 않고, 항상 유일한 값.
 *
 *   PRIMARY KEY
 *   > NULL을 허용하지 않고, 중복된 값도 허용하지 않음.
 *
 *   FOREIGN KEY
 *   > 참조되는 테이블의 컬럼에 값이 있으면 허용
 *
 *   CHECK
 *   > 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용.
 */

/*
 * # 제약 조건 확인
 * - USER_CONSTRAINTS 데이터 딕셔너리 뷰로 제약 조건에 관한 정보 확인가능.
 *   > CONSTRAINT_NAME : 제약 조건을 저장하는 컬럼
 *     CONSTRAINT_TYPE : 제약 조건 유형을 저장하는 컬럼
 *                       P - PRIMARY KEY
 *                       R - FOREIGN KEY
 *                       U - UNIQUE
 *                       C - CHECK, NOT NULL
 */

-- 현재 계정 소유의 테이블에 지정된 제약 조건 확인.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;

-- 제약 조건이 지정된 컬럼명 확인.
SELECT * FROM USER_CONS_COLUMNS;

/*
 * # NOT NULL
 * - 특정 컬럼은 반드시 값이 입력되도록 필수 입력 컬럼으로 지정.
 */
 
-- NOT NULL 제약 조건을 사용하지 않는 테이블
DROP TABLE EMP01;

CREATE TABLE EMP01(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(2) );

DESC EMP01;
SELECT * FROM EMP01;

-- 데이터 추가
INSERT INTO EMP01 VALUES (NULL, NULL, 'SALESMAN', 30);
SELECT * FROM EMP01;

-- NOT NULL 제약 조건을 사용하지 않는 테이블
DROP TABLE EMP02;

CREATE TABLE EMP02(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2) );

DESC EMP02;
SELECT * FROM EMP02;

-- 데이터 추가
INSERT INTO EMP02 VALUES (NULL, NULL, 'SALESMAN', 30);
SELECT * FROM EMP02;

/*
 * # UNIQUE
 * - 특정 컬럼에 대해서 중복된 데이터가 저장되지 않게 함.
 *   테이블 안에 유일한 값만 저장가능.
 */
 
DROP TABLE EMP03;

CREATE TABLE EMP03(
EMPNO NUMBER(4) UNIQUE,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2) );

DESC EMP03;
SELECT * FROM EMP03;

-- 데이터 추가
INSERT INTO EMP03 VALUES (1100, 'ORACLE', 'SALESMAN', 30);
SELECT * FROM EMP03;

-- 동일한 사원번호가 들어가면 ERROR
INSERT INTO EMP03 VALUES (1100, 'MARIA', 'SALESMAN', 30);
-- NULL값 반영은 됌 
INSERT INTO EMP03 VALUES (NULL, 'MARIA', 'SALESMAN', 30);
SELECT * FROM EMP03;

/*
 * # PRIMARY KEY
 * - NOT NULL과 UNIQUE를 결합한 제약 조건.
 */
 
DROP TABLE EMP04;

CREATE TABLE EMP04(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2) );

DESC EMP04;
SELECT * FROM EMP04;

-- 데이터 추가
INSERT INTO EMP04 VALUES (1100, 'ORACLE', 'DB', 70);
SELECT * FROM EMP04;

-- 동일한 사원번호, NULL 값이 들어가면 ERROR
INSERT INTO EMP04 VALUES (1100, 'MARIA', 'SALESMAN', 30);
INSERT INTO EMP04 VALUES (NULL, 'MARIA', 'SALESMAN', 30);
SELECT * FROM EMP04;

/*
 * # 참조 무결성 
 * - 테이블과 테이블 사이에서 발생하는 개념.
 * - 두 테이블 사이의 주종 관계가 종속되는 테이블, 즉 부모테이블과 자식테아불애 의해서 결정.
 */
 
/*
 * # FOREIGN KEY 
 * - 사원 테이블의 부서번호는 반드시 부서 테이블에 존재하는 부서번호만 입력되어야 함.
 *   사원 테이블이 부서 테이블의 부서번호를 참조 가능하도록 함.
 * - 부모 키가 되기 위한 컬럼은 반드시 부모 테이블의 PRIMARY KEY 또는 UNIQUE로 설정되어 있어야 함.
 */

-- FOREIGN KEY 제약 조건을 사용하는 테이블 
DROP TABLE EMP05;

CREATE TABLE EMP05(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2) REFERENCES DEPT(DEPTNO)
);

DESC EMP05;
SELECT * FROM EMP05;

-- 데이터 추가
INSERT INTO EMP05 VALUES (1100, 'MARIA', 'SALESMAN', 30);
SELECT * FROM EMP05;

-- 부모부서번호에 없는 데이터 값은 에러.
INSERT INTO EMP05 VALUES (1100, 'MARIA', 'SALESMAN', 50);

-- 부서 테이블에 부서번호 50 추가
INSERT INTO DEPT VALUES (50, 'DATABASE', 'KOR');
SELECT * FROM DEPT;

INSERT INTO EMP05 VALUES (1300, 'MYSQL', 'DB', 50);
SELECT * FROM EMP05;

/*
 * # CHECK 
 * - 입력되는 값을 확인하여 설정된 값 이외의 값이 들어오면 명령이 수행되지 않음.
 * - 조건으로 데이터 값의 범위나 특정 패턴의 숫자나 문자 값을 설정할 수 있음.
 */
 
 -- CHECK 제약 조건을 사용하는 테이블 
DROP TABLE EMP06;

CREATE TABLE EMP06(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10) NOT NULL,
SAL NUMBER(7,2) CHECK(SAL BETWEEN 500 AND 10000),
GENDER VARCHAR2(1) CHECK(GENDER IN ('M','F'))
);

DESC EMP06;
SELECT * FROM EMP06;

INSERT INTO EMP06 VALUES(1200, 'PYTHON', 1100, 'F');
INSERT INTO EMP06 VALUES(1300, 'JAVA', 700, 'M');
SELECT * FROM EMP06;

/*
 * # DEFAULT
 * - 아무런 값이 입력되지 않으면, DEFAULT 설정한 값이 입력.
 */
 
-- 연습용 테이블
DROP TABLE DEPT01;

CREATE TABLE DEPT01(
DEPTNO NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(10) NOT NULL,
LOC VARCHAR2(10) DEFAULT 'SEOUL'
);

DESC DEPT01;
SELECT * FROM DEPT01;

-- 지역을 설정하지 않은 데이터 추가 
INSERT INTO DEPT01 (DEPTNO,DNAME) VALUES (50,'DATABASE');
SELECT * FROM DEPT01;

/* 
 * # 제약 조건명을 자성해서 제약 조건 설정
 * - 제약 조건을 설정할 때 제약 조건명을 지정할 수 있음.
 *   제약 조건명을 지정하지 않으면 자동으로 제약 조건명을 부여.
 *   > 오라클이 부여하는 제약 조건명은 SYS_ 다음 숫자나열.
 * - 제약 조건명 지정 방법
 *   column_name data_type CONSTRAINT constraint_name constraint_type
 */

DROP TABLE EMP07;

CREATE TABLE EMP07(
EMPNO NUMBER(2) CONSTRAINT EMP07_PK_EMPNO PRIMARY KEY,
ENAME VARCHAR2(10) CONSTRAINT EMP07_NN_ENAME NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2) CONSTRAINT EMP07_FK_DEPTNO REFERENCES DEPT(deptno)
);

DESC DEPT01;
SELECT * FROM DEPT01;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('EMP07');