[국비지원] KH 정보교육원 46일차

kh day 046

이번주 공지사항

1. SQL 능력단위 늦어지는 중 -> 문제없다 크게 늦은 것 아니다.

2. 이번주 훈련방향
  가. DQL, DML 완료
  나. DDL, TCL, DCL 완성 계획
  다. 빠르면 이번주에 UI구현 들어간다.

3. 조금만 더 견디고 함께 협업하자. 현실과 미래에 대한 불안과 두려움이 있을 것이다.
   이러한 상황을 확실히 눈에 띄게, 손에 잡히게 나의 미래를 열어갈 날이 얼마 남지 않았다.

------------------------------------------------------
CHECK 제약조건

가. 해당 컬럼에 저장되는 데이터를 검사하여, 조건과 일치하는 데이터만 저장가능
나. 제약조건명 형식: table_column_ck
------------------------------------------------------
Basic Syntax1: column-level

CREATE TABLE [스키마].테이블명 (
    컬럼명1 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건식),
    컬럼명2 데이터타입,
    ...
);
------------------------------------------------------


CREATE TABLE department8 (
    deptno  NUMBER(2) ,
    dname   VARCHAR2(15)
        CONSTRAINT 
            department8_dname_ck 
                CHECK( dname IN('개발','인사') ),
    loc     VARCHAR2(15)
);


-- CHECK 제약조건에 부합
INSERT INTO department8 (deptno, dname, loc)
VALUES (10, '개발', '서울');


INSERT INTO department8 (deptno, dname, loc)
VALUES (20, '인사', '경기');


-- CHECK 제약조건에 위배
-- ORA-02290: check constraint (SCOTT.DEPARTMENT8_DNAME_CK) violated
INSERT INTO department8 (deptno, dname, loc)
VALUES (30, '개발부', '서울');


SELECT    *
FROM    department8;

------------------------------------------------------
Basic Syntax2: table-level

CREATE TABLE [스키마].테이블명 (
    컬럼명1 데이터타입 ,
    컬럼명2 데이터타입,
    ... ,
    [CONSTRAINT 제약조건명] CHECK(조건식)
);
------------------------------------------------------

CREATE TABLE department9 (
    deptno  NUMBER(2) ,
    dname   VARCHAR2(15),
    loc     VARCHAR2(15),

    CONSTRAINT 
        department9_dname_ck 
            CHECK( dname IN('개발','인사') ),

    CONSTRAINT
        department9_loc_ck 
            CHECK( loc IN('서울','경기') )
);


-- CHECK 제약조건에 부합
INSERT INTO department9 (deptno, dname, loc)
VALUES (10, '개발', '서울');


INSERT INTO department9 (deptno, dname, loc)
VALUES (20, '인사', '경기');


-- CHECK 제약조건에 위배
-- ORA-02290: check constraint (SCOTT.DEPARTMENT9_DNAME_CK) violated
INSERT INTO department9 (deptno, dname, loc)
VALUES (30, '개발부', '서울');


SELECT    *
FROM    department9;

------------------------------------------------------
FOREIGN KEY 제약조건 (= 참조무결성 제약조건)

가. '외래키' 또는 '참조키' 라고 부름
나. 자식 테이블에서 부모 테이블을 참조할 때, 올바른 데이터만 참조 가능하도록 제약하는 방법
다. null 값 허용
라. 제약조건명 형식: table_column_fk
------------------------------------------------------


-- deptno: 외래키(foreign key) or 참조키(reference key)
SELECT
    empno,
    deptno
FROM
    emp;


-- deptno: 기본키(primary key)
SELECT    *
FROM    dept;

-- 참조키는 기본키가 갖고 있는 값만 가질 수 있다



-- 참조 무결성 제약조건 위배 (참조키는 기본키가 갖고 있는 값만 가질 수 있다)
-- ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found
INSERT INTO emp (empno, ename, deptno)
VALUES (9000, 'John', 50);

------------------------------------------------------
Basic Syntax1: column-level

CREATE TABLE [스키마].테이블명 (
    컬럼명1 데이터타입 [CONSTRAINT 제약조건명] REFERENCES 부모테이블명(컬럼명),
    컬럼명2 데이터타입,
    ...
);

** 주의1 ** :
참조하는 부모테이블의 컬럼은, 
반드시 1) 기본키(Primary Key) 또는 2) UNIQUE 제약조건이 설정된 컬럼 이어야 함!!!
PK/UK 아닌 컬럼을, 왜래키 제약조건으로 설정 시도 -> 오류발생

** 주의2 ** :
외래키는, 부모 테이블과 자식 테이블 간의 참조 무결성을 위한 제약조건이기 때문에, 
자식 테이블에서 참조하게 되는 컬럼을 부모 테이블에서 기본키 또는 UNIQUE로 지정해 두어야 함!!!
-- ------------------------------------------------------

-- 부모 테이블 생성
CREATE TABLE dept02 (
    deptno  NUMBER(2)       CONSTRAINT dept02_deptno_pk PRIMARY KEY,
    dname   VARCHAR2(15),
    loc     VARCHAR2(15)
);


INSERT INTO dept02 (deptno, dname, loc)
VALUES (10, '인사', '서울');

INSERT INTO dept02 (deptno, dname, loc)
VALUES (20, '개발', '광주');

INSERT INTO dept02 (deptno, dname, loc)
VALUES (30, '관리', '부산');

INSERT INTO dept02 (deptno, dname, loc)
VALUES (40, '영업', '경기');

---


-- 자식 테이블 생성
CREATE TABLE emp02 (
    empno   NUMBER(4)       CONSTRAINT emp02_empno_pk PRIMARY KEY,
    ename   VARCHAR2(15),
    deptno  NUMBER(2)       CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno)
);


INSERT INTO emp02 (empno, ename, deptno)
VALUES (1000, 'John', 10);

INSERT INTO emp02 (empno, ename, deptno)
VALUES (2000, 'Smith', 20);

INSERT INTO emp02 (empno, ename, deptno)
VALUES (3000, 'Sam', NULL);
-- 외래키는  NULL도 허용


INSERT INTO emp02 (empno, ename, deptno)
VALUES (4000, 'Mike', 50);
-- ORA-02291: integrity constraint (SCOTT.EMP02_DEPTNO_FK) violated 
-- parent key not found
-- 부모 테이블의 기본키에 없는 값을 참조키로 삼을 수 없음


------------------------------------------------------
Basic Syntax2: table-level

CREATE TABLE [스키마].테이블명 (
    컬럼명1 데이터타입,
    컬럼명2 데이터타입,
    ...,
    [CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명n) REFERENCES 부모테이블명(컬럼명)
);
------------------------------------------------------

CREATE TABLE emp03 (
    empno   NUMBER(4)       CONSTRAINT emp03_empno_pk PRIMARY KEY,
    ename   VARCHAR2(15),
    deptno  NUMBER(2),

    CONSTRAINT emp03_deptno_fk FOREIGN KEY(deptno) REFERENCES dept02(deptno)
);


INSERT INTO emp03 (empno, ename, deptno)
VALUES (1000, 'John', 10);

INSERT INTO emp03 (empno, ename, deptno)
VALUES (2000, 'Smith', 20);

INSERT INTO emp03 (empno, ename, deptno)
VALUES (3000, 'Sam', NULL);

INSERT INTO emp03 (empno, ename, deptno)
VALUES (4000, 'Mike', 50);
-- FOREIGN KEY 제약조건 위배
-- ORA-02291: integrity constraint (SCOTT.EMP03_DEPTNO_FK) violated 
-- parent key not found
-- 부모 테이블의 기본키에 없는 값을 참조키로 삼을 수 없음


------------------------------------------------------
참조 무결성 제약조건 위배

-- 특정부서(부모)를 참조하고 있는 사원(자식)들이 있으므로, 부서를 삭제할 수 없음 (참조관계 존재)

DELETE FROM dept02
WHERE deptno = 10;      -- 부모 테이블의 특정부서 삭제

-- ORA-02292: integrity constraint (SCOTT.EMP02_DEPTNO_FK) violated 
-- child record found


*해결방법(2가지)
 ------------------------------------------------------
1) ON DELETE CASCADE
참조하는 부모테이블의 행이 삭제되면, 해당 행을 참조하는 자식테이블의 행도 연쇄삭제 됨.
------------------------------------------------------

-- 자식 테이블 만들 때 ON DELETE CASCADE 붙여서 이렇게 만들면 된다

CREATE TABLE emp02 (
    empno   NUMBER(4)       CONSTRAINT emp02_empno_pk PRIMARY KEY,
    ename   VARCHAR2(15),
    deptno  NUMBER(2) CONSTRAINT emp02_deptno_fk 
        REFERENCES dept02(deptno) ON DELETE CASCADE
);


INSERT INTO emp02 (empno, ename, deptno)
VALUES (1000, 'John', 10);

INSERT INTO emp02 (empno, ename, deptno)
VALUES (2000, 'Smith', 20);

INSERT INTO emp02 (empno, ename, deptno)
VALUES (3000, 'Sam', NULL);


DELETE FROM dept02
WHERE deptno = 10;      
-- 부모 테이블의 특정부서 삭제 이제 가능

------------------------------------------------------
2) ON DELETE SET NULL
참조하는 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 컬럼값을 NULL로 설정한다.
------------------------------------------------------

-- 자식 테이블 만들 때 ON DELETE SET NULL 붙여서 이렇게 만들면 된다

CREATE TABLE emp02 (
    empno   NUMBER(4)      CONSTRAINT emp02_empno_pk PRIMARY KEY,
    ename   VARCHAR2(15),
    deptno  NUMBER(2) CONSTRAINT emp02_deptno_fk 
            REFERENCES dept02(deptno) ON DELETE SET NULL
);


INSERT INTO emp02 (empno, ename, deptno)
VALUES (1000, 'John', 10);

INSERT INTO emp02 (empno, ename, deptno)
VALUES (2000, 'Smith', 20);

INSERT INTO emp02 (empno, ename, deptno)
VALUES (3000, 'Sam', NULL);


DELETE FROM dept02
WHERE deptno = 10;      -- 부모 테이블의 특정부서 삭제 이제 가능

------------------------------------------------------
테이블 삭제

가. 삭제되는 테이블에 저장된 모든 데이터/관련 인덱스/외래키 제약조건을 제외한, 모든 제약조건이 같이 삭제된다.
나. 외래키 제약조건은 자동으로 삭제되지 않기 때문에 
    자식 테이블에서 부모테이블을 참조하는 상황에서, 
    부모 테이블을 삭제하면, 종속성에 의해서, 삭제가 안됨.
    
    이 경우에 CASCADE CONSTRAINTS 옵션을 지정하여 삭제하면, 
    연쇄적으로 제약조건도 함께 삭제되기 때문에, 부모 테이블 삭제가능
------------------------------------------------------
DROP TABLE 테이블명 [CASCADE CONSTRAINTS];
------------------------------------------------------


------------------------------------------------------
Flashback Drop

가. 삭제된 테이블을 복구하는 방법 (from Oracle10g)
나. 테이블 삭제할 때, (DROP TABLE tablename;)
    삭제된 테이블은 휴지통(RECYCLEBIN)이라는 특별한 객체에,
    'BIN$' prefix가 붙은, 이름으로 저장됨.
다. 삭제된 테이블을 다시 복구하고 싶을 때, Flashback Drop
    복구기술을 이용하여, 휴지통(RECYCKEBIN) 객체에서, 삭제된 테이블을 복구할 수 있다.
------------------------------------------------------
* Flashback Drop Commands *

SHOW RECYCLEBIN;               -- RECYCLEBIN 객체정보 조회 (SQL Developer에서 하셈)

FLASHBACK TABLE tablename TO BEFORE DROP;   -- 삭제된 테이블 복구

DROP TABLE tablename PURGE;                 -- 테이블 완전삭제(복구불가)

PURGE RECYCLEBIN;                           -- RECYCLEBIN 객체정보 삭제 (영구삭제)
------------------------------------------------------


------------------------------------------------------
테이블 변경

가. 생성된 테이블의 구조를 변경
    a. 컬럼의 추가/삭제
    b. 컬럼의 타입/길이 변경
    c. 컬럼의 제약조건 추가/삭제
나. ALTER TABLE 문장 사용
다. 테이블의 구조변경은 기존 저장된 데이터에 영향을 주게 됨
------------------------------------------------------

------------------------------------------------------
(1) 컬럼 추가 (ALTER TABLE ADD 문장)

 a. 기존 테이블에 새로운 컬럼 추가
 b. 추가된 컬럼은, 테이블의 마지막에 추가
 c. 데이터는 자동으로 null 값으로 저장됨
 d. DEFAULT 옵션 설정도 가능
------------------------------------------------------
ALTER TABLE 테이블명
ADD ( 컬럼명1 데이터타입 [, ..., 컬럼명n 데이터타입] );
------------------------------------------------------

ALTER TABLE emp04
ADD ( 
        email VARCHAR2(10),
        address VARCHAR2(20) 
);

------------------------------------------------------
(2) 컬럼 변경 (ALTER TABLE MODIFY 문장)

a. 기존 테이블에 기존 컬럼 변경
b. 컬럼의 타입/크기/DEFAULT값 변경가능
   숫자/문자 컬럼의 전체길이의 증가/축소, 타입변경도 가능
c. DEFAULT 값 변경의 경우, 이후 입력되는 행에 대해서만 적용
------------------------------------------------------
ALTER TABLE 테이블명
MODIFY ( 컬럼명1 데이터타입 [, ..., 컬럼명n 데이터타입] );
-------------------------------------------------------

ALTER TABLE emp04
MODIFY ( email VARCHAR2(40) );

ALTER TABLE emp04
MODIFY ( ename VARCHAR2(20) );


------------------------------------------------------
(3) 컬럼 삭제 (ALTER TABLE DROP 문장)

a. 기존 테이블에 기존 컬럼 삭제
b. 컬럼은 값의 존재여부와 상관없이, 무조건 삭제됨
c. 동시에 여러 컬럼삭제가 가능
d. 최소한 1개의 컬럼은 반드시 존재해야 됨
------------------------------------------------------
ALTER TABLE 테이블명
DROP ( 컬럼명1, [컬럼명n] );
------------------------------------------------------

ALTER TABLE emp04
DROP ( email );


------------------------------------------------------
제약조건 추가 (ALTER TABLE 문장)

가. 기존 테이블에 제약조건 추가
나. PK/FK/UK/CK 제약조건 추가 -> ALTER TABLE ADD 문 사용
다. NN 제약조건 추가 -> ALTER TABLE MODIFY 문 사용
라. 기존 테이블에 추가적인 제약조건도 추가 가능
------------------------------------------------------
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건타입(컬럼명);
------------------------------------------------------

(1) PRIMARY KEY 제약조건 추가
------------------------------------------------------

-- 제약조건 없는 테이블 생성
CREATE TABLE dept03 (
    deptno  NUMBER(2),
    dname   VARCHAR2(15),
    loc     VARCHAR2(15)
);

-- 테이블에 제약조건 추가
ALTER TABLE dept03
ADD CONSTRAINT dept03_deptno_pk PRIMARY KEY(deptno);


------------------------------------------------------
(2) NOT NULL 제약조건 추가 (CK/PK/FK 제약조건 추가도 동일)
------------------------------------------------------
ALTER TABLE 테이블명
MODIFY ( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL );
------------------------------------------------------

-- NOT NULL 제약조건 추가
ALTER TABLE dept03
MODIFY ( dname VARCHAR2(15) CONSTRAINT dept03_dname_nn NOT NULL );


------------------------------------------------------
제약조건 삭제 (ALTER TABLE DROP 문)

가. 제약조건명 이용
    USER_CONSTRAINTS, USER_CON_COLUMNS 조회하여, 제약조건명 조회
나. CASCADE 옵션
    모든 종속적인 제약조건을 같이 삭제
다. 기본적으로, 제약조건명을 이용하여, 제약조건 삭제
라. 기본키(PK)와 UNIQUE(UK) 제약조건명 없이, 키워드만 사용하여 삭제가능
     NN/CK/FK 제약조건 삭제 -> CONSTRAINT 제약조건명 지정하여 삭제
------------------------------------------------------
ALTER TABLE 테이블명
DROP PRIMARY KEY | UNIQUE(컬럼) | CONSTRAINT 제약조건명 [CASCADE];
------------------------------------------------------


(1) PK 제약조건 삭제 (2가지 방법)

-- 1st. method (추천)
ALTER TABLE dept03
DROP PRIMARY KEY;

-- 2nd. method (비추)
ALTER TABLE dept03
DROP CONSTRAINT dept03_deptno_pk;



(2) NN 제약조건 삭제

ALTER TABLE dept03
DROP CONSTRAINT dept03_dname_nn;



(3) CASCADE 옵션 적용

-- 부모 테이블 생성
CREATE TABLE dept05 (
    deptno  NUMBER(2)       CONSTRAINT dept05_deptno_pk PRIMARY KEY,
    dname   VARCHAR2(15),
    loc     VARCHAR2(15)
);

INSERT INTO dept05 (deptno, dname, loc)
VALUES (10, '인사', '서울');

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

-- 자식 테이블 생성
CREATE TABLE emp05 (
    empno   NUMBER(4)       CONSTRAINT emp05_empno_pk PRIMARY KEY,
    ename   VARCHAR2(15),
    deptno  NUMBER(2)       CONSTRAINT emp05_deptno_fk REFERENCES dept05(deptno)
);

INSERT INTO emp05 (empno, ename, deptno)
VALUES (1000, 'John', 10);

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

-- 참조키에 의한 기본 키 삭제 불가
-- 자식테이블에서 부모테이블을 참조하고 있는 경우, 부모테이블의 
-- 기본키(PK)를 삭제하면, 에러가 발생

-- ORA-02273: this unique/primary key is referenced by some foreign keys
ALTER TABLE dept05
DROP PRIMARY KEY;


-- 부모테이블의 PK/UK 제약조건 삭제시, 자식테이블의 FK 제약조건을 연쇄삭제위해 
-- CASCADE 옵션 사용
ALTER TABLE dept05
DROP PRIMARY KEY CASCADE;


------------------------------------------------------
제약조건 활성화/비활성화

가. 기존 테이블의 제약조건을 필요에 의해 Enable/Disable 가능
나. 제약조건은 데이터의 무결성은 보장받을 수 있으나, 성능은 떨어뜨림
다. 예: 데이터의 무결성이 보장되는 방대한 데이터를, 테이블에 저장시 사용 
------------------------------------------------------
ALTER TABLE 테이블명
DISABLE | ENABLE CONSTRAINT 제약조건명 [CASCADE];

* ENABLE: 제약조건 활성화
* DISABLE: 제약조건 비활성화
* CASCADE: 해당 제약조건과 관련된 모든 제약조건을 연쇄적으로 비활성화
------------------------------------------------------

ALTER TABLE emp05
DISABLE CONSTRAINT emp05_empno_pk;