-- ■■■ FOREIGN KEY ■■■--
--1. 참조 키 또는 외래 키(FK)는
-- 두 테이블의 데이터 간 연결을 설정하고
-- 강제 적용시키는데 사용되는 열이다.
-- 한 테이블의 기본 키 값이 있는 열을
-- 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다.
-- 이 때, 두 번째 테이블에 추가되는 열이 외래 키가 된다.
--2. 부모 테이블(참조받는 컬럼이 포함된 테이블)이 먼저 생성된 후
-- 자식 테이블(참조하는 컬럼이 포함된 테이블)이 생성되어야 한다.
-- 이 때, 자식 테이블에 FOREIGN KEY 제약조건이 설정된다.
--3. 형식 및 구조
-- 1) 컬럼 레벨의 형식
-- 컬럼명 데이터타입[CONSTRAINT CONSTRAINT명]
-- REFERENCES 참조테이블명(참조컬럼명)
-- [ON DELETE CASCADE | ON DELETE SET NULL]
-- 2) 테이블 레벨의 형식
-- 컬럼명 데이터타입,
-- 컬럼명 데이터타입,
-- CONSTRAINT CONSTRAINT명 FOREIGN KEY(컬럼명)
-- REFERENCES 참조테이블명(참조컬럼명)
-- [ON DELETE CASCADE | ON DELETE SET NULL] -- 함부로 쓰면 안됨.
--※ FOREIGN KEY 제약조건을 설정하는 실습을 진행하기 위해서는
-- 독립적인 하나의 테이블을 생성하여 처리하는 것이 아니라
-- 부모 테이블 생성 작업을 먼저 수행해야 한다.
-- 그리고 이 때, 부모 테이블에는 반드시 PK 또는 UK 제약조건이
-- 설정된 컬럼이 존재해야 한다.
-- 부모 테이블 생성
-- 테이블명 : TBL_JOBS
CREATE TABLE TBL_JOBS
( JIKWI_ID NUMBER(5)
, 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 지정 실습(1) 컬럼 레벨의 형식)
CREATE TABLE TBL_EMP1
( SID NUMBER(5) PRIMARY KEY
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER REFERENCES TBL_JOBS(JIKWI_ID)
);
--○ 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
--==>>
/*HR SYS_C007032 TBL_EMP1 P SID
HR SYS_C007033 TBL_EMP1 R JIKWI_ID NO ACTION
*/
SELECT *
FROM TBL_EMP1;
/*
마이콜 1
도우너 2
희동이 3
고길동 4
둘리 1
철수
*/
-- 자식 테이블에 데이터 입력
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, JIKWI_ID) VALUES(6, '또치',NULL);
INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(7, '철수' ,NULL);
COMMIT;
--○ FK 지정 실습(2) 테이블 레벨의 형식)
CREATE TABLE TBL_EMP2
( SID NUMBER(5)
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER(5)
, 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 지정 실습(3) 테이블 생성 이후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_EMP3
( SID NUMBER(5)
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER(5)
);
--==>> 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. FROEIGN KEY 생성 시 주의 사항
-- - 참고하고자 하는 부모 테이블을 먼저 생성해야 한다.
-- - 참조하고자 하는 컬럼이 PRIMARY KEY 나 UNIQUE 제약조건이 있어야 한다.
-- - 테이블 간에 PRIMARY KEY 와 FOREIGN KEY 가 정의되어 있으면
-- PRIMARY 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;
--==>> 1 행 이(가) 업데이트되었습니
-- 확인
SELECT *
FROM TBL_EMP1;
--==>> 4 고길동 1
INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(6, '또치',NULL);
COMMIT;
--==>> 커밋
-- 부모테이블(TBL_JOBS)의 부장 데이터를 참조하고 있는
-- 자식 테이블(TBL_EMP1)의 데이터가 존재하지 않는 상황.
-- 이와 같은 상황에서 부모 테이블(TBL_JOBS)의
-- 부장 데이터 삭제
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=4;
-- 4 부장
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=4;
-- 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_JOBS;
/*
1 사원
2 대리
3 과장
*/
COMMIT;
-- 커밋완료
SELECT *
FROM TBL_EMP1;
/*
1 마이콜 1
2 도우너 2
3 희동이 3
4 고길동 1
5 둘리 1
6 또치 NULL
7 철수 NULL
*/
-- 부모테이블(TBL_JOBS)의 [사원] 데이터를 참조하고 있는
-- 자식 테이블(TBL_EMP1)의 데이터 3건이 존재하는 상황.
-- 이와 같은 상황에서 부모 테이블의
-- [사원] 데이터 삭제
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=1;
-- 사원
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
-->> 에러발생
-- ORA-02292: integrity constraint (HR.SYS_C007033) violated - child record found
-- 부모테이블(TBL_JOBS) 제거
DROP TABLE TBL_JOBS;
--==>> 에러
/*
1 행 이(가) 삭제되었습니다.
커밋 완료.
명령의 895 행에서 시작하는 중 오류 발생 -
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1
오류 보고 -
ORA-02292: integrity constraint (HR.SYS_C007033) violated - child record found
명령의 902 행에서 시작하는 중 오류 발생 -
DROP TABLE TBL_JOBS
오류 보고 -
ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 - "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 제약조건을 제거한 후
-- CASCADED 옵션을 포함한 상태로 다시 FK 제약조건을 설정한다.
-- 제약 조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
/*
HR SYS_C007032 TBL_EMP1 P SID
HR SYS_C007033 TBL_EMP1 R JIKWI_ID NO ACTION
*/
-- R 붙어있는 제약조건만 복사
-- 제약조건 제거
-- 제약조건명 : SYS_C007033
ALTER TABLE TBL_EMP1
DROP CONSTRAINT SYS_C007033;
--==>> Table TBL_EMP1이(가) 변경되었습니다.
-- 제약조건 제거 후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>> HR SYS_C007032 TBL_EMP1 P SID
--[ON DELETE CASCADE] 옵션이 포함된 내용으로 제약조건 다시 지정
ALTER TABLE TBL_EMP1
ADD CONSTRAINT EMP1_JIKWI_ID_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_C007032 TBL_EMP1 P SID
HR EMP1_JIKWI_ID_FK TBL_EMP1 R JIKWI_ID CASCADE -- CHECK~!!!
*/
-- CASCADE 옵션을 지정한 후에는
-- 참조받고 있는 부모 테이블의 데이터를
-- 언제든지 자유롭게 삭제하는 것이 가능하다.
-- 단 ... ... ... 부모 테이블의 데이터가 삭제될 경우
-- 이를 참조하는 자식 테이블의 데이터도 모~~~두 함께 삭제된다.
-- CHECK~!!!
-- 부모테이블
SELECT *
FROM TBL_JOBS;
-- 사원, 대리, 과장
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
/*
1 마이콜 1
2 도우너 2
3 희동이 3
4 고길동 1
5 둘리 1
7 철수 NULL
6 또치 NULL
*/
-- TBL_JOBS(부모 테이블)의 사원 데이터 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
-- 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_JOBS;
-- 대리, 과장
-- 자식 테이블
SELECT *
FROM TBL_EMP
/*
2 도우너 2
3 희동이 3
7 철수
6 또치
*/ -- 사원이 모두 삭제 됨 ..
댓글 영역