5일차(06.16) 외래키(foreign key)

 

외래키(foreign key)

: 부모테이블의 부모키를 참고해서 값을 넣을 수 있는 자식테이블의 자식키입니다.

  관련없는 잘못된 값을 넣는 실수를 줄이기 위해 테이블 생성 시 조건 지정해주면 좋다.

 

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

테이블이 생성되었습니다.

SQL> insert into dept values(10,'홍보부');

1 개의 행이 만들어졌습니다.

SQL> insert into dept values(20,'인사부');

1 개의 행이 만들어졌습니다.

SQL> insert into dept values(30,'연구부');

1 개의 행이 만들어졌습니다.

SQL> insert into dept values(40,'총무부');

1 개의 행이 만들어졌습니다.




1)deptno number references dept(deptno) : dept(부모) 테이블을 참고하여 데이터 넣는다.

-reference 테이블명(칼럼명) :자식테이블에 부모테이블에 없는 데이터가 들어오지 않도록 하고싶을때

SQL> create table employee( --자식테이블
  2  eno number,
  3  ename varchar2(10),
  4  deptno number references dept(deptno)  --자식키(외래키
  5  );

테이블이 생성되었습니다.

SQL> insert into employee values(1,'kim',20);

SQL> insert into employee values(2,'choi',30);

SQL> insert into employee values(3,'hong',10);

SQL> insert into employee values(4,'park',20);

SQL> insert into employee values(5,'jung',50);
insert into employee values(5,'jung',50)
*
1행에 오류:
ORA-02291: 무결성 제약조건(JSPID.SYS_C007724)이 위배되었습니다- 부모 키가 없습니다
↓
employee에 dept테이블에 없는 데이터가 들어오지 않도록 하고싶을때




drop table dept cascade constraints; : 자식이 참고하는 부모키가 있어도 table을 지우겠음 (자식이 있어도 지움)



2)deptno number references dept(deptno) on delete set null
 : 부모가 삭제되면 자식키는 null로 채워짐, 사용안하면 부모 해당 레코드 삭제불가

3)on delete cascade : 부모가 지워지면, 자식 레코드도 삭제가 된다.





Q. 부모키에 primary key 사용하는 이유
부모키에 primary key(기본키)를 설정해주지 않으면, (자식키가 헷갈려할 수 있음)중복되는 값이 생기는것을 대비해서 에러 발생 
→ 부모키 반드시 primary key를 설정해줘야한다. (uniqe도 사용가능)
4행에 오류:
ORA-02270: 이 열목록에 대해 일치하는 고유 또는 기본 키가 없습니다.


Q.부모의 20, 인사부 부서  레코드 삭제

delete from dept where deptno=20; 
--삭제 안됨, 부모키와 중복되는 자식키가 있기때문에 아래 테이블참조!
1행에 오류:
ORA-02292: 무결성 제약조건(JSPID.SYS_C007376)이 위배되었습니다- 자식 레코드가
발견되었습니다

delete from dept where deptno=40; 
--삭제 됨, 자식테이블에 자식키가 없으므로 삭제가 가능하다.

SQL> select * from employee;

ENO ENAME      DEPTNO
--- ---------- ------
  1 kim            20
  2 choi           30
  3 hong           10
  4 park           20
  1 kim            20
  2 choi           30
  3 hong           10
  4 park           20


Q. 자식이 참고하고 있는 부모 테이블을 삭제하고 싶을때
SQL> drop table dept; →삭제 안됨
drop table dept
           *
1행에 오류:
ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다

SQL> drop table dept cascade constraints; →삭제 됨

테이블이 삭제되었습니다.

 

예제2>

[자식테이블]
drop table book2;

create table book2(
 no number primary key,
 title varchar2(30) not null,
 author varchar2(20) not null,
 publisher varchar2(20),
 day date default sysdate,
 sell_no number references seller(sell_no) on delete set null
);

1) references seller(sell_no) : seller테이블(부모) 참고하여 book2(자식)테이블에 데이터를 넣는다.
 1-1) 자식과 부모의 이름이(칼럼) 꼭 동일해야 하는 것은 아니다(타입은 같아야함!)
 1-2) 부모와 자식과 일치하는 데이터가 들어오면, 에러가 발상한다. 아래 1-3) 참고
2) on delete set null : 부모가 삭제되면 자식키는 null로 채워짐, 이것을 사용안하면 레코드 삭제 불가함.
 2-1) delete from seller where sell_no =300; -부모테이블의 sell_no칼럼 삭제
 2-2) select * from seller; - sell_no 300 데이터가 지워졌는지 확인
 2-3) select * from book2; - 부모테이블에서 지웠으니, 참고하는 자식키도 삭제 되었는지 확인을 한다.

insert into book2
values(1,'백설공주','수지','문학동네','90/01/02',100);

insert into book2
values(2,'어린왕자','태연','창작과 비평','93/11/02',300);

insert into book2
values(3,'신데렐라','하니','문학동네','82/01/24',200);

insert into book2
values(4,'콩쥐팥쥐','보라','문학과 지성','99/05/02',200);

insert into book2
values(5,'선녀와 나무꾼','아이유','창작과 비평','16/03/04',100);

insert into book2
values(6,'흥부와 놀부','민아','문학동네','16/03/04',300);

 1-3)
insert into book2
values(7,'피터팬','웬디','문학동네','16/03/04',500);
	
	↓ insert하면 에러 발생, 부모테이블 seller에 sell_no 500 데이터가 없음.
insert into book2
*
1행에 오류:
ORA-02291: 무결성 제약조건(JSPID.SYS_C007389)이 위배되었습니다- 부모 키가
없습니다


commit;

[부모테이블]
drop table seller ;
create table seller(
sell_no number primary key, --" 부모키는 반드시 primary key "로 설정
place varchar2(20) not null
);

insert into seller values(100,'교보');
insert into seller values(200,'영풍');
insert into seller values(300,'yes24');
insert into seller values(400,'인터파크');

commit;
select * from book2;
select * from seller ;

Q. 자식테이블이 부모테이블을 참고하고 있을때 삭제하는 방법
drop table seller ; -> 삭제 안됨
drop table seller cascade constraints; -> 삭제 됨