[SQL] FOREIGN KEY

-- INSERT 문제 없음
INSERT INTO TEST_EMP(EMPLOYEE_ID, EMP_NAME, HIRE_DATE, DEPARTMENT_ID)
    VALUES(500,'HONG GIL DONG', '2025/07/15',100);

-- 무결성 제약조건(FK_EMP)이 위배되었습니다- 부모 키가 없습니다(레퍼런스에 이 데이터가 없어서 ERR)
INSERT INTO TEST_EMP(EMPLOYEE_ID, EMP_NAME, HIRE_DATE, DEPARTMENT_ID)
    VALUES(501,'HONG GIL DON2', '2025/07/15',700);​

CONSTRAINT 외래키명 FOREIGN KEY(칼럼명, , ,...)
REFERENCE 참조테이블(참조테이블 컬럼명, , ...)

외래키는 테이블 간의 참조 데이터 무결성을 위한 제약조건
반드시 참조하는 테이블(REF TABLE)이 먼저 생성되고, 참조키가 참조 테이블의 기본키로만 만들어져있어야 함.

CREATE TABLE TEST_EMP AS
SELECT * FROM EMPLOYEES; -- EMPLOYEES 복사

-- TEST_EMP라는 테이블에 PK_EMP라는 CONSTRAINTS를 추가해주겠다.
ALTER TABLE TEST_EMP ADD CONSTRAINTS PK_EMP PRIMARY KEY(EMPLOYEE_ID);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TEST_EMP';

CREATE TABLE TEST_FKEY (
    EMP_ID      NUMBER(6,0),
    EMP_NAME    VARCHAR2(80 BYTE),
    CONSTRAINTS TEST_FOREIGN FOREIGN KEY (EMP_ID) -- 얘를 외래키로 지정할건데
    REFERENCES TEST_EMP(EMPLOYEE_ID) -- 레퍼런스는 얘야
);

1100 같은 부모키에 없는 값을 넣지 못함

외래키에 사용할 수 있는 칼럼 개수는 최대 32개
여러 칼럼을 외래키로 만드려면, 참조하는 칼럼과 외래키 칼럼의 순서와 개수는 같아야함

---

/* foreign key 다른 예시 공부 */
DROP TABLE TEST_FKEY; -- TEST_EMP가 레퍼런스로 잡혀있는 테이블을 먼저 지워야함.
DROP TABLE TEST_EMP;

다른 예시를 공부하기위해 TEST_EMP를 지움

 

# 무결성 예시 1 ) FK  참조 테이블에 없는 값은 넣지 못함

CREATE TABLE TEST_DEPT AS
SELECT * FROM DEPARTMENTS;

CREATE TABLE TEST_EMP AS
SELECT * FROM EMPLOYEES;

ALTER TABLE TEST_EMP
    ADD CONSTRAINTS PK_EMP PRIMARY KEY (EMPLOYEE_ID);
    
ALTER TABLE TEST_DEPT
    ADD CONSTRAINTS PK_DEPT PRIMARY KEY (DEPARTMENT_ID);

연습용 부서, 근로자 테이블 생성 &  PK설정

DESC TEST_EMP;

근로자 테이블의 DEPTMENT_ID는 부서 테이블에 있는 값이여야 함.

-- TEST_DPET에 없는 DEPARTMENT_ID 가 들어오면 ERR를 내도록 FK를 설정
ALTER TABLE TEST_EMP
    ADD CONSTRAINTS FK_EMP FOREIGN KEY (DEPARTMENT_ID)
    REFERENCES TEST_DEPT (DEPARTMENT_ID);

-- INSERT 문제 없음
INSERT INTO TEST_EMP(EMPLOYEE_ID, EMP_NAME, HIRE_DATE, DEPARTMENT_ID)
    VALUES(500,'HONG GIL DONG', '2025/07/15',100);

-- 무결성 제약조건(FK_EMP)이 위배되었습니다- 부모 키가 없습니다
-- 레퍼런스에 이 데이터가 없어서 나는 ERR
INSERT INTO TEST_EMP(EMPLOYEE_ID, EMP_NAME, HIRE_DATE, DEPARTMENT_ID)
    VALUES(501,'HONG GIL DON2', '2025/07/15',700);

 

# 무결성 예시 2)  FK 참조가 된 레퍼런스는 자식 레코드가 그 값을 참조하고있다면 맘대로 지우지 못함

DEPARTMENT_ID가 60인 부서가 있음. TEST_DEPT에서 DEPARTMENT_ID = '60'을 지운다면?