210902_join+부모,자식테이블(예제)

(예제1)

--cart1에 상품추가
insert into cart1 values(seq_shop.nextval,1,2,sysdate);  --1번 나이키 추가
insert into cart1 values(seq_shop.nextval,5,1,sysdate);  --5번 추가
insert into cart1 values(seq_shop.nextval,2,3,sysdate); 
insert into cart1 values(seq_shop.nextval,4,2,sysdate); 
commit;

--shop에 없는 상품 추가
--오류 보고 -
--ORA-02291: 무결성 제약조건(ADMIN.CART_FK_NUM)이 위배되었습니다- 부모 키가 없습니다
--없는 번호
insert into cart1 values(seq_shop.nextval,7,2,sysdate);

--cart2에 상품추가
insert into cart2 values(seq_shop.nextval,3,2,sysdate);
insert into cart2 values(seq_shop.nextval,4,1,sysdate);

--cart1에 1번,5번 담김..1번 삭제
--오류 보고 -
--ORA-02292: 무결성 제약조건(ADMIN.CART_FK_NUM)이 위배되었습니다- 자식 레코드가 발견되었습니다
--on delete cascade를 안 해줬기에
delete from shop where num=1;

delete from shop where num=3; --3번은 cart에 없으므로 삭제됨


--cart2에 담긴 3,4번 삭제해보기
--on delete cascade 설정해놔서 cart2랑 shop 둘 다 삭제됨
delete from shop where num=3;

rollback;  --3번 삭제 취소

commit;

--join위해서 cart2에 insert 추가
insert into cart2 values(seq_shop.nextval,1,2,sysdate);
insert into cart2 values(seq_shop.nextval,3,1,sysdate);
insert into cart2 values(seq_shop.nextval,2,4,sysdate);
commit;

--테이블 출력
select * from shop;
select * from cart1;
select * from cart2;

--cart의 상품을 shop테이블과 join
--방법1..풀네임으로 쓰기
select cart1.idx, shop.num, shop.sangpum, shop.color, cart1.cnt, cart1.guipday 
from shop, cart1 where shop.num=cart1.num;
--방법2
select c.idx, s.num, s.sangpum, s.color, c.cnt, c.guipday from shop s, cart1 c
where s.num=c.num;
--방법3..join테이블에 중복되지 않는 컬럼명은 테이블명 생략 가능
select idx, c.num, sangpum, color, cnt, guipday from shop s, cart1 c
where s.num=c.num;



~~~~~~~~~~~~~~~~~~~~~~~

 

(예제2)
--foreign key 사용해서 board, answer join 테이블 만들기

create table board(bno number(3) constraint board_pk_bno primary key,
writer varchar2(30), subject varchar2(30), writeday date);

--board에 insert
insert into board values(seq1.nextval, '요다','오늘 뭐하세요?',sysdate);
insert into board values(seq1.nextval, '림짱','점심 뭐 먹을까',sysdate);
insert into board values(seq1.nextval, '루크','운동하러 가야 되는데',sysdate);
insert into board values(seq1.nextval, '볼트','나 완전 빨라',sysdate);
insert into board values(seq1.nextval, '오월이','넷플에서 영화보자',sysdate);
commit;

--answer라는 테이블 생성, bno를 외부키로 설정할 예정
--원글 삭제시 댓글 자동삭제되도록 만들기
create table answer(num number(5) constraint answer_pk_num primary key,
bno number(3) constraint answer_fk_bno references board(bno) on delete cascade,
nickname varchar2(20), content varchar2(50));

select * from board;

--board에 댓글 추가해보기
--원하는 bno에 댓글 넣으려면 bno 번호 쓰기
insert into answer values(seq1.nextval, 15, '곰돌','공부여..');
insert into answer values(seq1.nextval, 16, '여엉','로제떡볶이에 볶읔밥!!');
insert into answer values(seq1.nextval, 19, '현자타임','운동 정말 싫어');
insert into answer values(seq1.nextval, 20, '루이비','고양이계의 우사인볼트');
insert into answer values(seq1.nextval, 23, '순탄이','뭐 볼까?');
commit;

--join으로 출력
--글번호,작성자,작성자글,댓글단사람,댓글내용,원글작성날짜
select b.bno 글번호, writer 작성자, subject 제목, nickname 댓글단사람, content 댓글, 
writeday 원글작성일 from board b, answer a where b.bno=a.bno;

--원글 삭제하면 댓글 삭제되게 해보기
delete from board where bno=15;

--확인
select * from answer;

rollback;

--board 테이블 삭제..불가능
--오류 보고 ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다
drop table board;

--자식테이블 answer 삭제
drop table answer;

--자식 테이블 삭제 후 부모테이블 board 삭제 가능
drop table board;