[6주차] 26. 부모 테이블이 존재하도록 특정 필드를 foreign key(=자식키)로 지정하는 방법***

자식이 부모를 참조하는 방식에 대한 추상화된 설명

1) 자식은 부모에 존재하는 것만 가질 수 있다.

2) 또한, 부모의 값은 중복이 없어야 한다.

  (자식의 입장에서 정확히 부모의 무슨 값을 참조하는 것인지 구분할수 없기 때문에)

1. 부모와 자식의 테이블을 CREATE 할 때 주의할 점 및 관계 설정방법

아래의 예시는 employee 테이블이 dept 테이블을 부모로 삼도록 하는 예제이다.

이 과정은 employee 테이블의 deptno 필드가 dept 테이블의 deptno 필드를 참조하여 이뤄지는데,

참조하도록 하기 위한 설정은 자식 테이블의 필드 선언에서 다음과 같이 해줌으로써 할 수 있다.

"reference 부모테이블명(부모테이블의 primary key 필드명)"

* 참조하려는 부모키가 primary key 혹은 unique 제약조건을 가지고 있어야 한다.

* 자식키와 부모키의 데이터 타입은 같아야 하며, 컬럼 이름은 달라도 된다.

create table dept( -- 부모 테이블
	deptno number primary key, -- 부모키
	dname varchar2(10)
);

create table employee( -- 자식 테이블
	eno number,
	ename varchar2(10),
	deptno number references dept(deptno) -- 자식키(왜래키 foreign key) : 부모키를 참고하는 것
);

-- 만약 부모키의 필드가 unique 혹은 primary key 제약조건이 안붙어 있는 경우 만나게될 오류
ORA-02270: 이 열목록에 대해 일치하는 고유 또는 기본 키가 없습니다.

2. 레코드 삽입시 주의할 점

* 자식테이블에 데이터를 삽입할 때 부모키에 존재하지 않는 값을 넣으려고 하면 에러가 난다는 것을 알고 있어야한다.

insert into dept values(10, '홍보부');
insert into dept values(20, '인사부');
insert into dept values(30, '연구부');
insert into dept values(40, '총무부');

insert into employee values(1, 'kim', 20);
insert into employee values(2, 'choi', 30);
insert into employee values(3, 'hong', 10);
insert into employee values(4, 'park', 20);
insert into employee values(5, 'jung', 50); -- 부모키에 존재하지 않는 값을 넣으려하니 에러발생

-- 발생한 오류
ORA-02291: 무결성 제약조건(JSPID.SYS_C007405)이 위배되었습니다- 부모 키가
없습니다

3. 부모테이블의 레코드 DELETE 시 주의할 점

자식테이블의 레코드는 마음대로 삭제가 가능하지만, 부모테이블에서 레코드를 삭제할 때는 삭제 불가능한 경우가 존재한다.

삭제불가 : 자식테이블에 의해서 부모키의 특정 값이 참조당하고 있는 상태일 때, 이 특정 값을 가진 부모테이블의 행레코드

삭제가능 : 자식테이블에 의해서 부모키의 특정 값이 참조당하고 있는 상태가 아니라면 모든 부모테이블의 행레코드 가능

--<위에서 insert된 레코드 상태에서 삭제가 불가능한 경우>
delete dept where deptno = 20;

ORA-02292: 무결성 제약조건(JSPID.SYS_C007407)이 위배되었습니다- 자식 레코드가
발견되었습니다

--<위에서 insert된 레코드 상태에서 삭제가 가능한 경우>
delete dept where deptno = 40;

4. 자식 테이블 foreign key 선언 뒤에 붙을 수 있는 설정 2가지

1. foreign key 선언 뒤에 "on delete set null"를 사용한 경우

이 설정이 없는 상태에서는 부모테이블의 행레코드에 대해서 "delete dept where deptno = 20;" 쿼리를 수행하려고 했을 때

삭제가 불가하다고 오류가 떴었는데, 이제는 부모 테이블의 행 레코드가 삭제되며, 자동으로 이 값을 참조하는 자식 테이블의 모든 행 레코드의 해당 필드의 값은 null로 변경된다.

create table employee( -- 자식 테이블
	eno number,
	ename varchar2(10),
	deptno number references dept(deptno) on delete set null
);

2. foreign key 선언 뒤에 "on delete set null"를 사용한 경우

이 설정이 없는 상태에서는 부모테이블의 행레코드에 대해서 "delete dept where deptno = 20;" 쿼리를 수행하려고 했을 때

삭제가 불가하다고 오류가 떴었는데, 이제는 부모 테이블의 행 레코드가 삭제되며, 자동으로 이 값을 참조하는 자식 테이블의 모든 행 레코드도 같이 delete 된다.

create table employee( -- 자식 테이블
	eno number,
	ename varchar2(10),
	deptno number references dept(deptno) on delete cascade
);

5. 부모테이블 DROP 시 주의할 점

3번의 경우와는 조금다르게, 자식테이블을 CREATE 할 시 부모키를 참조하도록 하였다면 두 테이블 모두에 레코드가 존재하지 않더라도 부모테이블을 DROP으로 함부로 삭제할 수 없다. 만약 자식 테이블을 그대로 두고 부모 테이블만 삭제하고 싶다면 다음의 명령어를 사용하여 삭제하면 된다. "drop table dept cascade constraints"

create table dept( -- 부모 테이블
	deptno number primary key, -- 부모키
	dname varchar2(10)
);

create table employee( -- 자식 테이블
	eno number,
	ename varchar2(10),
	deptno number references dept(deptno) -- 자식키(왜래키 foreign key) : 부모키를 참고하는 것
);

drop table dept

ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다

-- 부모테이블만 강제로 삭제
drop table dept cascade constraints