reProject_24_프로젝트 중간 점검

reProject_23_주문 결제 기능 구현_아임포트(i'mport)

2024.01.11

프로젝트 중간 점검 : 같이 프로젝트를 다시 작업하기로 했던 팀원들중 4명이 작업에 진행이 없어 각자 작업하기로 하고 결국 나와 한 명의 팀원은 같이 나머지 작업을 완료하기로 했다. 초기 작업했던 DB와 변경된 부분도 있고, 중간에 투입된 팀원이라 DB 환경 파악부터 다시 시작하기로 함. 회의 결과 DB 컬럼명을 한자리 약자로 작업해둔 부분들을 테이블명 풀네임으로 변경하고, 필요없는 컬럼을 삭제, 화면은 부트스트랩을 활용하여 더 간결하고 가독성이 있는 심플함을 유지하기로 결정했다. 프로젝트 마감 목표 기한은 3주로 잡았고, 구정 전에 마무리하는 것을 목표로 열심히 달려보기로 결정

 

2024.01.14

현재 MySQL의 프로젝트 데이터베이스를 team_project로 사용하고 있었으나, 컬럼명을 변경해야 하고 테이블이 변경되는 만큼 다른 팀원들의 개발에 방해가 되지 않도록 team_project 데이터베이스의 데이터를 모두 복사하여 새로운 데이터베이스를 생성하기로 함.

구글링을 통해 데이터베이스 복사를 검색했는데, 데이터베이스를 mysqldump 명령어를 통해 백업 후 새로운 데이터베이스에 백업 데이터를 복원하는 방식으로 작업하는 방법이 있어 시도하였으나 실패했다. 

 

결국 워크벤치에서 데이터베이스를 하나 새로 생성했다.

-- 데이터베이스 생성

CREATE DATABASE w2_project;

 

team_project 데이터베이스에 있는 테이블을 w2_project 데이터베이스에 동일하게 생성하는 쿼리문을 사용하여 데이터를 복사했다.

-- 테이블 복사
CREATE TABLE w2_project.client SELECT * FROM team_project.client;
CREATE TABLE w2_project.admin SELECT * FROM team_project.admin;
CREATE TABLE w2_project.admin_image SELECT * FROM team_project.admin_image;
CREATE TABLE w2_project.bank SELECT * FROM team_project.bank;
CREATE TABLE w2_project.cart SELECT * FROM team_project.cart;
CREATE TABLE w2_project.category SELECT * FROM team_project.category;
CREATE TABLE w2_project.client_address SELECT * FROM team_project.client_address;
CREATE TABLE w2_project.client_image SELECT * FROM team_project.client_image;
CREATE TABLE w2_project.coupon_info SELECT * FROM team_project.coupon_info;
CREATE TABLE w2_project.coupon_list SELECT * FROM team_project.coupon_list;
CREATE TABLE w2_project.deliver_info SELECT * FROM team_project.deliver_info;
CREATE TABLE w2_project.delivery SELECT * FROM team_project.delivery;
CREATE TABLE w2_project.grade SELECT * FROM team_project.grade;
CREATE TABLE w2_project.notice SELECT * FROM team_project.notice;
CREATE TABLE w2_project.option_info SELECT * FROM team_project.option_info;
CREATE TABLE w2_project.orders SELECT * FROM team_project.orders;
CREATE TABLE w2_project.orders_info SELECT * FROM team_project.orders_info;
CREATE TABLE w2_project.payment_info SELECT * FROM team_project.payment_info;
CREATE TABLE w2_project.product  SELECT * FROM team_project.product;
CREATE TABLE w2_project.product_image  SELECT * FROM team_project.product_image;
CREATE TABLE w2_project.product_price  SELECT * FROM team_project.product_price;
CREATE TABLE w2_project.qna  SELECT * FROM team_project.qna;
CREATE TABLE w2_project.refund  SELECT * FROM team_project.refund;
CREATE TABLE w2_project.review  SELECT * FROM team_project.review;
CREATE TABLE w2_project.swap  SELECT * FROM team_project.swap;
CREATE TABLE w2_project.terms  SELECT * FROM team_project.terms;
CREATE TABLE w2_project.tm_agree  SELECT * FROM team_project.tm_agree;
CREATE TABLE w2_project.weather  SELECT * FROM team_project.weather;
CREATE TABLE w2_project.withdraw_info  SELECT * FROM team_project.withdraw_info;

 

 

컬럼명을 변경하기로 했으므로 테이블의 컬럼명을 변경

-- desc client;
ALTER TABLE client RENAME COLUMN cId TO clientId;
ALTER TABLE client RENAME COLUMN cPwd TO clientPwd;
ALTER TABLE client RENAME COLUMN chPwdDate TO changePwdDate;
ALTER TABLE client RENAME COLUMN cName TO clientName;
ALTER TABLE client RENAME COLUMN cNum TO clientNum;
ALTER TABLE client RENAME COLUMN cEmail TO clientEmail;
ALTER TABLE client RENAME COLUMN cEmailCheck TO clientEmailCheck;
ALTER TABLE client RENAME COLUMN cRegDate TO clientRegDate;
ALTER TABLE client RENAME COLUMN cRecId TO clientRecId;
ALTER TABLE client RENAME COLUMN cBirth TO clientBirth;
ALTER TABLE client RENAME COLUMN grId TO gradeId;
ALTER TABLE client RENAME COLUMN cPoint TO clientPoint;
ALTER TABLE client RENAME COLUMN crList TO recentList;
ALTER TABLE client RENAME COLUMN cmarkList TO clientMarkList;
ALTER TABLE client RENAME COLUMN cLogDate TO clientLogDate;
ALTER TABLE client RENAME COLUMN cBuyCnt TO clientBuyCnt;

-- desc notice;
ALTER TABLE notice RENAME COLUMN noId TO noticeId;
ALTER TABLE notice RENAME COLUMN noWriter TO noticeWriter;
ALTER TABLE notice RENAME COLUMN noDate TO noticeDate;
ALTER TABLE notice RENAME COLUMN noTitle TO noticeTitle;
ALTER TABLE notice RENAME COLUMN noContent TO noticeContent;
ALTER TABLE notice RENAME COLUMN noImage TO noticeImage;
ALTER TABLE notice RENAME COLUMN noView TO noticeView;
ALTER TABLE notice DROP COLUMN noFile;

-- desc swap;
ALTER TABLE swap RENAME COLUMN swId TO swapId;
ALTER TABLE swap RENAME COLUMN odId TO orderId;
ALTER TABLE swap RENAME COLUMN opId TO optionId;
ALTER TABLE swap RENAME COLUMN swReason TO swapReason;
ALTER TABLE swap RENAME COLUMN swImage TO swapImage;
ALTER TABLE swap RENAME COLUMN swCost TO swapCost;
ALTER TABLE swap RENAME COLUMN swCostMtd TO swapCostMtd;
ALTER TABLE swap RENAME COLUMN swStatus TO swapStatus;
ALTER TABLE swap RENAME COLUMN swEmail TO swapEmail;
ALTER TABLE swap RENAME COLUMN swRegDate TO swapRegDate;

-- desc terms;
ALTER TABLE terms RENAME COLUMN tmId TO termId;
ALTER TABLE terms RENAME COLUMN tmTitle TO termTitle;
ALTER TABLE terms RENAME COLUMN tmContent TO termContent;
ALTER TABLE terms RENAME COLUMN tmNecessary TO termNecessary;
ALTER TABLE terms RENAME COLUMN tmRegDate TO termRegDate;
ALTER TABLE terms RENAME COLUMN tmEndDate TO termEndDate;
ALTER TABLE terms RENAME COLUMN tmChangeDate TO termChangeDate;
ALTER TABLE terms RENAME COLUMN tmApplyDate TO termApplyDate;

-- desc tm_agree;
ALTER TABLE tm_agree RENAME COLUMN tmAgreeId TO termAgreeId;
ALTER TABLE tm_agree RENAME COLUMN tmId TO termId;
ALTER TABLE tm_agree RENAME COLUMN cId TO clientId;
ALTER TABLE tm_agree RENAME COLUMN tmAgreeStatus TO termAgreeStatus;
ALTER TABLE tm_agree RENAME COLUMN tmAgreeDate TO tmermAgreeDate;
ALTER TABLE tm_agree RENAME COLUMN tmAgreeChDate TO termAgreeChDate;

-- desc product_price;
ALTER TABLE product_price RENAME COLUMN productId TO productId;
ALTER TABLE product_price RENAME COLUMN proPrimeCost TO productPrimeCost;
ALTER TABLE product_price RENAME COLUMN proCost TO productCost;
ALTER TABLE product_price RENAME COLUMN proPrice TO productPrice;
ALTER TABLE product_price RENAME COLUMN proTax TO productTax;
ALTER TABLE product_price RENAME COLUMN proMargin TO productMargin;
ALTER TABLE product_price RENAME COLUMN proAddCost TO productAddCost;

-- desc weather;
ALTER TABLE weather RENAME COLUMN wdate TO weatherDate;

-- desc refund;
ALTER TABLE refund RENAME COLUMN refId TO refundId;
ALTER TABLE refund RENAME COLUMN odId TO orderId;
ALTER TABLE refund RENAME COLUMN refReason TO refundReason;
ALTER TABLE refund RENAME COLUMN refImage TO refundImage;
ALTER TABLE refund RENAME COLUMN refWay TO refundWay;
ALTER TABLE refund RENAME COLUMN refCost TO refundCost;
ALTER TABLE refund RENAME COLUMN refCostMtd TO refundCostMtd;
ALTER TABLE refund RENAME COLUMN refBankNum TO refundBankNum;
ALTER TABLE refund RENAME COLUMN refStatus TO refundStatus;
ALTER TABLE refund RENAME COLUMN refRegDate TO refundRegDate;
ALTER TABLE refund RENAME COLUMN refEmail TO refundEmail;

-- desc qna;
ALTER TABLE qna RENAME COLUMN qId TO qnaId;
ALTER TABLE qna RENAME COLUMN cId TO clientId;
ALTER TABLE qna RENAME COLUMN qTitle TO qnaTitle;
ALTER TABLE qna RENAME COLUMN qContent TO qnaContent;
ALTER TABLE qna RENAME COLUMN qImage TO qnaImage;
ALTER TABLE qna RENAME COLUMN qDate TO qnaDate;
ALTER TABLE qna RENAME COLUMN qSecCheck TO qnaSecCheck;
ALTER TABLE qna RENAME COLUMN qSecPwd TO qnaSecPwd;
ALTER TABLE qna RENAME COLUMN qStatus TO qnaStatus;
ALTER TABLE qna RENAME COLUMN qAnswer TO qnaAnswer;
ALTER TABLE qna RENAME COLUMN qaDate TO qnaAnswerDate;
ALTER TABLE qna RENAME COLUMN qnaType TO qnaType;

-- desc review;
ALTER TABLE review RENAME COLUMN reId TO reviewId;
ALTER TABLE review RENAME COLUMN cId TO clientId;
ALTER TABLE review RENAME COLUMN odId TO orderId;
ALTER TABLE review RENAME COLUMN opId TO optionId;
ALTER TABLE review RENAME COLUMN reContent TO reviewContent;
ALTER TABLE review RENAME COLUMN reImage TO reviewImage;
ALTER TABLE review RENAME COLUMN reStar TO reviewStar;
ALTER TABLE review RENAME COLUMN reDate TO reviewDate;
ALTER TABLE review DROP COLUMN reMinus;
ALTER TABLE review DROP COLUMN rePlus;
ALTER TABLE review RENAME COLUMN reStatus TO reviewStatus;
ALTER TABLE review RENAME COLUMN proId TO productId;

-- desc delivery;
ALTER TABLE delivery RENAME COLUMN dId TO deliveryId;
ALTER TABLE delivery RENAME COLUMN dComName TO deliveryComName;
ALTER TABLE delivery RENAME COLUMN dComNum TO deliveryComNum;
ALTER TABLE delivery RENAME COLUMN dPrice TO deliveryPrice;
ALTER TABLE delivery RENAME COLUMN dName TO deliveryName;
ALTER TABLE delivery RENAME COLUMN dNum TO deliveryNum;

-- desc deliver_info;
ALTER TABLE deliver_info RENAME COLUMN deliId TO deliverId;
ALTER TABLE deliver_info RENAME COLUMN odId TO orderId;
ALTER TABLE deliver_info RENAME COLUMN dId TO deliveryId;
ALTER TABLE deliver_info RENAME COLUMN deliType TO deliveType;
ALTER TABLE deliver_info DROP COLUMN deliCheck;
ALTER TABLE deliver_info RENAME COLUMN deliPrice TO deliverPrice;
ALTER TABLE deliver_info RENAME COLUMN deliNum TO deliverNum;

-- desc orders_info;
ALTER TABLE orders_info RENAME COLUMN odId TO orderId;
ALTER TABLE orders_info RENAME COLUMN opId TO optionId;
ALTER TABLE orders_info RENAME COLUMN odProCnt TO orderProCnt;
ALTER TABLE orders_info RENAME COLUMN odTotal TO orderTotal;

-- desc payment_info;
ALTER TABLE payment_info RENAME COLUMN payId TO paymentId;
ALTER TABLE payment_info RENAME COLUMN odId TO orderId;
ALTER TABLE payment_info RENAME COLUMN payMtd TO paymentMethod;
ALTER TABLE payment_info RENAME COLUMN payStatus TO paymentStatus;
ALTER TABLE payment_info DROP COLUMN cId;
ALTER TABLE payment_info RENAME COLUMN payDate TO paymentDate;

-- desc orders;
ALTER TABLE orders RENAME COLUMN odId TO orderId;
ALTER TABLE orders RENAME COLUMN cId TO clientId;
ALTER TABLE orders RENAME COLUMN addId TO addressId;
ALTER TABLE orders RENAME COLUMN opIdList TO optionIdList;
ALTER TABLE orders RENAME COLUMN deliId TO deliverId;
ALTER TABLE orders RENAME COLUMN odDate TO orderDate;
ALTER TABLE orders RENAME COLUMN odPrice TO orderPrice;
ALTER TABLE orders RENAME COLUMN odStatus TO orderStatus;
ALTER TABLE orders RENAME COLUMN cpId TO couponId;
ALTER TABLE orders RENAME COLUMN ckId TO cookieId;
ALTER TABLE orders RENAME COLUMN ckPwd TO cookiePwd;

-- desc product;
ALTER TABLE product RENAME COLUMN proId TO productId;
ALTER TABLE product RENAME COLUMN proCate TO productCate;
ALTER TABLE product RENAME COLUMN proName TO productName;
ALTER TABLE product RENAME COLUMN proContent TO productContent;
ALTER TABLE product RENAME COLUMN proView TO productView;
ALTER TABLE product RENAME COLUMN proLike TO productLike;
ALTER TABLE product RENAME COLUMN proRegDate TO productRegDate;
ALTER TABLE product RENAME COLUMN proSell TO productSell;
ALTER TABLE product RENAME COLUMN proCnt TO productCnt;

-- desc cart;
ALTER TABLE cart RENAME COLUMN caId TO cartId;
ALTER TABLE cart RENAME COLUMN proId TO productId;
ALTER TABLE cart RENAME COLUMN opId TO optionId;
ALTER TABLE cart RENAME COLUMN cId TO clientId;
ALTER TABLE cart RENAME COLUMN caDate TO cartDate;
ALTER TABLE cart RENAME COLUMN caCnt TO cartCnt;
ALTER TABLE cart RENAME COLUMN ckId TO cookieId;
ALTER TABLE cart RENAME COLUMN ckLimit TO cookieLimit;

-- desc option_info;
ALTER TABLE option_info RENAME COLUMN proId TO productId;
ALTER TABLE option_info RENAME COLUMN opColor TO optionColor;
ALTER TABLE option_info RENAME COLUMN opSize TO optionSIze;
ALTER TABLE option_info RENAME COLUMN stCnt TO stockCnt;
ALTER TABLE option_info RENAME COLUMN opId TO optionId;

-- desc client_address;
ALTER TABLE client_address RENAME COLUMN addId TO addressId;
ALTER TABLE client_address RENAME COLUMN cId TO clientId;
ALTER TABLE client_address ADD COLUMN addressTitle VARCHAR(30) NULL;
ALTER TABLE client_address RENAME COLUMN addName TO addressName;
ALTER TABLE client_address RENAME COLUMN addNum TO addressNum;
ALTER TABLE client_address RENAME COLUMN addPostNum TO addressPostNum;
ALTER TABLE client_address RENAME COLUMN address1 TO address1;
ALTER TABLE client_address RENAME COLUMN address2 TO address2;
ALTER TABLE client_address RENAME COLUMN addMemo TO addressMemo;
ALTER TABLE client_address RENAME COLUMN addBase TO addressBase;

-- desc category;
ALTER TABLE category RENAME COLUMN cateId TO categoryId;
ALTER TABLE category RENAME COLUMN catePrCode TO categoryPrCode;
ALTER TABLE category RENAME COLUMN cateName TO categoryName;

-- desc grade;
ALTER TABLE grade RENAME COLUMN grId TO gradeId;
ALTER TABLE grade RENAME COLUMN grName TO gradeName;
ALTER TABLE grade RENAME COLUMN grPoint TO gradePoint;

-- desc withdraw_info;
ALTER TABLE withdraw_info RENAME COLUMN wcId TO withdrawId;
ALTER TABLE withdraw_info RENAME COLUMN wcDate TO withdrawDate;

-- desc coupon_list;
ALTER TABLE coupon_list RENAME COLUMN cpId TO couponId;
ALTER TABLE coupon_list RENAME COLUMN cId TO clientId;
ALTER TABLE coupon_list RENAME COLUMN cpStatus TO couponStatus;

-- desc coupon_info;
ALTER TABLE coupon_info RENAME COLUMN cpId TO couponId;
ALTER TABLE coupon_info RENAME COLUMN cpName TO couponName;
ALTER TABLE coupon_info RENAME COLUMN cpPrice TO couponPrice;
ALTER TABLE coupon_info RENAME COLUMN cpStDate TO couponStDate;
ALTER TABLE coupon_info RENAME COLUMN cpEndDate TO couponEndDate;
ALTER TABLE coupon_info RENAME COLUMN minPrice TO minPrice;
ALTER TABLE coupon_info RENAME COLUMN grList TO gradeList;

 

 

제약 조건을 같이 넘겨오지 않은 상태이고 제약 조건이 변경되거나 사용하지 않던 것들도 있어 테이블을 확인하며 제약조건 생성

-- PRIMARY KEY
ALTER TABLE orders ADD CONSTRAINT ordersPk_orderId PRIMARY KEY (orderId);
ALTER TABLE payment_info ADD CONSTRAINT payment_info_pk_paymentId PRIMARY KEY (paymentId);
ALTER TABLE delivery ADD CONSTRAINT delivery_pk_deliveryId PRIMARY KEY(deliveryId);
ALTER TABLE deliver_info ADD CONSTRAINT deliver_info_pk_deliId PRIMARY KEY(deliverId);
ALTER TABLE qna ADD CONSTRAINT qna_pk_qnaId PRIMARY KEY(qnaId);
ALTER TABLE review ADD CONSTRAINT review_pk_reId PRIMARY KEY(reviewId);
ALTER TABLE swap ADD CONSTRAINT swap_pk_swapId PRIMARY KEY (swapId);
ALTER TABLE bank ADD CONSTRAINT bank_pk_bankId PRIMARY KEY (bankId);
ALTER TABLE refund ADD CONSTRAINT refund_pk_refundId PRIMARY KEY (refundId);
ALTER TABLE notice ADD CONSTRAINT notice_pk_noticeId PRIMARY KEY (noticeId);
ALTER TABLE terms ADD CONSTRAINT term_pk_bankId PRIMARY KEY (termId);
ALTER TABLE weather ADD CONSTRAINT weather_pk_weatherDate PRIMARY KEY(weatherDate, province);
ALTER TABLE category ADD CONSTRAINT category_pk_categoryId PRIMARY KEY (categoryId);
ALTER TABLE product ADD CONSTRAINT product_pk_productId PRIMARY KEY (productId);
ALTER TABLE grade ADD CONSTRAINT grade_pk_gradeId PRIMARY KEY (gradeId);
ALTER TABLE product_image ADD CONSTRAINT product_image_pk_imageId PRIMARY KEY (ImageId);
ALTER TABLE admin_image ADD CONSTRAINT admin_image_pk_imageId PRIMARY KEY (ImageId);
ALTER TABLE client_image ADD CONSTRAINT client_Image_pk_imageId PRIMARY KEY (ImageId);
ALTER TABLE option_info ADD CONSTRAINT option_info_pk PRIMARY key(productId, optionColor, optionSize);
ALTER TABLE cart ADD CONSTRAINT cart_pk_cartId PRIMARY KEY(cartId);
ALTER TABLE coupon ADD CONSTRAINT cpinfo_pk_couponId PRIMARY KEY (couponId);
ALTER TABLE client_address ADD CONSTRAINT client_address_pk_addressId PRIMARY KEY(addressId);
ALTER TABLE client ADD CONSTRAINT client_pk_clientId PRIMARY KEY(clientId);

-- CHECK
ALTER TABLE orders ADD CONSTRAINT orders_check_orderStatus CHECK(orderStatus IN('상품준비중', '배송준비중', '배송보류', '배송대기', '배송중', '배송완료', '교환중', '환불중', '교환완료', '환불완료'));
ALTER TABLE payment_info ADD CONSTRAINT payment_info_check_paymentStatus CHECK (paymentStatus IN ('결제완료', '결제대기', '결제취소'));
ALTER TABLE payment_info ADD CONSTRAINT payment_info_check_paymentMtd CHECK (paymentMethod IN ('카드', '무통장', '간편결제'));
ALTER TABLE deliver_info ADD CONSTRAINT deliver_info_check_deliverType CHECK (deliverType IN ('신규', '교환수거', '교환재배송', '환불수거'));
ALTER TABLE qna ADD CONSTRAINT qna_check_qnaSecCheck CHECK (qnaSecCheck IN ('Y', 'N'));
ALTER TABLE qna ADD CONSTRAINT qna_check_qnaStatus CHECK (qnaStatus IN ('답변대기', '답변완료'));
ALTER TABLE qna ADD CONSTRAINT qna_check_qnaType CHECK(qnaType IN('회원정보', '상품확인', '주문/결제', '배송', '교환/취소(반품)', '서비스'));
ALTER TABLE review ADD CONSTRAINT review_check_reviewStatus CHECK (reviewStatus IN ('일반', '포토'));
ALTER TABLE swap ADD CONSTRAINT swap_check_swapStatus CHECK (swapStatus IN('교환요청', '교환진행', '교환완료'));
ALTER TABLE swap ADD CONSTRAINT swap_check_swapCostMtd CHECK (swapCostMtd IN('택배에동봉', '무통장입금'));
ALTER TABLE refund ADD CONSTRAINT refund_check_refundWay CHECK (refundWay IN ('직접발송', '택배수거'));
ALTER TABLE refund ADD CONSTRAINT refund_check_refundCostMtd CHECK (refundcostmtd IN('택배에동봉', '무통장입금'));
ALTER TABLE refund ADD CONSTRAINT refubnd_check_refundStatus CHECK (refundStatus IN('환불요청', '환불진행', '환불완료'));
ALTER TABLE terms ADD CONSTRAINT term_check_termNecessary  CHECK (termNecessary IN('Y', 'N'));
ALTER TABLE product ADD CONSTRAINT product_check_productSell CHECK (productSell IN('Y','N'));
ALTER TABLE grade ADD CONSTRAINT grade_check_gradeId CHECK (gradeId in ('S', 'G', 'B'));
ALTER TABLE grade ADD CONSTRAINT grade_check_gradeName CHECK (gradeName IN ('SILVER', 'GOLD', 'BLACK'));
ALTER TABLE grade ADD CONSTRAINT grade_check_gradePoint CHECK (gradePoint IN (0.5, 1.0, 1.5));
ALTER TABLE client ADD CONSTRAINT client_check_clientEmailCheck CHECK(clientEmailCheck IN('Y', 'N'));
ALTER TABLE client_address ADD CONSTRAINT client_address_check_addressBase CHECK(addressBase IN('Y', 'N'));
ALTER TABLE coupon_list ADD CONSTRAINT coupon_list_check_couponStatus  CHECK(couponStatus IN('사용가능', '사용완료', '기간만료'));
ALTER TABLE client_image ADD CONSTRAINT client_image_check_imageStatus CHECK (imageStatus IN('환불','문의','리뷰'));
ALTER TABLE admin_image ADD CONSTRAINT admin_image_check_imageStatus CHECK (imageStatus IN('공지','메인','기타'));
ALTER TABLE product_image ADD CONSTRAINT product_image_check_imageStatus CHECK (imageStatus IN('대표','추가','상세'));

-- AUTO
ALTER TABLE tm_agree CHANGE COLUMN tmAgreeId tmAgreeId INT NOT NULL AUTO_INCREMENT;
ALTER TABLE product_price CHANGE COLUMN productCost productCost INT GENERATED ALWAYS AS ((floor((((productPrimeCost * (1 + productMargin)) * (1 + productTax)) / 1000)) * 1000)) STORED;
ALTER TABLE product_price CHANGE COLUMN productPrice productPrice INT GENERATED ALWAYS AS (productCost) STORED;
ALTER TABLE product_price CHANGE COLUMN productCost productCost INT GENERATED ALWAYS AS (FLOOR(((productPrimeCost * (1 + productMargin)) * (1 + productTax))/1000)*1000) STORED;
ALTER TABLE option_info CHANGE COLUMN optionId optionId VARCHAR(60) GENERATED ALWAYS AS (concat(productId, optionColor, optionSIze)) STORED;

-- FOREIGN KEY
ALTER TABLE orders_info ADD CONSTRAINT orders_infoFk_orderId FOREIGN KEY (orderId) REFERENCES orders(orderId);
ALTER TABLE deliver_info ADD CONSTRAINT deliver_infoFk_orderId FOREIGN KEY(orderId) REFERENCES orders(orderId);
ALTER TABLE deliver_info ADD CONSTRAINT deliver_infoFk_deliveryId FOREIGN KEY(deliveryId) REFERENCES delivery(deliveryId);
ALTER TABLE qna ADD CONSTRAINT qnaFk_clientId FOREIGN KEY(clientId) REFERENCES client(clientId);
ALTER TABLE review ADD CONSTRAINT reviewFk_productId FOREIGN KEY (productId) REFERENCES product(productId);
ALTER TABLE review ADD CONSTRAINT reviewFk_clientId FOREIGN KEY(clientId) REFERENCES client(clientId);
ALTER TABLE review ADD CONSTRAINT reviewFk_orderId FOREIGN KEY(orderId) REFERENCES orders(orderId);
ALTER TABLE review ADD CONSTRAINT reviewFk_productId FOREIGN KEY(productId) REFERENCES product(productId);
ALTER TABLE swap ADD CONSTRAINT swapFk_orderId FOREIGN KEY (orderId) REFERENCES orders(orderId);
ALTER TABLE refund ADD CONSTRAINT refundFk_orderId FOREIGN KEY (orderId) REFERENCES orders(orderId);
ALTER TABLE refund ADD CONSTRAINT refundFk_bankId FOREIGN KEY (bankId) REFERENCES bank(bankId);
ALTER TABLE refund ADD CONSTRAINT refundFk_refundImage FOREIGN KEY (refundImage) REFERENCES client_image(ImageId);
ALTER TABLE withdraw_info ADD CONSTRAINT withdraw_info_pk_wclientId FOREIGN KEY (withdrawId) REFERENCES client(clientId);
ALTER TABLE tm_agree ADD CONSTRAINT tm_agreeFk_clientId FOREIGN KEY (clientId) REFERENCES client (clientId);
ALTER TABLE tm_agree ADD CONSTRAINT tm_agreeFk_termId FOREIGN KEY (termId) REFERENCES terms(termId);
ALTER TABLE category ADD CONSTRAINT categoryFk_catePrCode FOREIGN KEY (categoryPrCode) REFERENCES category (categoryId);
ALTER TABLE product ADD CONSTRAINT productFk_productCate FOREIGN KEY (productCate) REFERENCES category (categoryId);
ALTER TABLE product_price ADD CONSTRAINT product_priceFk_productId FOREIGN KEY(productId) REFERENCES product(productId);
ALTER TABLE client ADD CONSTRAINT clientFk_gradeId FOREIGN KEY(gradeId) REFERENCES grade(gradeId);
ALTER TABLE client_address ADD CONSTRAINT client_addressFk_clientId FOREIGN KEY(clientId) REFERENCES client(clientId);
ALTER TABLE coupon_list ADD CONSTRAINT cplistFk_couponId FOREIGN KEY (couponId) REFERENCES coupon_info(couponId);
ALTER TABLE cart ADD CONSTRAINT cartFk_productId FOREIGN KEY(productId) REFERENCES product(productId);
ALTER TABLE cart ADD CONSTRAINT cartFk_clientId FOREIGN KEY(clientId) REFERENCES client(clientId);

 

reProject_25_[ 보류 ] DROPZONE 이미지 드래그해서 업로드하기