[DB]02-05.Oracle DB REFERENCE, 참조 제약 조건(외래키, Unique 제약 조건, 연계 참조 무결성 제약조건, Check 제약 조건)

데이터베이스의 '외래키'(Foreign Key)는

어떤 테이블에서 다른 테이블의 기본키를 참조하는 키를 말한다.

예를들어

학생 테이블이 있고 국어 과목의 학생 테이블이 있다면

학생 테이블에는 (학번, 이름, 이메일 등), 학번이 기본키

국어 테이블에는 (학번, 출석번호, 중간점수, 기말점수 등) 학번이 외래키, 출석번호가 기본키

위와 같이 국어테이블이 학생 테이블의 학번을 참조하여 사용하는 것이다.

여기서 학생 테이블의 경우는 키를 빌려주기 때문에 부모 테이블이라고 하고

국어 테이블은 빌려와 쓰기 때문에 자식 테이블이라고 한다.

-- 1. 참조 제약 조건 (FOREIGN KEY () REFERENCES)
-- FOREIGN KEY(외래키) FK
-- 외부에서 부터 가져온 키
-- 부모 테이블
CREATE TABLE parentTable(
    userId VARCHAR2(30),
    userName VARCHAR2(20),
    userPhone VARCHAR2(13),
    userAddr VARCHAR2(100),
    CONSTRAINT parent_pk PRIMARY KEY(userId)
);

-- 자식 테이블(외래키)
CREATE TABLE childTable(
    orderId NUMBER(10),
    userId VARCHAR2(30),
    productName VARCHAR2(20),
    price NUMBER(10),
    qty NUMBER(5)
--  CONSTRAINT fk_child FOREIGN KEY(userId)
--  REFERENCES parentTable(userId)
);

-- 외래키 추가(비식별 관계 : 부모의 pk를 일반 필드로 사용)
ALTER TABLE childTable ADD CONSTRAINT fk_child FOREIGN KEY(userId) REFERENCES parentTable(userId);

-- 외래키 추가(식별 관계 : 부모의 pk를 자식의 pk로 사용)
-- 이름이 fk_child와 같이 지정하는 형식이 아니므로 임의로 지정됨
CREATE TABLE idenTable(
    userId VARCHAR2(30),
    PRIMARY KEY(userId),
    FOREIGN KEY(userId) REFERENCES parentTable(userId)
);

-- 식별 관계 - 기본키로 사용 하냐 안하냐


-- Unique 제약 조건 (UNIQUE)
-- 중복을 허용 하지 않는다.
-- NULL을 허용 된다.
-- 테이블에 여러개가 존재 할 수 있음.
ALTER TABLE childTable ADD CONSTRAINT pd_name_uq UNIQUE(productName);

-- 해당 테이블의 제약 조건 검색
SELECT constraint_name, table_name FROM USER_CONSTRAINTS;
-- parent 테이블이랑 child 테이블의 제약조건만 검색
SELECT constraint_name, table_name FROM USER_CONSTRAINTS WHERE table_name = 'CHILDTABLE' OR table_name = 'PARENTTABLE';



-- 연계 참조 무결성 제약조건 (ON DELETE CASCADE)
-- 무결성 : 논리적으로 앞, 뒤가 맞는가?
-- 부모 자식간의 논리적 관계를 의미
-- 부모키에서 값이 사라지면, 참조하는 자식의 값도 사라지는 제약조건
-- ex) 게시글의 댓글이 15개 있다. 게시글이 삭제 된다면 그의 자식인 댓글도 함께 삭제된다.

-- 부모 테이블
CREATE TABLE supplier(
    supplier_id NUMBER(10),
    supplier_name NVARCHAR2(20) NOT NULL,
    phone VARCHAR2(13),
    CONSTRAINT pk_supplier_id PRIMARY KEY(supplier_id)
);
-- 자식 테이블
CREATE TABLE products(
    product_id NUMBER(10),
    supplier_id NUMBER(10),
    product_price NUMBER(10),
    CONSTRAINT fk_supplier_id FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE CASCADE
);

-- 자료 넣고 시험해보기
-- 부모테이블 데이터 추가
INSERT INTO supplier VALUES(1, '김철수', '010-1234-1234');
INSERT INTO supplier VALUES(2, '홍길동', '032-4321-1234');
INSERT INTO supplier VALUES(3, '박영수', '011-1212-3434');
SELECT * FROM supplier;

--자식 테이블 데이터 추가
INSERT INTO products VALUES(1111, 1, 6000);
INSERT INTO products VALUES(1112, 2, 7000);
INSERT INTO products VALUES(1113, 3, 8000);
INSERT INTO products VALUES(1114, 1, 7000);
SELECT * FROM products;

-- 부모 테이블의 항목을 지운다.
DELETE FROM supplier WHERE supplier_id = 1;
SELECT * FROM supplier;
SELECT * FROM products;

-- ORA-02449 오류 : 외래키에 의해 참조된 테이블의 유니크/기본키가 존재합니다.
DROP TABLE supplier;

-- 해결법1 - 자식 테이블을 모두 지우고 부모 테이블을 지운다.

-- 해결법2 - 테이블과 함께 CASCADE 제약 조건을 삭제 한다.
DROP TABLE supplier CASCADE CONSTRAINTS;
-- 이 후 자식 테이블은 제약 없이 삭제 할 수 있다.


-- Check 제약 조건
-- 예전에 쓰던 기능으로 DB쪽의 부담을 줄이기 위해 잘 안쓴다.
-- DB쪽에서 계산을 돌리는것은 비효율적
-- products 입력시 product_price가 5,000~10,000 사이의 값만 받게 한다.
ALTER TABLE products ADD CONSTRAINT chk_price CHECK(product_price BETWEEN 5000 AND 10000);

SELECT * FROM products;

INSERT INTO products VALUES(1114, 2, 6000);
-- 오류 발생 ORA-02290: check constraint (WEB_USER.CHK_PRICE) violated
INSERT INTO products VALUES(1115, 3, 15000);

-- CHECK 제약조건 주의사항
-- 기존에 입력된 값은 CHECK 제약 조건에 영향을 받지 않는다.
-- => 즉, 기존 데이터는 CHECK 제약 조건에 걸리지 않고 유지

-- 확인
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'PRODUCTS';

25번째 줄까지 코드를 실행하면 위와같이 테이블 모델이 연결된다.