0. VSCODE에서 ERD생성
1. ERD 기반 DDL 코드
CREATE TABLE BOARD
(
BNO NUMBER ,
BTITLE VARCHAR2(100),
BCONTENT CLOB ,
BHIT NUMBER DEFAULT 1,
BREGDATE DATE DEFAULT CURRENT_DATE,
BTYPE NUMBER(1) ,
UEMAIL VARCHAR2(100)
);
COMMENT ON TABLE BOARD IS '게시판테이블';
COMMENT ON COLUMN BOARD.BNO IS '게시글번호';
COMMENT ON COLUMN BOARD.BTITLE IS '제목';
COMMENT ON COLUMN BOARD.BCONTENT IS '내용';
COMMENT ON COLUMN BOARD.BHIT IS '조회수';
COMMENT ON COLUMN BOARD.BREGDATE IS '등록일';
COMMENT ON COLUMN BOARD.BTYPE IS '게시판종류';
COMMENT ON COLUMN BOARD.UEMAIL IS '이메일';
CREATE TABLE BUY
(
BNO NUMBER NOT NULL,
BCNT NUMBER ,
BREGDATE TIMESTAMP ,
ICODE NUMBER NOT NULL,
UEMAIL VARCHAR2(100) NOT NULL,
CONSTRAINT PK_BUY PRIMARY KEY (BNO)
);
COMMENT ON TABLE BUY IS '주문테이블';
COMMENT ON COLUMN BUY.BNO IS '주문번호';
COMMENT ON COLUMN BUY.BCNT IS '주문수량';
COMMENT ON COLUMN BUY.BREGDATE IS '주문일자';
COMMENT ON COLUMN BUY.ICODE IS '물품코드';
COMMENT ON COLUMN BUY.UEMAIL IS '이메일';
CREATE TABLE BUYSTEP
(
BSNO NUMBER NOT NULL,
BNO NUMBER NOT NULL,
STEP NUMBER(2),
REGDATE DATE DEFAULT CURRENT_DATE,
CONSTRAINT PK_BUYSTEP PRIMARY KEY (BSNO)
);
COMMENT ON TABLE BUYSTEP IS '주문절차테이블';
COMMENT ON COLUMN BUYSTEP.BSNO IS '번호';
COMMENT ON COLUMN BUYSTEP.BNO IS '주문번호';
COMMENT ON COLUMN BUYSTEP.STEP IS '절차번호';
COMMENT ON COLUMN BUYSTEP.REGDATE IS '날짜';
CREATE TABLE CART
(
CNO NUMBER NOT NULL,
CCNT NUMBER ,
CREGDATE DATE DEFAULT CURRENT_DATE,
ICODE NUMBER NOT NULL,
UEMAIL VARCHAR2(100),
CONSTRAINT PK_CART PRIMARY KEY (CNO)
);
COMMENT ON TABLE CART IS '장바구니테이블';
COMMENT ON COLUMN CART.CNO IS '카트번호';
COMMENT ON COLUMN CART.CCNT IS '수량';
COMMENT ON COLUMN CART.CREGDATE IS '등록일';
COMMENT ON COLUMN CART.ICODE IS '물품코드';
COMMENT ON COLUMN CART.UEMAIL IS '이메일';
CREATE TABLE ITEM
(
ICODE NUMBER NOT NULL,
INAME VARCHAR2(100),
ICONTENT CLOB ,
IPRICE NUMBER DEFAULT 0,
IQUANTITY NUMBER DEFAULT 0,
IREGDATE DATE DEFAULT CURRENT_DATE,
IIMAGE BLOB ,
IIMAGENAME VARCHAR2(100),
IIMAGESIZE NUMBER DEFAULT 0,
IIMAGETYPE VARCHAR2(30) ,
UEMAIL VARCHAR2(100) NOT NULL,
CONSTRAINT PK_ITEM PRIMARY KEY (ICODE)
);
COMMENT ON TABLE ITEM IS '물품테이블';
COMMENT ON COLUMN ITEM.ICODE IS '물품코드';
COMMENT ON COLUMN ITEM.INAME IS '물품이름';
COMMENT ON COLUMN ITEM.ICONTENT IS '물품내용';
COMMENT ON COLUMN ITEM.IPRICE IS '물품가격';
COMMENT ON COLUMN ITEM.IQUANTITY IS '재고수량';
COMMENT ON COLUMN ITEM.IREGDATE IS '등록일';
COMMENT ON COLUMN ITEM.IIMAGE IS '대표이미지';
COMMENT ON COLUMN ITEM.IIMAGENAME IS '이미지이름';
COMMENT ON COLUMN ITEM.IIMAGESIZE IS '이미지크기';
COMMENT ON COLUMN ITEM.IIMAGETYPE IS '이미지타입';
COMMENT ON COLUMN ITEM.UEMAIL IS '이메일';
CREATE TABLE ITEMIMAGE
(
IMGCODE NUMBER NOT NULL,
INAME VARCHAR2(100),
ISIZE NUMBER DEFAULT 0,
ITYPE VARCHAR2(30) ,
IREGDATE DATE DEFAULT CURRENT_DATE,
ICODE NUMBER NOT NULL,
CONSTRAINT PK_ITEMIMAGE PRIMARY KEY (IMGCODE)
);
COMMENT ON TABLE ITEMIMAGE IS '물품이미지';
COMMENT ON COLUMN ITEMIMAGE.IMGCODE IS '물품이미지코드';
COMMENT ON COLUMN ITEMIMAGE.INAME IS '이미지이름';
COMMENT ON COLUMN ITEMIMAGE.ISIZE IS '이미지크기';
COMMENT ON COLUMN ITEMIMAGE.ITYPE IS '이미지타입';
COMMENT ON COLUMN ITEMIMAGE.IREGDATE IS '이미지등록일';
COMMENT ON COLUMN ITEMIMAGE.ICODE IS '물품코드';
CREATE TABLE MEMBER
(
UEMAIL VARCHAR2(100) NOT NULL,
UPW VARCHAR2(200) NOT NULL,
UNAME VARCHAR2(15) ,
UPHONE VARCHAR2(15) DEFAULT 010-0000-0000,
UROLE VARCHAR2(10) DEFAULT 'CUSTOMER',
UREGDATE DATE DEFAULT CURRENT_DATE,
CONSTRAINT PK_MEMBER PRIMARY KEY (UEMAIL)
);
COMMENT ON TABLE MEMBER IS '회원테이블';
COMMENT ON COLUMN MEMBER.UEMAIL IS '이메일';
COMMENT ON COLUMN MEMBER.UPW IS '암호';
COMMENT ON COLUMN MEMBER.UNAME IS '이름';
COMMENT ON COLUMN MEMBER.UPHONE IS '연락처';
COMMENT ON COLUMN MEMBER.UROLE IS '권한';
COMMENT ON COLUMN MEMBER.UREGDATE IS '등록일';
CREATE TABLE MEMBERADDR
(
UCODE NUMBER NOT NULL,
UADDRESS VARCHAR2(150),
UPOSTCODE VARCHAR2(10) ,
UREGDATE DATE DEFAULT CURRENT_DATE,
UEMAIL VARCHAR2(100) NOT NULL,
UCHK NUMBER(2) DEFAULT 1,
CONSTRAINT PK_MEMBERADDR PRIMARY KEY (UCODE)
);
COMMENT ON TABLE MEMBERADDR IS '회원주소테이블';
COMMENT ON COLUMN MEMBERADDR.UCODE IS '주소코드';
COMMENT ON COLUMN MEMBERADDR.UADDRESS IS '전체주소';
COMMENT ON COLUMN MEMBERADDR.UPOSTCODE IS '우편번호';
COMMENT ON COLUMN MEMBERADDR.UREGDATE IS '등록일';
COMMENT ON COLUMN MEMBERADDR.UEMAIL IS '이메일';
COMMENT ON COLUMN MEMBERADDR.UCHK IS '대표주소';
ALTER TABLE MEMBERADDR
ADD CONSTRAINT FK_MEMBER_TO_MEMBERADDR
FOREIGN KEY (UEMAIL)
REFERENCES MEMBER (UEMAIL);
ALTER TABLE ITEMIMAGE
ADD CONSTRAINT FK_ITEM_TO_ITEMIMAGE
FOREIGN KEY (ICODE)
REFERENCES ITEM (ICODE);
ALTER TABLE ITEM
ADD CONSTRAINT FK_MEMBER_TO_ITEM
FOREIGN KEY (UEMAIL)
REFERENCES MEMBER (UEMAIL);
ALTER TABLE BOARD
ADD CONSTRAINT FK_MEMBER_TO_BOARD
FOREIGN KEY (UEMAIL)
REFERENCES MEMBER (UEMAIL);
ALTER TABLE CART
ADD CONSTRAINT FK_ITEM_TO_CART
FOREIGN KEY (ICODE)
REFERENCES ITEM (ICODE);
ALTER TABLE CART
ADD CONSTRAINT FK_MEMBER_TO_CART
FOREIGN KEY (UEMAIL)
REFERENCES MEMBER (UEMAIL);
ALTER TABLE BUY
ADD CONSTRAINT FK_ITEM_TO_BUY
FOREIGN KEY (ICODE)
REFERENCES ITEM (ICODE);
ALTER TABLE BUY
ADD CONSTRAINT FK_MEMBER_TO_BUY
FOREIGN KEY (UEMAIL)
REFERENCES MEMBER (UEMAIL);
ALTER TABLE BUYSTEP
ADD CONSTRAINT FK_BUY_TO_BUYSTEP
FOREIGN KEY (BNO)
REFERENCES BUY (BNO);2. 안되면 드랍더 테이블
DROP TABLE MEMBER CASCADE CONSTRAINTS;
DROP TABLE MEMBERADDR CASCADE CONSTRAINTS;
DROP TABLE BOARD CASCADE CONSTRAINTS;
DROP TABLE ITEM CASCADE CONSTRAINTS;
DROP TABLE ITEMIMAGE CASCADE CONSTRAINTS;
DROP TABLE CART CASCADE CONSTRAINTS;
DROP TABLE BUY CASCADE CONSTRAINTS;
DROP TABLE BUYSTEP CASCADE CONSTRAINTS;3. 시퀀스 생성
CREATE SEQUENCE SEQ_MEMBERADDR_UCODE START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
CREATE SEQUENCE SEQ_ITEM_ICODE START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
CREATE SEQUENCE SEQ_ITEMIMAGE_ICODE START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
CREATE SEQUENCE SEQ_BOARD_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
CREATE SEQUENCE SEQ_CART_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
CREATE SEQUENCE SEQ_BUY_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
CREATE SEQUENCE SEQ_BUYSTEP_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
The best