[SQL] 데이터 품질 높이기 - FOREIGN KEY

실습1. dept 테이블에 deptno에 primary key 제약을 거세요

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

실습2. emp 테이블에 deptno 에 foreign key 제약을 걸고 dept 테이블의 deptno 를 참고해라 라고 하시오

부모-자식(foreign key) 관계 생성

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

실습3. emp 테이블의 KING 의 부서번호를 60번으로 수정하시오

-- 에러발생
--  ORA-02291: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다

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

실습4. dept 테이블의 부서번호 20번을 지우시오

delete from dept       
 where deptno = 20;

-- 에러발생
-- ORA-02292: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다

실습5. dept 테이블의 deptno 에 걸린 primary key 제약을 삭제하시오

-- 에러발생
-- ORA-02273: 고유/기본 키가 외부 키에 의해 참조되었습니다
alter table dept
 drop constraint dept_deptno_pk;
 
 
-- cascade 사용해서 삭제
alter table dept
 drop constraint dept_deptno_pk cascade;

💡 cascade 옵션을 쓰면 부모키를 삭제할 때 자식키도 같이 삭제됨

📌 부모키와 자식키 한번에 삭제

alter table 테이블명
drop constraint 제약조건명 cascade;

---

문제1. telecom_table 에 telecom 에 primary key 제약을 거시오

alter table telecom_table
 add constraint tt_telecom_pk primary key(telecom);

---

문제2. emp21 테이블에 telecom 에 자식키를 걸고 telecom_table 의 telecom 을 참고하게 제약을 걸고 제약이 잘 작동되는지 이름이 000 학생의 telecom 을 toss 로 변경하시오

alter table emp21
 add constraint emp21_telecom_fk foreign key(telecom)
 references telecom_table(telecom);
 

-- 에러발생
-- ORA-02291: 무결성 제약조건(C##SCOTT.EMP21_TELECOM_FK)이 위배되었습니다- 부모 키가 없습니다
update emp21
 set telecom='toss'
 where ename='000';

---

💡 두개의 테이블의 릴레이션 관계에 대한 옵션 2가지

(SQLD, SQLP, OCP)

dept와 emp 테이블에 부모 자식관계를 맺을 때

1. on delete cascade 옵션

dept 테이블의 deptno를 지우면
emp 테이블의 deptno도 같이 지워지는 옵션

2. on delete set null 옵션

dept 테이블의 deptno 를 지우면
emp 테이블의 deptno는 null로 변경이 되는 옵션

---

on delete cascade 실습

• 순서:
  #1. demo 스크립트를 수행합니다.
  #2. dept 테이블의 deptno 에 primary key 제약을 겁니다.
  #3. emp 테이블의 deptno 에 foreign key 제약을 걸면서
  dept 테이블의 deptno 를 참조하겠다. (on delete cascade 옵션을 주고)
  #4. dept 테이블의 부서번호 10번을 지우시오
  #5. emp 테이블의 부서번호 10번도 같이 지워지는지 확인합니다.

구현

#1. demo 스크립트를 수행합니다.
cmd 창에서 @demo

#2. dept 테이블의 deptno 에 primary key 제약을 겁니다.

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

#3. emp 테이블의 deptno 에 foreign key 제약을 걸면서
dept 테이블의 deptno를 참조하겠다. (on delete cascade 옵션을 주고)

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

#4. dept 테이블의 부서번호 10번을 지우시오

delete from dept
 where deptno = 10;

#5. emp 테이블의 부서번호 10번도 같이 지워지는지 확인합니다.

select *
 from emp
 where deptno = 10;

#6. 부모키 제약을 삭제하면서 자식키 제약도 같이 삭제되게하시오

alter table dept
 drop constraint dept_deptno_pk cascade;

---

on delete set null 실습

• 순서:
  #1. demo 스크립트를 수행합니다.
  #2. dept 테이블의 deptno 에 primary key 제약을 겁니다.
  #3. emp 테이블의 deptno 에 foreign key 제약을 걸면서
  dept 테이블의 deptno 를 참조하겠다. (on delete set null 옵션을 주고)
  #4. dept 테이블의 부서번호 20번을 지우시오
  #5. emp 테이블의 부서번호 20번도 같이 지워지는지 확인합니다.

alter table dept
 add constraint dept_deptno_pk primary key(deptno);
 
alter table emp
 add constraint emp_deptno_fk foreign key(deptno)
 references dept(deptno) on delete set null;
 
delete from dept
 where deptno = 20;
 
select * from emp;