파이널 sql

CREATE TABLE MEMBER(
  user_id number PRIMARY KEY,
  id VARCHAR2(30) NOT NULL,
  password VARCHAR2(30) NOT NULL,
  nickname VARCHAR2(30) NOT NULL,
  email VARCHAR2(30) NOT NULL,
  phone_number VARCHAR2(30) NOT NULL,
  address VARCHAR2(50),
  holding_coin NUMBER DEFAULT 0,
  evaluation NUMBER DEFAULT 0
);



CREATE TABLE CATEGORY(
  category_id NUMBER PRIMARY KEY,
  category_name VARCHAR2(20) NOT NULL
);


CREATE TABLE MEMBER_IMG(
  image_id NUMBER PRIMARY KEY,
  user_id NUMBER,
  CONSTRAINT FK_USER_ID FOREIGN KEY(user_id) REFERENCES MEMBER(USER_ID) ON DELETE CASCADE,
  url VARCHAR2(200) NOT NULL
);


CREATE TABLE PRODUCT(
  product_id NUMBER PRIMARY KEY,
  user_id NUMBER,
  category_id NUMBER,
  product_name VARCHAR2(30) NOT NULL,
  title VARCHAR2(50) NOT NULL,
  product_info VARCHAR2(200) NOT NULL,
  min_price NUMBER NOT NULL,
  pay_method VARCHAR2(10) NOT NULL,
  create_date DATE NOT NULL,
  sale_date DATE NOT NULL,
  status CHAR(1) CONSTRAINT status_boolean CHECK(status IN ('0', '1')),
  buyer_id NUMBER NOT NULL,
  sale_price NUMBER DEFAULT 0,
  CONSTRAINT FK_USERID_PRODUCT FOREIGN KEY(user_id) REFERENCES MEMBER(user_id) ON DELETE CASCADE,
  CONSTRAINT FK_CATEGORY_PRODUCT FOREIGN KEY(category_id) REFERENCES CATEGORY(category_id)
);



CREATE TABLE VIDEOS(
  video_id NUMBER PRIMARY KEY,
  product_id NUMBER,
  video_url VARCHAR2(200) NOT NULL,
  CONSTRAINT FK_PRODUCTID_VIDEOS FOREIGN KEY(product_id) REFERENCES PRODUCT(product_id) ON DELETE CASCADE
);



CREATE TABLE IMAGES(
  image_id NUMBER PRIMARY KEY,
  product_id NUMBER,
  image_url VARCHAR2(200) NOT NULL,
  CONSTRAINT FK_PRODUCTID_IMAGES FOREIGN KEY(product_id) REFERENCES PRODUCT(product_id) ON DELETE CASCADE
);



CREATE TABLE DEAL(
  deal_id NUMBER PRIMARY KEY,
  product_id NUMBER,
  user_id NUMBER,
  status NUMBER DEFAULT 0,
  CONSTRAINT FK_PRODUCTID_DEAL FOREIGN KEY(product_id) REFERENCES PRODUCT(product_id) ON DELETE CASCADE,
  CONSTRAINT FK_MEMBERID_DEAL FOREIGN KEY(user_id) REFERENCES MEMBER(user_id) ON DELETE CASCADE
);



CREATE TABLE PRODUCT_COMMENTS(
  comment_id NUMBER PRIMARY KEY,
  product_id NUMBER,
  comment_contents CLOB,
  comment_writer VARCHAR2(30) NOT NULL,
  comment_date DATE NOT NULL,
  CONSTRAINT FK_PRODUCTID_PROD_COMMENT FOREIGN KEY(product_id) REFERENCES PRODUCT(product_id)
);



CREATE TABLE WISHLIST(
  wish_id NUMBER PRIMARY KEY,
  product_id NUMBER,
  user_id NUMBER,
  wish_date DATE NOT NULL,
  CONSTRAINT FK_PRODUCTID_WISH FOREIGN KEY(product_id) REFERENCES PRODUCT(product_id) ON DELETE CASCADE,
  CONSTRAINT FK_USERID_WISH FOREIGN KEY(user_id) REFERENCES MEMBER(user_id) ON DELETE CASCADE
);


/*
ALTER TABLE PRODUCT ADD CONSTRAINT UNIQ_USERID_PRODUCT UNIQUE(user_id);
*/

CREATE TABLE CHAT_ROOM(
  room_id NUMBER PRIMARY KEY,
  user_id NUMBER,
  product_id NUMBER,
  participant NUMBER,
  CONSTRAINT FK_USERID_CHATROOM FOREIGN KEY(user_id) REFERENCES MEMBER(user_id),
  CONSTRAINT FK_PRODUCTID_CHATROOM FOREIGN KEY(product_id) REFERENCES PRODUCT(product_id)
  /*,
  CONSTRAINT FK_PATICIPANT_CHATROOM FOREIGN KEY(participant) REFERENCES PRODUCT(user_id)*/
);


CREATE TABLE MESSAGE(
  message_id NUMBER PRIMARY KEY,
  room_id NUMBER,
  message_content CLOB,
  log_time DATE NOT NULL,
  CONSTRAINT FK_ROOMID_MESSAGE FOREIGN KEY(room_id) REFERENCES CHAT_ROOM(room_id) ON DELETE CASCADE
);


CREATE TABLE MANAGER(
  manager_id NUMBER PRIMARY KEY,
  id VARCHAR2(30) NOT NULL,
  password VARCHAR2(30) NOT NULL
);



CREATE TABLE PAYMENT(
  payment_id NUMBER PRIMARY KEY,
  user_id NUMBER,
  CONSTRAINT FK_USERID_PAYMENT FOREIGN KEY(user_id) REFERENCES MEMBER(user_id)
);



CREATE TABLE BOARD_CATEGORY(
  category_id NUMBER PRIMARY KEY,
  category_name VARCHAR2(20) NOT NULL
);


CREATE TABLE REPORT_CATEGORY(
  category_id NUMBER PRIMARY KEY,
  category_name VARCHAR2(30) NOT NULL
);



CREATE TABLE QNA(
  qna_id NUMBER PRIMARY KEY,
  category_id NUMBER,
  user_id NUMBER,
  qna_title VARCHAR2(30) NOT NULL,
  qna_writer VARCHAR2(30) NOT NULL,
  qna_contents CLOB NOT NULL,
  qna_date DATE NOT NULL,
  CONSTRAINT FK_CATEGORYID_QNA FOREIGN KEY(category_id) REFERENCES BOARD_CATEGORY(category_id),
  CONSTRAINT FK_USERID_QNA FOREIGN KEY(user_id) REFERENCES MEMBER(user_id)
);



CREATE TABLE COMMENTS(
  comment_id NUMBER PRIMARY KEY,
  manager_id NUMBER,
  qna_id NUMBER,
  comment_contents CLOB NOT NULL,
  commnet_date DATE NOT NULL,
  comment_writer VARCHAR2(30) NOT NULL,
  CONSTRAINT FK_MANAGERID_COMMENTS FOREIGN KEY(manager_id) REFERENCES MANAGER(manager_id),
  CONSTRAINT FK_QNAID_COMMENTS FOREIGN KEY(qna_id) REFERENCES QNA(qna_id)
);



CREATE TABLE FAQ(
  faq_id NUMBER PRIMARY KEY,
  category_id NUMBER,
  manager_id NUMBER,
  faq_category NUMBER NOT NULL,
  faq_title VARCHAR2(30) NOT NULL,
  faq_contents CLOB NOT NULL,
  faq_date DATE NOT NULL,
  CONSTRAINT FK_CATEGORYID_FAQ FOREIGN KEY(category_id) REFERENCES BOARD_CATEGORY(category_id),
  CONSTRAINT FK_MANAGERID_FAQ FOREIGN KEY(manager_id) REFERENCES MANAGER(manager_id)
);


CREATE TABLE REPORT(
  report_id NUMBER PRIMARY KEY,
  category_id NUMBER,
  user_id NUMBER,
  report_contents CLOB NOT NULL,
  report_writer VARCHAR2(30) NOT NULL,
  report_target VARCHAR2(30) NOT NULL,
  report_date DATE NOT NULL,
  CONSTRAINT FK_CATEGORYID_REPORT FOREIGN KEY(category_id) REFERENCES REPORT_CATEGORY(category_id) ON DELETE CASCADE,
  CONSTRAINT FK_USERID_REPORT FOREIGN KEY(user_id) REFERENCES MEMBER(user_id) ON DELETE CASCADE
);



CREATE TABLE NOTICE(
  notice_id NUMBER PRIMARY KEY,
  manager_id NUMBER,
  notice_title VARCHAR2(30) NOT NULL,
  notice_contents CLOB NOT NULL,
  notice_writer VARCHAR2(30) NOT NULL,
  notice_date DATE NOT NULL,
  CONSTRAINT FK_MANAGERID_NOTICE FOREIGN KEY(manager_id) REFERENCES MANAGER(manager_id) ON DELETE CASCADE
);


CREATE TABLE PENALTY(
  penalty_id NUMBER PRIMARY KEY,
  user_id NUMBER,
  manager_id NUMBER,
  penalty_contents CLOB NOT NULL,
  penalty_date DATE NOT NULL,
  end_date DATE NOT NULL,
  CONSTRAINT FK_USERID_PENALTY FOREIGN KEY(user_id) REFERENCES MEMBER(user_id) ON DELETE CASCADE,
  CONSTRAINT FK_MANAGERID_PENALTY FOREIGN KEY(manager_id) REFERENCES MANAGER(manager_id) ON DELETE CASCADE
);


CREATE SEQUENCE SEQ_CATEGORYID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_BOARD_CATEGORYID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_CHAT_ROOMID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_COMMENTID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_DEALID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_FAQID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_IMAGEID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_MANAGERID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_MEMBER_IMGID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_MESSAGEID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_NOTICEID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_PAYMENTID
  INCREMENT BY 1
  START WITH 1;

CREATE SEQUENCE SEQ_PENALTYID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_PRODUCT_COMMENTID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_QNAID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_REPORTID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_REPORT_CATEGORYID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_WISHLISTID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_MEMBER
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_PRODUCTID
  INCREMENT BY 1
  START WITH 1;
  
CREATE SEQUENCE SEQ_VIDEOID
  INCREMENT BY 1
  START WITH 1;
 
CREATE SEQUENCE SEQ_USERID
  INCREMENT BY 1
  START WITH 1;

  
ALTER TABLE MEMBER ADD(NAME VARCHAR2(50));
ALTER TABLE MEMBER MODIFY(ADDRESS VARCHAR2(200));

INSERT INTO CATEGORY(
  CATEGORY_ID, CATEGORY_NAME
  )VALUES(
  SEQ_CATEGORYID.NEXTVAL, 'category'
  );

INSERT INTO PRODUCT(
  PRODUCT_ID, USER_ID, CATEGORY_ID, PRODUCT_NAME, TITLE, PRODUCT_INFO, MIN_PRICE,
  PAY_METHOD, CREATE_DATE, SALE_DATE, STATUS, BUYER_ID
  )VALUES(
  SEQ_PRODUCTID.NEXTVAL, 3, 1, 'test', 'testTitle', 'exam for stream', 3000, 'cash', SYSDATE, SYSDATE + 1, 1, 15
  );
  
INSERT INTO VIDEOS(
  VIDEO_ID, PRODUCT_ID, VIDEO_URL
  )VALUES(
  SEQ_VIDEOID.NEXTVAL, 2, 'https://youtube.com/embed/_aM1d3KNPB8?autoplay=1&mute=1'
  );

SET DEFINE OFF;

/*
5.12 final 수정
코인결제 테이블 삭제
회원 - 보유코인 삭제
게시카테고리 테이블 삭제
FAQ, 문의사항 - 카테고리id 삭제
판매상품 - 낙찰가, 시작가격, 판매기간, 결제방식
판매상품 - 구매자 널 허용 수정, 가격(price)컬럼 추가
*/

drop table payment;

alter table product drop column sale_price;
alter table product drop column min_price;
alter table product drop column sale_date;
alter table product drop column pay_method;

alter table product add price number;
alter table product modify (buyer_id number null);

drop table board_category;

/*상품 판매날짜 추가*/
alter table product add sale_date date;

/*상품 이미지 추가*/
alter table product add image varchar2(50);

/*토큰 인증테이블 추가*/
CREATE TABLE member_auth
(user_id number primary key,
auth VARCHAR2(50) NOT NULL,
CONSTRAINT FK_auth_user_id FOREIGN KEY(user_id) REFERENCES member(user_id)
);

alter table member add
enabled char(1) default '1';

/*수정*/
alter table member modify password varchar2(150);