-------------------------------------------------------------------------------------
■■■ FOREIGN KEY(FK : F: R) ■■■ (참조외래외부키)
1. 참조 키 또는 외래 키(FK)는 두 테이블의 데이터 간 연결을 설정하고
강제 적용시키는데 사용되는 열이다. 한 테이블의 기본 기 값이
있는 열을 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다.
이 때, 두 번째 테이블에 추가되는 열이 외래 키가 된다.
2. 부모 테이블(참조받는 컬럼이 포함된 테이블)이 먼저 생성된 후
자식 테이블(참조하는 컬럼이 포함된 테이블) 이 생성되어야 한다.
이 때, 자식 테이블에 FOREIGN KEY 제약조건이 설정된다.
3. 형식 및 구조
① 컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명]
REFERENCES 참조테이블명(참조컬럼명)
[ON DELETE CASCADE | ON DELETE SEL NULL]
② 테이블 레벨의 형식
컬럼명 데이터타입
컬럼명 테이터타입
CONSTRAINT CONSTRAINT명 FOREIGN KEY (컬럼명)
REFERENCES 참조테이블명(참조컬럼명)
[ON DELETE CASCADE | ON DELETE SEL NULL]
FOREIGN KEY 제약조건을 설정하는 실습을 진행하기 위해서는
독립적인 하나의 테이블을 생성하여 처리하는 것이 아니라
부모 테이블 생성 작업을 먼저 수행해야 한다.
그리고 이때, 부모 테이블에는 반드시 PK 또는 UK 제약조건이
설정된 컬럼이 존재해야 한다.
-- 부모 테이블 생성
CREATE TABLE TBL_JOBS
( JIKWI_ID NUMBER
, JIKWI_NAME VARCHAR2(30)
, CONSTRAINT JOBS_ID_PK PRIMARY KEY(JIKWI_ID)
);
--==>>Table TBL_JOBS이(가) 생성되었습니다.
-- 생성된 부모 테이블에 데이터 입력
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES (1, '사원');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES (2, '대리');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES (3, '과장');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES (4, '부장');
SELECT *
FROM TBL_JOBS;
/*
1 사원
2 대리
3 과장
4 부장
*/
COMMIT;
--● FK 지정 실습(ⓐ 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_EMP1
(SID NUMBER PRIMARY KEY
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER REFERENCES TBL_JOBS(JIKWI_ID)
);
--==>>Table TBL_EMP1이(가) 생성되었습니다.
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
/*
HR SYS_C007130 TBL_EMP1 P SID
HR SYS_C007131 TBL_EMP1 R JIKWI_ID NO ACTION
*/
-- 자식 테이블에 데이터 입력
INSERT INTO TBL_EMP1(SID,NAME,JIKWI_ID) VALUES (1,'조은은',1);
INSERT INTO TBL_EMP1(SID,NAME,JIKWI_ID) VALUES (2,'김서서',2);
INSERT INTO TBL_EMP1(SID,NAME,JIKWI_ID) VALUES (3,'이상상',3);
INSERT INTO TBL_EMP1(SID,NAME,JIKWI_ID) VALUES (4,'김현현',4);
INSERT INTO TBL_EMP1(SID,NAME,JIKWI_ID) VALUES (5,'장서서',5);--==>> 에러발생
INSERT INTO TBL_EMP1(SID,NAME,JIKWI_ID) VALUES (5,'장서서',1);
INSERT INTO TBL_EMP1(SID,NAME) VALUES (6,'이유림');
INSERT INTO TBL_EMP1(SID,NAME,JIKWI_ID) VALUES (7,'심혜혜',NULL);
SELECT *
FROM TBL_EMP1;
/*
1 조은은 1
2 김서서 2
3 이상상 3
4 김현현 4
5 장서서 1
6 이유림
7 심혜혜
*/
COMMIT;
-- ● FK 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_EMP2
(SID NUMBER
,NAME VARCHAR2(30)
,JIKWI_ID NUMBER
,CONSTRAINT EMP2_SID_PK PRIMARY KEY(SID)
, CONSTRAINT EMP2_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
REFERENCES TBL_JOBS(JIKWI_ID)
);
--==>Table TBL_EMP2이(가) 생성되었습니다.
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP2';
/*
HR EMP2_SID_PK TBL_EMP2 P SID
HR EMP2_JIKWI_ID_FK TBL_EMP2 R JIKWI_ID NO ACTION
*/
-- ● FK 지정 실습(③ 테이블 생성 이후 제약조건 추가 FK 제약조건 추가)
--테이블 생성
CREATE TABLE TBL_EMP3
(SID NUMBER
,NAME VARCHAR2(30)
,JIKWI_ID NUMBER
);
--==>>Table TBL_EMP3이(가) 생성되었습니다.
-- 제약 조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP3';
-- 제약 조건 추가
ALTER TABLE TBL_EMP3
ADD( CONSTRAINT EMP3_SID_PK PRIMARY KEY(SID)
,CONSTRAINT EMP3_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
REFERENCES TBL_JOBS(JIKWI_ID)
);
--==>>Table TBL_EMP3이(가) 변경되었습니다.
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP3';
/*
HR EMP3_SID_PK TBL_EMP3 P SID
HR EMP3_JIKWI_ID_FK TBL_EMP3 R JIKWI_ID NO ACTION
*/
/*
4. FOREIGNKEY 생성 시 주의사항
참고하고자 하는 부모 테이블을 먼저 생성해야 한다.
참고하고자 하는 컬럼이 PRIMARY KEY 나 UNIQUE 제약조건이 있어야 한다.
테이블 사이에 PRIMARy KEY 와 FOREIGN KEY 가 정의되어 있으면
FOREIGN KEY 제약조건이 설정된 컬럼의 데이터 삭제 시
FOREIGN KEY 컬럼에 그 값이 입력되어 있는 경우 삭제되지 않는다.
(단,FK 설정 과정에서 ON DELETE CASCADE 나 ON DELETE SET NULL 옵션을 사용하여 설정한 경우에는 삭제가 가능하다.
부모 테이블을 제거하기 위해서는 자식 테이블을 먼저 제거해야 한다. */
SELECT *
FROM TBL_JOBS;
/*
1 사원
2 대리
3 과장
4 부장
*/
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
/*
1 조은은 1
2 김서서 2
3 이상상 3
4 김현현 4
5 장서서 1
6 이유림 (null)
7 심혜혜 (null)
*/
-- 이희주 부장의 직위를 사원으로 변경
UPDATE TBL_EMP1
SET JIKWI_ID=1
WHERE SID=4;
SELECT *
FROM TBL_EMP1;
COMMIT;
-- 부모 테이블TBL_JOB의 부장 데이터를 참고하고 있는 자식 테이블TBL_EMP1의 데이터가 존재하지 않는 상황
-- 이와 같은 상황에서 부모 테이블의 부장 데이터를 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=4;
SELECT *
FROM TBL_JOBS;
COMMIT;
SELECT *
FROM TBL_EMP1;
--==>> 아까와 다른 점이 없다
-- 부모 테이블 (TBL_JOBS)의 사원데이터를 참조하고 있는
-- 자식 테이블 (TBL_EMP1)의 데이터가 3건 존재하는 상황
-- 이와 같은 상황에서 부모 테이블의 사원 데이터 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
/*
오류 보고 -
ORA-02292: integrity constraint (HR.SYS_C007131) violated - child record found
*/
-- 그럼 아예 부모 테이블을 없앤다면?
DROP TABLE TBL_JOBS;
/*
"unique/primary keys in table referenced by foreign keys"
*Cause: An attempt was made to drop a table with unique or
primary keys referenced by foreign keys in another table.
*Action: Before performing the above operations the table, drop the
foreign key constraints in other tables. You can see what
constraints are referencing a table by issuing the following
command:
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
*/
-- ※ 참조하고 있는 자식 테이블의 레코드가 존재하는 상황임에도 불구하고
-- 부모 테이블의 데이터를 자유롭게 삭제하기 위해서는 『ON DELETE CASCADE』 옵션 지정이 필요하다.
--TBL_EMP1 테이블(자식 테이블) 에서 FK 제약조건을 제거한 후
--CASCADE 옵션을 포함하여 다시 FK 제약조건을 설정한다.
-- 제약조건 확인해서FK 이름 어떻게 구성되어있는지 파악
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
/*
HR SYS_C007130 TBL_EMP1 P SID
HR SYS_C007131 TBL_EMP1 R JIKWI_ID NO ACTION
+++++++++++
*/
-- 제약 조건 제거
ALTER TABLE TBL_EMP1
DROP CONSTRAINT SYS_C007131;
--==>>Table TBL_EMP1이(가) 변경되었습니다.
-- ● 제약 조건 제거 이후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
/*
HR SYS_C007130 TBL_EMP1 P SID */
-- ● ON DELETE CASCADE 옵션이 포함된 내영으로 제약조건 재지정
ALTER TABLE TBL_EMP1
ADD CONSTRAINT EMP1_JIKWIID_FK FOREIGN KEY(JIKWI_ID)
REFERENCES TBL_JOBS(JIKWI_ID)
ON DELETE CASCADE;
--==>>Table TBL_EMP1이(가) 변경되었습니다
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
/*
HR SYS_C007130 TBL_EMP1 P SID ★★★★
HR EMP1_JIKWIID_FK TBL_EMP1 R JIKWI_ID CASCADE*/
-- ※ CASCADE 옵션을 지정한 후에는 참조받고 있는 부모 테이블의 데이터를 언제든지 자유롭게
-- 삭제하는 것이 가능하다. 단!!!! 부모 테이블의 데이터가 삭제될 경우 이를 참조하는 자식
-- 테이블의 데이터도 모두 함께 삭제된다. ★★★★★중요!
SELECT *
FROM TBL_EMP1;
/*
1 조은은 1
2 김서서 2
3 이상상 3
4 김현현 1
5 장서서 1
6 이유림
7 심혜혜
*/
SELECT *
FROM TBL_JOBS;
-- TBL_JOBS (부모테이블)의 사원 ㅏ데이터 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>>1 행 이(가) 삭제되었습니다 (현재 대리하고 과장 만 남아있는 상태)
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
/*
2 김서서 2
3 이상상 3
6 이유림
7 심혜혜
*/
-- 그런데 CASCADE 가무슨뜻???? --==>> 종속이란 뜻