[Oracle] PK, FK

*** PK와 FK의 관계를 가진 테이블을 생성하기

1) 생성은 부모테이블 → 자식테이블

2) 삭제와 수정은 자식테이블 → 부모테이블

 

 

** 부모테이블 생성하기

drop table members;



create table members

(

seqno number constraints member_seq_pk primary key,

user_id varchar2(10) constraints member_id_uq unique,

user_name varchar2(20) constraints member_name_nn not null,

birthday date constraints member_birthday_nn not null,

gender char(1) constraints member_gender_ck check(gender in('F', 'M'))

member_use char(1)

);



create sequence member_seq;

 

 

-- 데이터 추가하기

insert into members

values

(

member_seq.nextval,

'SON',

'손오공',

'00/01/23',

'M'

);





insert into members

values

(

member_seq.nextval,

'JEON',

'전우치',

'93/12/05',

'M'

);





insert into members

values

(

member_seq.nextval,

'KIM',

'김순희',

'99/01/03',

'F'

);





commit;

 

 

** 사용자가 입력한 제약조건명 확인

select * from user_constraints;

 

 

-- 부모테이블 확인

select * from members;

 

 

 

** 자식테이블 생성하기

--FK는 부모의 PK로 생성하는 것이 보통이나, unique도 가능하다.

 

drop table purchase;



create table purchase

(

seqid number constraints purchase_seq_pk primary key, -- 구매번호

item varchar2(50) constraints purchase_item_nn not null, -- 품명

price number(10) default 10000, -- 구매가격

pday date default sysdate, -- 구매일

quantity number(3) default 1, -- 수량

seqno number references members(seqno) on delete cascade -- 부모의 PK번호

);



create sequence purchase_seq;

 

 

-- 손오공이 '자바의 정석' 책 1권 구매

insert into purchase

(seqid, item, price, seqno)

values

(purchase_seq.nextval, '자바의 정석', 24000, 1);

 

 

-- 전우치가 '혼공파이썬' 책 2권 구매

insert into purchase

(seqid, item, price, quantity, seqno)

values

(purchase_seq.nextval, '혼공파이썬', 16000*2, 2, 2);

 

 

-- 김순희가 'V3' 1카피 구매

insert into purchase

(seqid, item, price, seqno)

values

(purchase_seq.nextval, 'V3 백신', 20000, 3);

 

 

-- 손오공이 '긱계식 청축 키보드' 1개 구매

insert into purchase

(seqid, item, price, seqno)

values

(purchase_seq.nextval, '기계식 청축 키보드', 320000, 1);

 

select * from purchase;



commit;

 

 

-- [문제] 구매번호, 구매인, 아이템명, 구매가격을 조회

select rownum, m.user_id, m.user_name, p.item, p.price

from members m join purchase p

using (seqno);

 

 

 

-- [문제] 다음과 같은 조건으로 3개의 테이블과 1개의 sequence 객체를 생성하는 쿼리문을 작성하시오.

 

-- 1) 테이블 : lunchbox (판매하는 도시락 정보)

drop table lunchbox;



create table lunchbox

(

lunchbox_id varchar2(10) constraints luncbox_id_pk primary key,

menu_title varchar2(50) constraints lunchbox_menu_nn not null,

price number(7) constraints lunchbox_price_nn not null

);

 

 

-- 2) 테이블 : customer (고객 정보 테이블)

drop table customer;



create table customer

(

cust_id varchar2(10) constraints customer_id_pk primary key,

cust_name varchar2(30) constraints customer_name_nn not null,

address varchar2(100) constraints customer_address_nn not null,

phone varchar2(20) constraints customer_phone_nn not null

);

 

 

-- 3) 테이블 : order (주문 정보 테이블)

drop table orders;



create table orders

(

order_id number constraints orders_id_pk primary key,

cust_id varchar2(10) references customer(cust_id),

lunchbox_id varchar2(10) references lunchbox(lunchbox_id),

order_count varchar2(20) default '1' not null,

order_date date default sysdate

);

 

 

--4) 시퀀스 : order_seq (주문 정보 테이블에서 사용할 일련번호를 위한 sequence 객체)

create sequence order_seq;

 

 

-- 5) lunchbox 테이블에 도시락 종류 5개 이상 필요한 만큼 입력하고 입력된 결과를 조회

insert into lunchbox values ('menu01', '숯불직화구이', 6500);

insert into lunchbox values ('menu02', '치킨제육', 4400);

insert into lunchbox values ('menu03', '돈까스도련님', 7000);

insert into lunchbox values ('menu04', '매화(치킨, 연어구이)', 10000);

insert into lunchbox values ('menu05', '순살 고등어 간장구이', 12000);

 

 

-- 6) 고객 정보 5명 이상 필요한 만큼 입력하고 입력된 결과를 조회

insert into customer values ('hong','홍길동','서울시 강남구 역삼동','010-1111-2222');

insert into customer values ('lim','임꺽정', '부산시 충장로1가','010-2222-1212');

insert into customer values ('son','전우치', '인천시 연수구','010-3333-3434');

insert into customer values ('park','박가가','광주시 무산대로 1234','010-1234-5656');

insert into customer values ('kim','김나나', '서울시 동작구 사당동','010-5678-8989');

 

 

-- 7) 고객이 도시락을 주문한 정보 10개 이상 필요한 만큼 order 테이블에 입력하고 입력된 결과를 조회

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'hong', 'menu01', 2);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'lim', 'menu02', 3);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'son', 'menu05', 1);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'kim', 'menu03', 1);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'hong', 'menu04', 5);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'hong', 'menu01', 1);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'son', 'menu02', 3);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'park', 'menu05', 1);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'kim', 'menu02', 1);

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'son', 'menu05', 1);

 

select * from lunchbox;

select * from customer;

select * from orders;

 

 

-- 8) “치킨” 이라는 단어가 포함된 도시락 종류를 조회하는 쿼리문을 작성하시오

select menu_title

from lunchbox

where menu_title like '%치킨%';

 

 

-- 9) 도시락 가격이 10,000원 이하인 도시락을 조회하는 쿼리문을 작성하시오

select menu_title, price

from lunchbox

where price < 10000;

 

 

-- 10) 고객이 자신이 주문한 도시락 내역을 아래와 같은 정보로 조회하는 쿼리문을 작성하시오.

select c.cust_name as 고객이름, 
       l.menu_title as 도시락이름, 
       l.price as 가격, 
       o.order_count as 주문수량, 
       o.order_count * l.price as 주문금액, 
       o.order_date as 주문날짜

from customer c join orders o

on c.cust_id = o.cust_id

join lunchbox l

on l.lunchbox_id = o.lunchbox_id;