오라클 FK 2개를 PK로 변경

[참고 링크]

[Oracle] 오라클 기본키(PK) 2개 이상 지정하기

 

 

[참고 링크]

Composite Foreign Key - Possible In Oracle?

 

[실행]

 

 

[제약조건 확인]

FK 2개에 PK 1개 

 

[시간 설정]

-- 3.발주테이블

create table ordering

(

-- 주문번호 -> 주문번호 _sequence

order_code varchar2(20) CONSTRAINT ordering_order_code_pk primary key,

-- 총주문액

total_order_price number(20) CONSTRAINT ordering_total_order_price_nn not null,

-- 주문상태

order_status number(1)  CONSTRAINT ordering_order_status_check check(order_status >

0 and order_status < 4),

-- 주문일자

order_date date  CONSTRAINT ordering_order_date_nn not null

);

 

-- 발주테이블 시퀀스

create sequence ordering_seq;

 

-- 세션 설정

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

 

insert into ordering

(order_code, total_order_price, order_status, order_date)

values

('주문번호_'||ordering_seq.nextval, 134500, 1, (to_date('2018-12-25 11:11:12' ,'YYYY-MM-DD  HH24:MI:SS')));

 

insert into ordering

(order_code, total_order_price, order_status, order_date)

values

('주문번호_'||ordering_seq.nextval, 214800, 1, (to_date('2021-06-01 06:23:49' ,'YYYY-MM-DD  HH24:MI:SS')));

 

insert into ordering

(order_code, total_order_price, order_status, order_date)

values

('주문번호_'||ordering_seq.nextval, 163500, 1, (to_date('2021-06-02 10:34:42' ,'YYYY-MM-DD  HH24:MI:SS')));

 

 

 

[FK 2개를 복합키이자, PK키로]

-- 4.발주상품테이블

create table order_products

(

-- 상품번호

product_code varchar2(10) references products(product_code),

-- 주문번호 -> 주문번호 _sequence

order_code varchar2(20) references ordering(order_code),

primary key(product_code, order_code),

 

-- 상품명

product_name varchar2(50) CONSTRAINT ordering_product_name_nn not null,

-- 상품 주문 개수

order_count number(4)  CONSTRAINT ordering_order_count_nn not null,

-- 상품 원가

cost_price number(7) CONSTRAINT ordering_cost_price_nn not null

 

);

 

insert into order_products

(product_code, order_code, product_name, order_count, cost_price)

values

((select 'l2001' from products where product_code = 'l2001'),

(select '주문번호_1' from ordering where order_code = '주문번호_1'), '빼빼로', 18, 880);

 

insert into order_products

(product_code, order_code, product_name, order_count, cost_price)

values

((select 'cj1001' from products where product_code = 'cj1001'),

(select '주문번호_2' from ordering where order_code = '주문번호_2'), '미정당쫄면떡볶이', 16, 2500);

 

insert into order_products

(product_code, order_code, product_name, order_count, cost_price)

values

((select 'k2001' from products where product_code = 'k2001'),

(select '주문번호_3' from ordering where order_code = '주문번호_3'), '콘트라베이스라떼', 20, 1500);