12.기본키,외래키

/*
    <제약조건>
        - 데이터의 무결성을 지키기 위해 입력값을 제한
        
        NOT NULL : NULL 입력 제한
        UNIQUE : 중복되지 않는 값
        DEFAULT : DEFAULT 값을 부여
        CHECK : 체크로직에 만족하는 값
        
        PRIMARY KEY : 기본키(NOT NULL + UNIQUE)
        FOREIGN KEY : 외래키(두 테이블의 연관관계에 따라 데이터 입력 또는 삭제시 제한을 받는다)
          
    <PRIMARY KEY(기본키) 제약조건>
        - 테이블에서 한 행(튜플)의 정보를 식별하기 위해 사용할 컬럼에 부여하는 제약조건
        - 각 행들을 구분할 수 있는 식별자 역할을 함(사번, 부서코드, 직급코드 등)
        - 기본 키 제약조건을 설정하게 되면 자동으로 해당 컬럼에 NOT NULL + UNIQUE 제약조건이 설정됨
        - 한 테이블에 한개만 설정 가능
          (단, 한 개 이상의 컬럼을 묶어서 PRIMARY KEY로 제약조건을 설정할 수 있음)
        - 컬럼 레벨, 테이블 레벨 방식 모두 설정 가능함
*/
DROP TABLE BOOK;
/*
-- 컬럼 레벨의 지정 방식
CREATE TABLE BOOK(
    BOOK_NO CHAR(5) CONSTRAINT BOOK_NO_PK PRIMARY KEY, -- 컬럼 레벨 방식 제약조건에 이름 부여 CONSTRAINT BOOK_NO_PK
    TITLE VARCHAR2(100) NOT NULL,
    AUTHOR VARCHAR(100) NOT NULL,
    RENTYN CHAR(1) DEFAULT 'N' 
                   CHECK(RENTYN IN ('Y', 'N')), -- 도메인 : 속성의 값, 타입, 제약사항등에 대한 값의 범위
    REG_DATE DATE DEFAULT SYSDATE,
    UPDATE_DATE DATE
); */

-- 테이블 레벨의 지정 방식
CREATE TABLE BOOK(
    BOOK_NO CHAR(5), -- 컬럼 레벨 방식
    TITLE VARCHAR2(100) NOT NULL,
    AUTHOR VARCHAR(100) NOT NULL,
    RENTYN CHAR(1) DEFAULT 'N' 
                   CHECK(RENTYN IN ('Y', 'N')), -- 도메인 : 속성의 값, 타입, 제약사항등에 대한 값의 범위
    REG_DATE DATE DEFAULT SYSDATE,
    UPDATE_DATE DATE,
    CONSTRAINT BOOK_NO_PK PRIMARY KEY(BOOK_NO)
);

INSERT INTO BOOK VALUES('B_001','책1','지은이1','Y',SYSDATE,'');
INSERT INTO BOOK VALUES('B_002','책2','지은이2','Y',SYSDATE,'');
INSERT INTO BOOK VALUES('B_003','책3','지은이3','Y',SYSDATE,'');
-- ERR : 중복되는 값 입력 불가
-- INSERT INTO BOOK VALUES('B_003','책4','지은이4','Y',SYSDATE,'');
-- ERR : NULL 입력 불가
-- INSERT INTO BOOK VALUES(NULL,'책5','지은이5','Y',SYSDATE,'');
COMMIT;

SELECT * FROM BOOK;

--------------------------------------------------------------------
-- 회원 등급에 대한 데이터를 보관하는 테이블(부모테이블)
--------------------------------------------------------------------
/*CREATE TABLE MEMBER_GRADE(
    GRADE_CODE CHAR(1) PRIMARY KEY, -- 컬럼레벨
    GRADE_NAME VARCHAR2(30) NOT NULL    
);*/
DROP TABLE MEMBER_GRADE;

CREATE TABLE MEMBER_GRADE(
    GRADE_CODE CHAR(1),
    GRADE_NAME VARCHAR2(30) NOT NULL,
    CONSTRAINT MEMBER_GRADE_CODE_PK PRIMARY KEY(GRADE_CODE)
);

INSERT INTO MEMBER_GRADE VALUES('C', '일반회원');
INSERT INTO MEMBER_GRADE VALUES('B', '우수회원');
INSERT INTO MEMBER_GRADE VALUES('A', '특별회원');

SELECT * FROM MEMBER_GRADE;
COMMIT;

/*
    <FOREIGN KEY(외래키/참조키) 제약조건>
    - 다른 테이블에 존재하는 값만을 가져야 하는 컬럼에 부여하는 제약조건(단, NULL값을 가질 수 있음)
    - 즉, 참조된 다른 테이블이 제공하는 값만 기록할 수 있다.(FOREIGN KEY 제약조건에 의해서 테이블 간에 관계가 생김)
   
    - 입력제한 
        - 자신 테이블이 부모테이블을 참조하여 부모테이블에 입력된 값만 입력 가능
        
    -삭제 제한 : 옵선에 따라 3가지로 나눠진다.
    부모 테이블에 데이트럴 삭제시 자식테이블에서 사용중이면 
        1. ON DELETE RESTRICT : 삭제불가(기본)
        2. ON DELETE SET NULL : 자식 테이블의 컬럼을 null로 업데이트
        3. ON DELETE CASCADE : 자식테이블의 행을 삭제
        
    1) 컬럼 레벨
        컬럼명 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조할 테이블명 (컬럼명) [삭제룰]
    2) 테이블 레벨
        [CONSTRAINT 제약조건명] FORREIGN KEY(컬럼명) REFERENCES 참조할 테이블명 (컬럼명) [삭제룰]
*/

----------------------------------------------------------------------------
-- 회원에 대한 데이터를 보관하는 테이블(자식테이블/부모테이블)
----------------------------------------------------------------------------
CREATE TABLE MEMBER (
    MEMBER_NO CHAR(5) PRIMARY KEY,
    ID VARCHAR2(20) NOT NULL,
    PW VARCHAR2(20) NOT NULL,
    NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN('남','여')),
    AGE NUMBER CHECK(AGE>0),
    REG_DATE DATE DEFAULT SYSDATE,
    -- 참조하려는 부모테이블이 가지고 있는 컬럼과 일치하는 타입을 지정
    GRADE_CODE CHAR(1) CONSTRAINT MEMBER_GRADE_CODE_FK REFERENCES MEMBER_GRADE -- 기본키는 참조하려는 칼럼(GRADE_CODE)생략이 가능함
);

INSERT INTO MEMBER VALUES('M_001', 'ID1', '1234', 'GD', '남', 30, DEFAULT, 'A');
INSERT INTO MEMBER VALUES('M_002', 'ID2', '1234', '문인수', '남', 33, DEFAULT, 'A');
INSERT INTO MEMBER VALUES('M_003', 'ID3', '1234', '아이브', '여', 20, DEFAULT, 'C');
INSERT INTO MEMBER VALUES('M_004', 'ID4', '1234', '홍길동', '남', 25, DEFAULT, 'B');
-- 무결정 제약조건이 위배
-- 부모테이블에 연결된 컬럼이 가지고 있는 값만 입력 가능(부모 키가 없습니다 오류)
-- INSERT INTO MEMBER VALUES('M_005', 'ID1', '1234', 'GD', '남', 30, DEFAULT, 'E');
SELECT * FROM MEMBER_GRADE;
SELECT * FROM MEMBER;
COMMIT;

-- 외래키 제약조건 삭제 확인
-- 무결성 제약조건(JUNGANG.MEMBER_GRADE_CODE_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
-- 자식테이블에서 사용중인 경우 삭제 불가 -> 옵션 사용
DELETE MEMBER_GRADE WHERE Grade_code='A';
DELETE Member WHERE member_no = 'M_004';
-- 자식테이블에서 사용하지 않는 경우 삭제 가능
DELETE MEMBER_GRADE WHERE Grade_code='B';

---------------------------------------------------------------------------
-- 도서 대여 이력을 관리하는 테이블(자식테이블)
---------------------------------------------------------------------------
CREATE TABLE RENT(
    RENT_NO CHAR(5) CONSTRAINT RENT_NO_PK PRIMARY KEY,
    BOOK_NO CHAR(5) CONSTRAINT RENT_BOOK_NO_FK REFERENCES BOOK ON DELETE SET NULL,
    MEMBER_NO CHAR(5),
    START_DATE DATE DEFAULT SYSDATE,
    END_DATE DATE DEFAULT SYSDATE+14,
    OVERDUE_NUM NUMBER,
    CONSTRAINT RENT_MEMBER_NO_FK FOREIGN KEY(MEMBER_NO) REFERENCES MEMBER ON DELETE CASCADE
);

SELECT * FROM BOOK;
SELECT * FROM MEMBER;

INSERT INTO Rent VALUES('R_001', 'B_001', 'M_001', DEFAULT, DEFAULT, NULL);
INSERT INTO Rent VALUES('R_002', 'B_002', 'M_002', DEFAULT, DEFAULT, NULL);
INSERT INTO Rent VALUES('R_003', 'B_003', 'M_003', DEFAULT, DEFAULT, NULL);

--모두 대여 되었으므로 렌트 여부를 업데이트
UPDATE BOOK SET RENTYN='Y';

/*
대여처리를 구현시 선행 되어야 하는 작업들이 있음
1. 도서가 대여가능한 상태인지 확인
    - 도서의 대출가능 여부
    - 사용자의 대출제한 여부
    - 사용자의 대출가능 도서 수 확인
2. 가능한 상태라면 대여처리를 진행
    - 도서테이블의 대여상태를 업데이트
    - 대여테이블에 데이터를 등록
      두개의 작업은 항상 같이 이루어 져야 하므로 하나의 트렌젝션으로 묶어 두개 중 하나라도 실패할 경우 ROLLBACK 처리 진행
*/

SELECT * FROM Rent;
SELECT * FROM Book; -- SET NULL
SELECT * FROM Member;
DELETE FROM Book WHERE Book_no = 'B_001'; -- ON DELETE SET NULL옵션을 줬기때문에 삭제하면 자식테이블의 Book_no가 null로 업데이트 됨
DELETE FROM Member WHERE Member_no = 'M_001'; -- ON DELETE CASCADE옵션을 줬기때문에 member테이블과 rent테이블 둘 다 삭제됨

------------------------------------------------------------------------------
-- 제약조건은 수정 할 수 없으므로 삭제 후 다시 만들어야 함
------------------------------------------------------------------------------
-- 제약조건 조회
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='RENT';
ALTER TABLE RENT DROP CONSTRAINT RENT_MEMBER_NO_FK;
ALTER TABLE RENT ADD CONSTRAINT RENT_MEMBER_NO_FK FOREIGN KEY(MEMBER_NO) REFERENCES MEMBER;

DELETE FROM Member WHERE Member_no = 'M_002';

-- 1)옵션을 주어 삭제하는 방법
--   DROP TABLE 테이블명 [CASCADE CONSTRAINTS];
DROP TABLE BOOK CASCADE CONSTRAINTS;
-- 2)제약 조건을 삭제 후 테이블을 삭제 할 수 있음
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'RENT';
ALTER TABLE Rent DROP CONSTRAINTS RENT_MEMBER_NO_FK;
DROP TABLE Member;
-- 3)자식 테이블을 삭제 후 부모 테이블 삭제 가능
DROP TABLE Rent;
DROP TABLE Book;