SQL 제약 (5) Foreign Key (자식키)

특정컬럼에 지정된 데이터만 허용할때 그 지정된 데이터가 다른 테이블의 데이터를 참조하는 경우 허용하는 제약이다.

• foreign key 제약을 걸려면 참조하려는 테이블에 primary key 제약이 걸려있어야 걸린다.
• 따라서 만약 dept 를 참조해서 emp 를 데이터를 입력하게 된다면
• dept 에 primary key 를 걸어 (부모 테이블) 로 만들고
• emp에 foreign key 를 걸어 (자식 테이블) 로 생성이 가능하다.
• 자식테이블에는 함부로 데이터를 insert 를 할 수 없고
• 부모 테이블에는 함부로 데이터를 delete 를 할 수 없다.

ex ) dept 테이블의 dpetno 에 pk 제약을 걸고 emp 테이블의 deptno fk 제약을 걸게 되면 앞으로 emp 테이블의 deptno 에 데이터를 입력할 때 dept 테이블에 존재하는 부서번호에 대한 데이터만 입력, 수정이 가능하다.

그리고 dept 테이블의 데이터를 지울때 emp 테이블에 관련된 부서번호가 있다면dept 테이블의 해당 부서번호는 지워지지 않는다. (에러나면서 안지워진다)

자식테이블인 emp 테이블에 해당 부서번호가 없어야지만 지워진다.

emp 테이블 deptno 에 foreign key 걸기

먼저 부모테이블을 지정해 주어야한다. (PRIMARY KEY지정 ) 

부모테이블은 dept 테이블 deptno 로 하겠다~

alter table dept
add constraint dept_deptno_pk primary key(deptno);

후에 emp 테이블 deptno 에 fk 를 건다.

alter table emp
add constraint emp_deptno_fk foreign key(deptno) references dept(deptno);

KING 의 부서번호를 70 번으로 변경하시오

update emp
set deptno = 70
where ename = 'KING';

 

• DEPT 테이블에 DEPTNO 가 70 번이 없기 때문에 수정할 수 없게 된다.

 

문제 아래 SQL 이 입력되는지 확인해 보시오

INSERT INTO EMP (EMPNO, ENAME, SAL, DEPTNO)
VALUES(3929, 'JACK', 3000, 90);

→ 부모테이블인 DEPT 의 DEPTNO 에 90 번이 없기때문에 EMP 테이블에 부서번호 90 번을 추가 할 수 없다.

dept 테이블 deptno 의 10 번을 지울 수 있는지해보시오

delete from dept where deptno = 10;

→ emp 테이블 deptno 에 10 번이 있어서 함부로 dept의 deptno= 10을 삭제 할 수 없다.

제약 삭제

alter table emp
drop constraint emp_deptno_fk;

alter table dept
drop constraint dept_deptno_pk;

→ 자식 테이블의 제약을 먼저 삭제 해야 부모테이블의 제약을 삭제 할 수 있다.
부모 테이블의 제약은 먼저 삭제 할 수 없다.

→ 지우려고 하면 이러한 오류 메세지가 뜬다.

---

또는 fk, pk 둘다 한번에 삭제하는 방법

alter table dept
drop constraint dept_deptno_pk cascade ;

• cascade 를 걸면 fk 까지 한번에 날라간다~