JUST DO, 데이터베이스! 🕺 - (7) FK, 뷰, 시퀀스, 인덱스

방대한 데이터의 세계로, JUST DO DBMS!

---

📁 FOREIGN KEY

• 외래키, 외부키, 참조키, 외부 식별자 등으로 불리고 흔히 FK라고 해요.
• FK가 정의된 테이블을 자식 테이블이라고 칭해요. 그래서 참조되는 테이블 즉, PK가 있는 테이블을 모두 부모 테이블이라고 해요.
• 부모테이블은 자식의 데이터나 테이블이 삭제된다고 영향을 받지 않아요.
• 참조하는 데이터 컬럼과 데이터 타입은 반드시 일치해야 하는데 참조할 수 있는 컬럼은 기본키(PK) 이거나 UNIQUE여야 해요.

⚫ FK의 사용 예

	--PK가 있는 부모 테이블 DADDY
    CREATE 	TABLE DADDY(
        IDX 	NUMBER(10),
        MID		NUMBER(10) PRIMARY KEY
    );

    -- 자식 테이블(DAUGHTER)에서 부모 테이블(DADDY) 쪽으로 FK를 걸어준다.
   	 CREATE 	TABLE	DAUGHTER(
        IDX		NUMBER(10) PRIMARY KEY,
        MID		NUMBER(10),
        -- FK_DA는 제약 조건의 알리아스다.
        CONSTRAINTS FK_DA FOREIGN KEY(MID)
        REFERENCES DADDY(MID) ON DELETE CASCADE
    );

    INSERT	INTO DADDY VALUES (1, 10);
    INSERT	INTO DADDY VALUES (2, 20);
    INSERT	INTO DADDY VALUES (4, 40);
    SELECT	* FROM DADDY;

    INSERT 	INTO DAUGHTER  VALUES (100, 10);
    INSERT 	INTO DAUGHTER  VALUES (101, 20);
    INSERT 	INTO DAUGHTER  VALUES (200, 20);
    INSERT 	INTO DAUGHTER  VALUES (201, 10);
    -- 부모테이블에서 정의되어 있지 않은 값으로 인해 제약조건에 오류가 발생했다.
    -- INSERT 	INTO DAUGHTER  VALUES (300, 30);
    INSERT 	INTO DAUGHTER  VALUES (300, 40);
    SELECT 	* FROM DAUGHTER d ;

    DELETE 	FROM DAUGHTER d WHERE IDX = 100;
    SELECT 	* FROM DAUGHTER d ;

    DELETE 	FROM DAUGHTER d WHERE IDX = 100;
    DELETE 	FROM DADDY d WHERE IDX = 1;

    -- ON DELETE CASCADE 이 옵션으로 인해 잘 처리가 되었다.
    -- ON DELETE CASCADE - 참조되는 부모 테이블의 행에 대한 DELETE를 허용
    SELECT 	* FROM DAUGHTER d ;

---

📁 삭제 옵션

📎 ON DELETE CASCADE

• 참조되는 부모 테이블의 행에 대한 DELETE를 허용해요.
• 즉, 참조되는 부모 테이블 값이 삭제되면 연쇄적으로 자식 테이블의 값 역시 삭제돼요.

📎 ON DELETE SET NULL

• 참조되는 부모 테이블의 행에 대한 DELETE를 허용해요.
• 부모테이블의 값이 삭제되면 해당 참조하는 자식 테이블의 값들은 NULL 값으로 설정돼요.

---

📁 VIEW(뷰)!

업로드중..

• VIEW는 TABEL(테이블)과 유사하며, 테이블처럼 사용해요.
• 테이블과는 달리 데이터를 저장하기 위한 물리적인 공간이 필요하지 않은 가상 테이블이에요.
• 테이터를 물리적으로 갖지 않지만 논리적인 집합을 갖고 테이블과 마찬가지로 SELECT, INSERT, UPDATE, DELETE 명령 가능해요.

⚫ VIEW의 사용 예

    -- VIEW
    SELECT	* FROM EMP_DETAILS_VIEW edv ;


    -- VIEW CREATE
    --CREATE  VIEW V_EMP2(EMP_ID, FIRST_NAME, JOB_ID, HIREDATE, DEPT_ID)
    --AS 
    --SELECT 	EMPLOYEE_ID , FIRST_NAME , JOB_ID , HIRE_DATE , DEPARTMENT_ID 
    --FROM 	EMPLOYEES e 
    --WHERE 	JOB_ID = 'ST_CLERK'
    --;

    SELECT 	* FROM V_EMP ve ;

    -- 삭제
    -- DROP VIEW V_EMP;
    -- DROP VIEW V_EMP2;

    CREATE 	VIEW V_EMP2(EMP_ID, COMM_PCT, FIRST_NAME, JOB_ID, HIREDATE, DEPARTMENT_ID)
    AS 
    SELECT	EMPLOYEE_ID
        ,	NVL(COMMISSION_PCT, 0)
        ,	FIRST_NAME 
        ,	JOB_ID 
        ,	HIRE_DATE 
        ,	DEPARTMENT_ID 
    FROM	EMPLOYEES e 
    ;

    -- 139, John
    SELECT 	* 
    FROM	V_EMP2 ve 
    WHERE	FIRST_NAME = 'John' 
    AND 	JOB_ID = 'ST_CLERK'
    ;

    UPDATE 	V_EMP2 SET FIRST_NAME = 'Kim'
    WHERE	FIRST_NAME = 'John' 
    AND 	JOB_ID = 'ST_CLERK'
    ;

    SELECT 	*
    FROM 	V_EMP2 ve 
    WHERE	FIRST_NAME = 'John' 
    AND 	JOB_ID = 'ST_CLERK'
    ;

    SELECT 	*
    FROM 	EMPLOYEES e 
    WHERE 	EMPLOYEE_ID = 139
    ;

    UPDATE 	EMPLOYEES SET FIRST_NAME = 'John'
    WHERE 	EMPLOYEE_ID = 139
    ;

⚫ VIEW에서의 READ ONLY

원본 파일에 변화없이 읽기만 가능하게 할 수 있어요.

	CREATE 	VIEW V_EMP3(EMP_ID, COMM_PCT, FIRST_NAME, JOB_ID, HIREDATE, DEPARTMENT_ID)
    AS 
    SELECT	EMPLOYEE_ID
        ,	NVL(COMMISSION_PCT, 0)
        ,	FIRST_NAME 
        ,	JOB_ID 
        ,	HIRE_DATE 
        ,	DEPARTMENT_ID 
    FROM	EMPLOYEES e 
    WITH	READ ONLY
    ;

    SELECT 	*
    FROM 	V_EMP3 ve 
    ;

    -- 옵션인 READ ONLY 로 인하여 UPDATE가 불가능하다.
    --UPDATE 	V_EMP3 SET FIRST_NAME = 'Kim'
    --WHERE	FIRST_NAME = 'John' 
    --AND 	JOB_ID = 'ST_CLERK'
    --;

---

📁 시퀀스(SEQUENCE)

• 연속적으로 번호를 만들어 주는 기능이에요.
• 자동으로, 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체에요.

📎 시퀀스 구분

	CREATE SEQUENCE 시퀀스 이름
	INCREMENT BY n	-- 증가값을 설정, 2 => 2씩 증가, 기본값 1
	START WITH n	-- 시작값을 설정, 기본값1
	MAXVALUE n	-- 시퀀스의 최대값을 설정
	MINVALUE n	-- 시퀀스의 최소값을 설정
	CYCLE / NOCYCLE	-- 시퀀스를 반복적으로 사용할지를 설정
	CACHE n		-- 시퀀스 속도를 개선하기위한 캐싱여부 지정

⚫ SEQUENCE의 활용 예

    -- 시퀀스
    CREATE SEQUENCE SEQ_SERIAL_NO
    INCREMENT BY 1
    START WITH	100
    MAXVALUE	110
    MINVALUE	99
    CYCLE 
    CACHE 2;

    CREATE TABLE GOOD(
        GOOD_NO		NUMBER(3), 
        GOOD_NAME	VARCHAR(10)
    );

    SELECT 	*
    FROM 	GOOD
    ;

    -- NEXTVAL : 다음 값 ()
    INSERT INTO GOOD VALUES (SEQ_SERIAL_NO.NEXTVAL, '제품5');

    -- CURRVAL : 현재 값 (CURRENT VALUE)
    INSERT INTO GOOD VALUES (SEQ_SERIAL_NO.CURRVAL, '제품5');

    SELECT	*
    FROM 	GOOD
    ;

    -- 현재 시퀀스 값이 얼마인지 아래와 같은 방법으로도 알 수 있다.
    SELECT 	SEQ_SERIAL_NO.CURRVAL FROM DUAL;

    -- 시퀀스 삭제
    DROP SEQUENCE SEQ_SERIAL_NO;

---

📁 INDEX

📎 인덱스란?

인덱스는 색인이라는 뜻으로 조회속도를 향상시키기 위한 데이터베이스 검색 기술이에요. 즉 인덱스가 필요한 이유는 인덱스를 생성해 줌으로서 조회 속도를 빠르게 할 수 있어요.

⚫ 인덱스가 불필요한 경우

• 데이터가 적은(수천만건 미만) 경우에는 인덱스를 설정하지 않는게 오히려 성능에 좋을 수 있어요.
• 조회보다 삽입, 수정, 삭제 처리가 많은 테이블인 경우에도 인덱스가 불필요해요.

📎 UNIQUE INDEX

• 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있어요.

	CREATE UNIQUE INDEX 인덱스명
	ON 테이블명(컬럼);

📎 NON-UNIQUE INDEX

• 인덱스를 사용한 컬럼에 중복 데이터값을 가질 수 있어요.

	CREATE INDEX 인덱스명
	ON 테이블명(컬럼);

⚫ INDEX의 활용 예

    -- INDEX
    SELECT 	* FROM  EMPLOYEES3 e ;

    CREATE	TABLE EMPLOYEESE3
    AS
    SELECT	* FROM EMPLOYEES e
    ;

    INSERT INTO EMPLOYEESE3 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
    VALUES (100, 'Ismael', 'Kim')
    ;

    SELECT 	* FROM  EMPLOYEESE3 ORDER BY EMPLOYEE_ID;

    -- UNIQUE INDEX 생성
    CREATE UNIQUE INDEX IDX_EMPLOYEE3_ID
    ON EMPLOYEESE3(EMPLOYEE_ID);

    DELETE FROM EMPLOYEESE3 e 
    WHERE FIRST_NAME = 'Ismael'
    AND LAST_NAME = 'Kim'
    ;

    -- UNIQUE INDEX가 생성된 테이블에는 동일한 데이터가 입력되지 않는다.
    --INSERT INTO EMPLOYEESE3 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
    --VALUES (100, 'Ismael', 'Kim')
    --;

    -- NON-UNIQUE INDEX
    CREATE INDEX IDX_EMPLOYEE3_FIRST
    ON EMPLOYEESE3(FIRST_NAME)
    ;

    INSERT INTO EMPLOYEESE3 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
    VALUES (500, 'Steven', 'King')
    ;

    SELECT * FROM EMPLOYEESE3 e 
    WHERE FIRST_NAME = 'Steven';

---

DBMS 기초 완료 ✔️

---

출처
https://media.giphy.com/media/1hVi7JFFzplHW/giphy.gif
https://media.giphy.com/media/jUwpNzg9IcyrK/giphy.gif