[세미프로젝트] 우리GYM SQL문 - Create Table

홈트레이닝 용품 쇼핑몰 "우리GYM"

2PM의 노래 우리집에서 착안한 이름이다.

몇번의 수정에 걸쳐 ERD 다이어그램, 테이블 기술서를 작성한 후 14개의 테이블을 생성하기로 하였다.

ORACLE에 우리 조만 쓸 로컬계정도 만들고, 학원에서 열어준 서버로 계정을 만들었다

 

GYM_DB 0.0.3_CREATE.SQL

CREATE TABLE "USER" (
	"USER_ID"	VARCHAR2(30)		NOT NULL,
	"USER_PWD"	VARCHAR2(30)		NOT NULL,
	"USER_NAME"	VARCHAR2(30)		NOT NULL,
	"EMAIL"	VARCHAR2(50)		NOT NULL,
	"EMAIL_YN"	NUMBER	DEFAULT 0	NOT NULL,
	"PHONE"	VARCHAR2(30)		NOT NULL,
	"JOIN_DATE"	DATE		NOT NULL,
	"MILEAGE"	NUMBER	DEFAULT 0	NULL,
	"BIRTHDAY"	DATE		NOT NULL,
	"IDENTITY_NUMBER"	VARCHAR2(30)		NOT NULL,
	"GENDER"	NUMBER		NOT NULL
);

CREATE TABLE "ADDRESS" (
	"ADDRESS_NO"	NUMBER		NOT NULL,
	"USER_ID"	VARCHAR2(30)		NOT NULL,
	"POSTCODE"	VARCHAR2(20)		NOT NULL,
	"BASIC_ADDRESS"	VARCHAR2(100)		NOT NULL,
	"DETAIL_ADDRESS"	VARCHAR2(100)		NOT NULL,
	"FIXED_ADDRESS"	NUMBER	DEFAULT 0	NOT NULL
);

COMMENT ON COLUMN "ADDRESS"."POSTCODE" IS '우편번호5자리';

COMMENT ON COLUMN "ADDRESS"."BASIC_ADDRESS" IS 'API에서 우편번호와 함께 선택되는 기본 주소';

COMMENT ON COLUMN "ADDRESS"."DETAIL_ADDRESS" IS '사용자 직접입력 상세 주소';

COMMENT ON COLUMN "ADDRESS"."FIXED_ADDRESS" IS '주문결제 창에서 자동으로 입력되어 있을 주소';

CREATE TABLE "QNA" (
	"Q_NO"	NUMBER		NOT NULL,
	"USER_ID"	VARCHAR2(30)		NOT NULL,
	"Q_CATEGORY"	VARCHAR2(20)		NULL,
	"Q_TITLE"	VARCHAR2(300)		NOT NULL,
	"Q_CONTENT"	VARCHAR2(4000)		NOT NULL,
	"Q_ASK_DATE"	DATE		NOT NULL,
	"Q_ANSWER"	VARCHAR2(4000)		NULL,
	"Q_ANSWER_DATE"	DATE		NULL
);

COMMENT ON COLUMN "QNA"."Q_NO" IS '시퀀스';

CREATE TABLE "PRODUCT" (
	"PRODUCT_NO"	VARCHAR2(30)		NOT NULL,
	"PRODUCT_NAME"	VARCHAR2(300)		NOT NULL,
	"PARENT_CATEGORY"	VARCHAR2(50)		NOT NULL,
	"CHILD_CATEGORY"	VARCHAR2(50)		NULL,
	"QUANTITY"	NUMBER		NULL,
	"PRICE"	NUMBER		NULL,
	"PRODUCT_INFO_URL"	VARCHAR2(500)		NULL,
	"PRODUCT_OPTION"	VARCHAR2(100)		NULL
);

CREATE TABLE "CART" (
	"CART_NO"	NUMBER		NOT NULL,
	"USER_ID"	VARCHAR2(30)		NOT NULL,
	"PRODUCT_NO"	VARCHAR2(30)		NOT NULL,
	"CART_QUANTITY"	NUMBER		NOT NULL,
	"CHECKED"	NUMBER		NOT NULL
);

CREATE TABLE "REVIEW" (
	"R_NO"	NUMBER		NOT NULL,
	"ORDER_DETAIL_NO"	VARCHAR2(50)		NOT NULL,
	"R_CONTENT"	VARCHAR2(500)		NULL,
	"R_WRITEDATE"	DATE		NULL,
	"SCORE"	NUMBER		NULL
);

COMMENT ON COLUMN "REVIEW"."R_NO" IS '시퀀스';

COMMENT ON COLUMN "REVIEW"."SCORE" IS '숫자로 표현 후 별점으로 나타내기(0~5)';

CREATE TABLE "PRODUCT_OUT" (
	"OUT_NO"	NUMBER		NOT NULL,
	"ORDER_DETAIL_NO"	VARCHAR2(50)		NOT NULL,
	"OUT_QUANTITY"	NUMBER		NULL,
	"OUT_DATE"	DATE		NULL
);

COMMENT ON COLUMN "PRODUCT_OUT"."OUT_NO" IS '시퀀스';

CREATE TABLE "ORDER" (
	"ORDER_NO"	VARCHAR2(50)		NOT NULL,
	"USER_ID"	VARCHAR2(30)		NOT NULL,
	"ADDRESS_NO"	NUMBER		NOT NULL,
	"ORDER_MEMO"	VARCHAR2(100)		NULL,
	"ORDER_TOTAL"	NUMBER		NOT NULL,
	"ORDER_COST"	NUMBER		NULL,
	"POINT_DISCOUNT"	NUMBER		NULL,
	"COUPON_DISCOUNT"	NUMBER		NULL,
	"ORDER_PAYMENT"	NUMBER		NOT NULL,
	"ORDER_METHOD"	NUMBER		NOT NULL,
	"ORDER_DATE"	DATE		NOT NULL,
	"PAY_STATE"	VARCHAR2(30)		NOT NULL,
	"ORDER_STATE"	VARCHAR2(30)		NOT NULL,
	"ARRIVE_DATE"	DATE		NULL,
	"ADD_MILEAGE"	NUMBER		NULL
);

COMMENT ON COLUMN "ORDER"."ORDER_NO" IS '주문일+시퀀스';

COMMENT ON COLUMN "ORDER"."ORDER_COST" IS '기본 배송비 2500원, 상품금액 합계 10만원 초과시 0원';

COMMENT ON COLUMN "ORDER"."ADD_MILEAGE" IS '결제금액의 5%';

CREATE TABLE "COUPON" (
	"COUPON_NO"	VARCHAR2(50)		NOT NULL,
	"USER_ID"	VARCHAR2(30)		NOT NULL,
	"C_NAME"	VARCHAR2(30)		NOT NULL,
	"C_DISCOUNT"	NUMBER		NOT NULL,
	"C_ISSUE_DATE"	DATE		NOT NULL,
	"C_EXPIRE_DATE"	DATE		NOT NULL,
	"C_USE"	NUMBER	DEFAULT 0	NULL
);

COMMENT ON COLUMN "COUPON"."COUPON_NO" IS '발급날짜 + 시퀀스';

CREATE TABLE "NOTICE" (
	"NOTICE_NO"	NUMBER		NOT NULL,
	"N_TITLE"	VARCHAR2(300)		NOT NULL,
	"N_CONTENT"	VARCHAR2(4000)		NOT NULL,
	"N_DATE"	DATE		NOT NULL
);

COMMENT ON COLUMN "NOTICE"."NOTICE_NO" IS '시퀀스';

CREATE TABLE "PRODUCT_IN" (
	"IN_NO"	NUMBER		NOT NULL,
	"PRODUCT_NO"	VARCHAR2(30)		NOT NULL,
	"IN_QUANTITY"	NUMBER		NULL,
	"IN_DATE"	DATE		NULL
);

COMMENT ON COLUMN "PRODUCT_IN"."IN_NO" IS '시퀀스';

CREATE TABLE "CLAIM" (
	"ORDER_DETAIL_NO"	VARCHAR2(50)		NOT NULL,
	"CLAIM_DATE"	DATE		NOT NULL,
	"CLAIM_KIND"	VARCHAR2(30)		NOT NULL,
	"CLAIM_PROCESS"	VARCHAR2(30)		NULL,
	"DONE_DATE"	DATE		NULL
);

COMMENT ON COLUMN "CLAIM"."ORDER_DETAIL_NO" IS '주문번호+시퀀스';

CREATE TABLE "ORDER_DETAIL" (
	"ORDER_DETAIL_NO"	VARCHAR2(50)		NOT NULL,
	"ORDER_NO"	VARCHAR2(50)		NOT NULL,
	"PRODUCT_NO"	VARCHAR2(30)		NOT NULL,
	"BUY_QUANTITY"	NUMBER		NULL
);

COMMENT ON COLUMN "ORDER_DETAIL"."ORDER_DETAIL_NO" IS '주문번호+시퀀스';

CREATE TABLE "REVIEW_IMG" (
	"R_NO"	NUMBER		NOT NULL,
	"R_IMG"	VARCHAR2(100)		NULL
);

ALTER TABLE "USER" ADD CONSTRAINT "PK_USER" PRIMARY KEY (
	"USER_ID"
);

ALTER TABLE "ADDRESS" ADD CONSTRAINT "PK_ADDRESS" PRIMARY KEY (
	"ADDRESS_NO"
);

ALTER TABLE "QNA" ADD CONSTRAINT "PK_QNA" PRIMARY KEY (
	"Q_NO"
);

ALTER TABLE "PRODUCT" ADD CONSTRAINT "PK_PRODUCT" PRIMARY KEY (
	"PRODUCT_NO"
);

ALTER TABLE "CART" ADD CONSTRAINT "PK_CART" PRIMARY KEY (
	"CART_NO"
);

ALTER TABLE "REVIEW" ADD CONSTRAINT "PK_REVIEW" PRIMARY KEY (
	"R_NO"
);

ALTER TABLE "PRODUCT_OUT" ADD CONSTRAINT "PK_PRODUCT_OUT" PRIMARY KEY (
	"OUT_NO"
);

ALTER TABLE "ORDER" ADD CONSTRAINT "PK_ORDER" PRIMARY KEY (
	"ORDER_NO"
);

ALTER TABLE "COUPON" ADD CONSTRAINT "PK_COUPON" PRIMARY KEY (
	"COUPON_NO"
);

ALTER TABLE "NOTICE" ADD CONSTRAINT "PK_NOTICE" PRIMARY KEY (
	"NOTICE_NO"
);

ALTER TABLE "PRODUCT_IN" ADD CONSTRAINT "PK_PRODUCT_IN" PRIMARY KEY (
	"IN_NO"
);

ALTER TABLE "CLAIM" ADD CONSTRAINT "PK_CLAIM" PRIMARY KEY (
	"ORDER_DETAIL_NO"
);

ALTER TABLE "ORDER_DETAIL" ADD CONSTRAINT "PK_ORDER_DETAIL" PRIMARY KEY (
	"ORDER_DETAIL_NO"
);

ALTER TABLE "ADDRESS" ADD CONSTRAINT "FK_USER_TO_ADDRESS_1" FOREIGN KEY (
	"USER_ID"
)
REFERENCES "USER" (
	"USER_ID"
);

ALTER TABLE "QNA" ADD CONSTRAINT "FK_USER_TO_QNA_1" FOREIGN KEY (
	"USER_ID"
)
REFERENCES "USER" (
	"USER_ID"
);

ALTER TABLE "CART" ADD CONSTRAINT "FK_USER_TO_CART_1" FOREIGN KEY (
	"USER_ID"
)
REFERENCES "USER" (
	"USER_ID"
);

ALTER TABLE "CART" ADD CONSTRAINT "FK_PRODUCT_TO_CART_1" FOREIGN KEY (
	"PRODUCT_NO"
)
REFERENCES "PRODUCT" (
	"PRODUCT_NO"
);

ALTER TABLE "REVIEW" ADD CONSTRAINT "FK_ORDER_DETAIL_TO_REVIEW_1" FOREIGN KEY (
	"ORDER_DETAIL_NO"
)
REFERENCES "ORDER_DETAIL" (
	"ORDER_DETAIL_NO"
);

ALTER TABLE "PRODUCT_OUT" ADD CONSTRAINT "FK_DETAIL_TO_PRODUCT_OUT_1" FOREIGN KEY (
	"ORDER_DETAIL_NO"
)
REFERENCES "ORDER_DETAIL" (
	"ORDER_DETAIL_NO"
);

ALTER TABLE "ORDER" ADD CONSTRAINT "FK_USER_TO_ORDER_1" FOREIGN KEY (
	"USER_ID"
)
REFERENCES "USER" (
	"USER_ID"
);

ALTER TABLE "ORDER" ADD CONSTRAINT "FK_ADDRESS_TO_ORDER_1" FOREIGN KEY (
	"ADDRESS_NO"
)
REFERENCES "ADDRESS" (
	"ADDRESS_NO"
);

ALTER TABLE "COUPON" ADD CONSTRAINT "FK_USER_TO_COUPON_1" FOREIGN KEY (
	"USER_ID"
)
REFERENCES "USER" (
	"USER_ID"
);

ALTER TABLE "PRODUCT_IN" ADD CONSTRAINT "FK_PRODUCT_TO_PRODUCT_IN_1" FOREIGN KEY (
	"PRODUCT_NO"
)
REFERENCES "PRODUCT" (
	"PRODUCT_NO"
);

ALTER TABLE "CLAIM" ADD CONSTRAINT "FK_ORDER_DETAIL_TO_CLAIM_1" FOREIGN KEY (
	"ORDER_DETAIL_NO"
)
REFERENCES "ORDER_DETAIL" (
	"ORDER_DETAIL_NO"
);

ALTER TABLE "ORDER_DETAIL" ADD CONSTRAINT "FK_ORDER_TO_ORDER_DETAIL_1" FOREIGN KEY (
	"ORDER_NO"
)
REFERENCES "ORDER" (
	"ORDER_NO"
);

ALTER TABLE "ORDER_DETAIL" ADD CONSTRAINT "FK_PRODUCT_TO_ORDER_DETAIL_1" FOREIGN KEY (
	"PRODUCT_NO"
)
REFERENCES "PRODUCT" (
	"PRODUCT_NO"
);

ALTER TABLE "REVIEW_IMG" ADD CONSTRAINT "FK_REVIEW_TO_REVIEW_IMG_1" FOREIGN KEY (
	"R_NO"
)
REFERENCES "REVIEW" (
	"R_NO"
);


-- 여기까지 테이블 생성, PK, FK 제약조건 설정

-- 여기 부터 시퀀스 생성
-- 명명 규칙 : 테이블명_SEQ

CREATE SEQUENCE QnA_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE COUPON_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE ADDRESS_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE ORDER_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE CART_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE ORDER_DETAIL_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE PRODUCT_IN_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE PRODUCT_OUT_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE REVIEW_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;

CREATE SEQUENCE NOTICE_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE 
NOCACHE
;