[Oracle/SQL] 부모,자식 table(insert,update,delete)

Day32. 230614

	부모	자식
insert	O	참조할 수 있는 부모키값에 한해O
update	x(child record found)	참조할 수 있는 부모키값에 한해O
delete	x(child record found)	O

 

예)

insert into dept(deptno,dname,loc) values(99,'d99','서울');
insert into emp(empno,ename,deptno) values(8888,'홍88',88); --fk위배. parent key not found
insert into emp(empno,ename,deptno) values(8888,'홍88',99);
 
select * from dept;
select * from emp;
 
update dept set deptno=9 where deptno=99; --fk위배. child record found
update dept set deptno=77 where deptno=66;
update emp set deptno=77 where ename='MILLER'; --원10->77  
update emp set deptno=1 where ename='MILLER'; --fk위배.parent key not found
 
delete from dept where deptno=99; --fk위배. child record found
delete from dept where deptno=65;
delete from emp where deptno=99;

 

 

create table pt (
  id number primary key
);
 
create table ctable(
  id number primary key,
  parent_id number,
  foreign key (parent_id) references pt(id) on delete cascade
);
 
--부모테이블에 data입력
insert into pt values(1); --child record 존재
insert into pt values(2); --child record 존재x
 
--자식테이블에 data입력
insert into ctable values(10,1);
insert into ctable values(11,1);
commit;
 
--부모테이블의 1삭제시   자식테이블의 레코드는?
delete from pt where id=1;
 
 
select * from pt;
select * from ctable;

 

처음 부모테이블pt

delete 후 부모테이블pt

처음 자식테이블 ctable

delete 후 자식테이블 ctable