맞춤검색

오라클 스키마 생성

CREATE SEQUENCE F_ID 
 INCREMENT BY 1 
 START WITH 1 
 CACHE 10
;

CREATE TABLE COM_CD_DTLD (
       MSTR_CD              VARCHAR2(5) NOT NULL,
       DTLD_CD              VARCHAR2(6) NOT NULL,
       DTLD_CD_NM           VARCHAR2(100) NOT NULL,
       DTLD_CD_NM1          VARCHAR2(100) NULL,
       DTLD_CD_NM2          VARCHAR2(100) NULL,
       DTLD_CD_EXPL         VARCHAR2(1000) NULL,
       ETC_CD1              VARCHAR2(50) NULL,
       ETC_CD2              VARCHAR2(50) NULL,
       USE_YN               VARCHAR2(1) DEFAULT 'Y' NOT NULL,
       ARA                  NUMBER NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE COM_CD_DTLD IS '공통_코드상세';
COMMENT ON COLUMN COM_CD_DTLD.MSTR_CD IS '마스터코드';
COMMENT ON COLUMN COM_CD_DTLD.DTLD_CD IS '상세코드';
COMMENT ON COLUMN COM_CD_DTLD.DTLD_CD_NM IS '상세코드명';
COMMENT ON COLUMN COM_CD_DTLD.DTLD_CD_NM1 IS '상세코드명1';
COMMENT ON COLUMN COM_CD_DTLD.DTLD_CD_NM2 IS '상세코드명2';
COMMENT ON COLUMN COM_CD_DTLD.DTLD_CD_EXPL IS '상세코드설명';
COMMENT ON COLUMN COM_CD_DTLD.ETC_CD1 IS '기타코드1';
COMMENT ON COLUMN COM_CD_DTLD.ETC_CD2 IS '기타코드2';
COMMENT ON COLUMN COM_CD_DTLD.USE_YN IS '사용여부';
COMMENT ON COLUMN COM_CD_DTLD.ARA IS '정렬';
COMMENT ON COLUMN COM_CD_DTLD.REGR_ID IS '등록자아이디';
COMMENT ON COLUMN COM_CD_DTLD.REG_TM IS '등록일시';
COMMENT ON COLUMN COM_CD_DTLD.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COM_CD_DTLD.UPD_TM IS '수정일시';
CREATE UNIQUE INDEX PK_COM_CD_DTLD ON COM_CD_DTLD
(
       MSTR_CD                        ASC,
       DTLD_CD                        ASC
);

CREATE INDEX IF1_COM_CD_DTLD ON COM_CD_DTLD
(
       MSTR_CD                        ASC
);


ALTER TABLE COM_CD_DTLD
       ADD  ( PRIMARY KEY (MSTR_CD, DTLD_CD) ) ;


CREATE TABLE COM_CD_MSTR (
       MSTR_CD              VARCHAR2(5) NOT NULL,
       MSTR_NM              VARCHAR2(50) NOT NULL,
       CD_STRC              VARCHAR2(10) NULL,
       MAIN_TBL_NM          VARCHAR2(50) NULL,
       USE_YN               VARCHAR2(1) DEFAULT 'Y' NOT NULL,
       COL_NM               VARCHAR2(50) NULL,
       CLF_EXPL             VARCHAR2(1000) NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE COM_CD_MSTR IS '공통_코드마스터';
COMMENT ON COLUMN COM_CD_MSTR.MSTR_CD IS '마스터코드';
COMMENT ON COLUMN COM_CD_MSTR.MSTR_NM IS '마스터코드명';
COMMENT ON COLUMN COM_CD_MSTR.CD_STRC IS '코드구조';
COMMENT ON COLUMN COM_CD_MSTR.MAIN_TBL_NM IS '메인테이블명';
COMMENT ON COLUMN COM_CD_MSTR.USE_YN IS '사용여부';
COMMENT ON COLUMN COM_CD_MSTR.COL_NM IS '컬럼명';
COMMENT ON COLUMN COM_CD_MSTR.CLF_EXPL IS '분류설명';
COMMENT ON COLUMN COM_CD_MSTR.REGR_ID IS '등록자아이디';
COMMENT ON COLUMN COM_CD_MSTR.REG_TM IS '등록일시';
COMMENT ON COLUMN COM_CD_MSTR.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COM_CD_MSTR.UPD_TM IS '수정일시';
CREATE UNIQUE INDEX PK_COM_CD_MSTR ON COM_CD_MSTR
(
       MSTR_CD                        ASC
);


ALTER TABLE COM_CD_MSTR
       ADD  ( PRIMARY KEY (MSTR_CD) ) ;


CREATE TABLE COM_FILEUPLOAD (
       F_ID                 INTEGER NOT NULL,
       F_SEQ                NUMBER(3,0) NOT NULL,
       FILENAME             VARCHAR2(256 BYTE) NOT NULL,
       FILESIZE             NUMBER NOT NULL,
       CONTENT_TYPE         VARCHAR2(200 BYTE) NULL,
       FILEEXT              VARCHAR2(10 BYTE) NULL,
       UPLOADED_FILENAME    VARCHAR2(256 BYTE) NOT NULL,
       UPLOADED_DIRECTORY   VARCHAR2(256 BYTE) NULL,
       DOWN_CNT             NUMBER DEFAULT 0 NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       IMAGE_WIDTH          NUMBER NULL,
       IMAGE_HEIGHT         NUMBER NULL,
       THUMBNAIL_FILENAME   VARCHAR2(256 BYTE) NULL,
       FORM_NM              VARCHAR2(10) NULL,
       REF_TABLE            VARCHAR2(100) NULL
);

COMMENT ON TABLE COM_FILEUPLOAD IS '게시물_첨부파일';
COMMENT ON COLUMN COM_FILEUPLOAD.F_ID IS '첨부파일ID';
COMMENT ON COLUMN COM_FILEUPLOAD.F_SEQ IS '파일일련번호';
COMMENT ON COLUMN COM_FILEUPLOAD.FILENAME IS '파일명';
COMMENT ON COLUMN COM_FILEUPLOAD.FILESIZE IS '파일사이즈';
COMMENT ON COLUMN COM_FILEUPLOAD.CONTENT_TYPE IS 'CONTENT타입';
COMMENT ON COLUMN COM_FILEUPLOAD.FILEEXT IS '파일확장자';
COMMENT ON COLUMN COM_FILEUPLOAD.UPLOADED_FILENAME IS '업로드파일명';
COMMENT ON COLUMN COM_FILEUPLOAD.UPLOADED_DIRECTORY IS '업로드디렉토리';
COMMENT ON COLUMN COM_FILEUPLOAD.DOWN_CNT IS '다운로드수';
COMMENT ON COLUMN COM_FILEUPLOAD.REG_TM IS '등록일시';
COMMENT ON COLUMN COM_FILEUPLOAD.IMAGE_WIDTH IS '이미지가로사이즈';
COMMENT ON COLUMN COM_FILEUPLOAD.IMAGE_HEIGHT IS '이미지세로사이즈';
COMMENT ON COLUMN COM_FILEUPLOAD.THUMBNAIL_FILENAME IS '썸네일파일명';
COMMENT ON COLUMN COM_FILEUPLOAD.FORM_NM IS 'FORM명';
COMMENT ON COLUMN COM_FILEUPLOAD.REF_TABLE IS '참조테이블명';

ALTER TABLE COM_FILEUPLOAD
       ADD  ( PRIMARY KEY (F_ID, F_SEQ) ) ;


CREATE TABLE COM_MENU (
       MENU_ID              VARCHAR2(50) NOT NULL,
       LEAF                 NUMBER(1,0) NULL,
       CLS                  VARCHAR2(20) NULL,
       MENU_NODE            VARCHAR2(50) NULL,
       MENU_NM              VARCHAR2(100) NULL,
       DEPTH                NUMBER(3,0) NULL,
       HREF                 VARCHAR2(256) NULL,
       TARGET               VARCHAR2(20) NULL,
       MENU_TEXT            VARCHAR2(1000) NULL,
       SORT_ORD             NUMBER(6) NULL,
       USE_YN               VARCHAR2(1) NULL
);

COMMENT ON COLUMN COM_MENU.MENU_ID IS '메뉴ID';
COMMENT ON COLUMN COM_MENU.LEAF IS 'LEAF';
COMMENT ON COLUMN COM_MENU.CLS IS 'CLS';
COMMENT ON COLUMN COM_MENU.MENU_NODE IS '상위메뉴ID';
COMMENT ON COLUMN COM_MENU.MENU_NM IS '메뉴명';
COMMENT ON COLUMN COM_MENU.DEPTH IS 'DEPTH';
COMMENT ON COLUMN COM_MENU.HREF IS '링크주소';
COMMENT ON COLUMN COM_MENU.TARGET IS '링크타켓';
COMMENT ON COLUMN COM_MENU.MENU_TEXT IS '메뉴출력TXT';
COMMENT ON COLUMN COM_MENU.SORT_ORD IS '정렬순서';
COMMENT ON COLUMN COM_MENU.USE_YN IS 'USE_YN';
CREATE UNIQUE INDEX XPKCOM_MENU ON COM_MENU
(
       MENU_ID                        ASC
);


ALTER TABLE COM_MENU
       ADD  ( PRIMARY KEY (MENU_ID) ) ;


CREATE TABLE COM_SAMPLE (
       SEQ                  NUMBER NOT NULL,
       USER_ID              VARCHAR2(20) NOT NULL,
       USER_NM              VARCHAR2(20) NOT NULL,
       PWD                  VARCHAR2(20) NOT NULL,
       RRN                  VARCHAR2(13) NULL,
       BIRTH_DAY            VARCHAR2(8) NULL,
       MAX_NUMBER           NUMBER(5,0) NULL,
       MIN_NUMBER           NUMBER(5,0) NULL,
       SELF_DESC            VARCHAR(4000) NULL,
       HTML_DESC            CLOB NULL,
       F_ID                 INTEGER NULL,
       REGR_ID              VARCHAR2(10) NULL,
       REG_TM               DATE DEFAULT sysdate NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON COLUMN COM_SAMPLE.SEQ IS '일련번호';
COMMENT ON COLUMN COM_SAMPLE.USER_ID IS '사용자ID';
COMMENT ON COLUMN COM_SAMPLE.USER_NM IS '사용자명';
COMMENT ON COLUMN COM_SAMPLE.PWD IS '비밀번호';
COMMENT ON COLUMN COM_SAMPLE.RRN IS '주민등록번호';
COMMENT ON COLUMN COM_SAMPLE.BIRTH_DAY IS '생년월일';
COMMENT ON COLUMN COM_SAMPLE.MAX_NUMBER IS '최대값';
COMMENT ON COLUMN COM_SAMPLE.MIN_NUMBER IS '최소값';
COMMENT ON COLUMN COM_SAMPLE.SELF_DESC IS '자기소개';
COMMENT ON COLUMN COM_SAMPLE.HTML_DESC IS 'HTML_DESC';
COMMENT ON COLUMN COM_SAMPLE.F_ID IS '첨부파일ID';
COMMENT ON COLUMN COM_SAMPLE.REGR_ID IS '등록자ID';
COMMENT ON COLUMN COM_SAMPLE.REG_TM IS '등록일시';
COMMENT ON COLUMN COM_SAMPLE.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN COM_SAMPLE.UPD_TM IS '수정일시';
CREATE UNIQUE INDEX XPKCOM_SAMPLE ON COM_SAMPLE
(
       SEQ                            ASC
);


ALTER TABLE COM_SAMPLE
       ADD  ( PRIMARY KEY (SEQ) ) ;


CREATE TABLE HP_BOARD (
       B_ID                 VARCHAR2(20 BYTE) NOT NULL,
       B_SEQ                NUMBER NOT NULL,
       THREAD               NUMBER NOT NULL,
       DEPTH                NUMBER(3,0) DEFAULT 0 NOT NULL,
       CTGRY_CD             VARCHAR2(20) NULL,
       P_B_SEQ              NUMBER NULL,
       TITLE                VARCHAR2(200 BYTE) NULL,
       CNTS                 CLOB NULL,
       TITLE_HTML_YN        VARCHAR2(1) NULL,
       RPLY_CNTS            VARCHAR2(4000) NULL,
       CNTS_HTML_YN         VARCHAR2(1 BYTE) DEFAULT 'N' NULL,
       RPLY_HTML_YN         VARCHAR2(1) NULL,
       SECRET_YN            VARCHAR2(1) NULL,
       PSWD                 VARCHAR2(50) NULL,
       READ_CNT             NUMBER(5,0) DEFAULT 0 NULL,
       EMAIL                VARCHAR2(50) NULL,
       HMPG                 VARCHAR2(100) NULL,
       RPLY_CNT             NUMBER(3,0) NULL,
       ESTM_AVG             NUMBER(2,1) DEFAULT 3.0 NULL,
       POINT_VAL            NUMBER(5,0) DEFAULT 0 NULL,
       GOOD_CNT             NUMBER(3,0) DEFAULT 0 NULL,
       GOOD_RPLY_B_SEQ      NUMBER NULL,
       FILE_CNT             NUMBER(3,0) DEFAULT 0 NULL,
       LINK_URL1            VARCHAR2(256) NULL,
       LINK_URL2            VARCHAR2(256) NULL,
       POPUP_SDATE          DATE NULL,
       POPUP_EDATE          DATE NULL,
       POPUP_WIDTH          VARCHAR2(4 BYTE) NULL,
       POPUP_HEIGHT         VARCHAR2(4 BYTE) NULL,
       POLL_TYPE_DVS        VARCHAR2(10) NULL,
       REGR_NM              VARCHAR2(20) NULL,
       DEL_YN               VARCHAR2(1) DEFAULT 'N' NULL,
       STATUS               VARCHAR2(2) NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       REGR_IP_ADDR         VARCHAR2(15) NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE NULL
);

COMMENT ON TABLE HP_BOARD IS '게시물';
COMMENT ON COLUMN HP_BOARD.B_ID IS '게시판ID';
COMMENT ON COLUMN HP_BOARD.B_SEQ IS '게시물일련번호';
COMMENT ON COLUMN HP_BOARD.THREAD IS 'THREAD';
COMMENT ON COLUMN HP_BOARD.DEPTH IS 'DEPTH';
COMMENT ON COLUMN HP_BOARD.CTGRY_CD IS '카테고리코드';
COMMENT ON COLUMN HP_BOARD.P_B_SEQ IS '상위게시물일련번호';
COMMENT ON COLUMN HP_BOARD.TITLE IS '제목';
COMMENT ON COLUMN HP_BOARD.CNTS IS '내용';
COMMENT ON COLUMN HP_BOARD.TITLE_HTML_YN IS '제목HTML여부';
COMMENT ON COLUMN HP_BOARD.RPLY_CNTS IS '답변내용';
COMMENT ON COLUMN HP_BOARD.CNTS_HTML_YN IS '내용HTML여부';
COMMENT ON COLUMN HP_BOARD.RPLY_HTML_YN IS '답변HTML여부';
COMMENT ON COLUMN HP_BOARD.SECRET_YN IS '비밀글여부';
COMMENT ON COLUMN HP_BOARD.PSWD IS '비밀번호';
COMMENT ON COLUMN HP_BOARD.READ_CNT IS '조회수';
COMMENT ON COLUMN HP_BOARD.EMAIL IS '이메일';
COMMENT ON COLUMN HP_BOARD.HMPG IS '홈페이지주소';
COMMENT ON COLUMN HP_BOARD.RPLY_CNT IS '답변수';
COMMENT ON COLUMN HP_BOARD.ESTM_AVG IS '평가평점수';
COMMENT ON COLUMN HP_BOARD.POINT_VAL IS '배팅포인트수';
COMMENT ON COLUMN HP_BOARD.GOOD_CNT IS '추천수';
COMMENT ON COLUMN HP_BOARD.GOOD_RPLY_B_SEQ IS '답변채택게시물일련번호';
COMMENT ON COLUMN HP_BOARD.FILE_CNT IS '파일수';
COMMENT ON COLUMN HP_BOARD.LINK_URL1 IS '링크1';
COMMENT ON COLUMN HP_BOARD.LINK_URL2 IS '링크2';
COMMENT ON COLUMN HP_BOARD.POPUP_SDATE IS '팝업시작일자';
COMMENT ON COLUMN HP_BOARD.POPUP_EDATE IS '팝업종료일자';
COMMENT ON COLUMN HP_BOARD.POPUP_WIDTH IS '팝업가로사이즈';
COMMENT ON COLUMN HP_BOARD.POPUP_HEIGHT IS '팝업세로사이즈';
COMMENT ON COLUMN HP_BOARD.POLL_TYPE_DVS IS '설문유형';
COMMENT ON COLUMN HP_BOARD.REGR_NM IS '등록자명';
COMMENT ON COLUMN HP_BOARD.DEL_YN IS '삭제여부';
COMMENT ON COLUMN HP_BOARD.STATUS IS '상태';
COMMENT ON COLUMN HP_BOARD.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD.REGR_IP_ADDR IS '등록자IP주소';
COMMENT ON COLUMN HP_BOARD.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD.UPD_TM IS '수정일시';
CREATE INDEX XIF1HP_BOARD ON HP_BOARD
(
       B_ID                           ASC
);


ALTER TABLE HP_BOARD
       ADD  ( PRIMARY KEY (B_ID, B_SEQ) ) ;


CREATE TABLE HP_BOARD_CTGRY (
       CTGRY_CD             VARCHAR2(6) NOT NULL,
       CTGRY_GRP_SEQ        NUMBER NOT NULL,
       CTGRY_NM             VARCHAR2(50) NOT NULL,
       CTGRY_EXPL           VARCHAR2(100) NULL,
       SORT_ORDR            NUMBER NULL,
       USE_YN               VARCHAR2(1) DEFAULT 'Y' NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE HP_BOARD_CTGRY IS '게시판_카테고리';
COMMENT ON COLUMN HP_BOARD_CTGRY.CTGRY_CD IS '카테고리코드';
COMMENT ON COLUMN HP_BOARD_CTGRY.CTGRY_GRP_SEQ IS '카테고리그룹일련번호(순번)';
COMMENT ON COLUMN HP_BOARD_CTGRY.CTGRY_NM IS '카테고리명';
COMMENT ON COLUMN HP_BOARD_CTGRY.CTGRY_EXPL IS '카테고리설명';
COMMENT ON COLUMN HP_BOARD_CTGRY.SORT_ORDR IS '정렬순서';
COMMENT ON COLUMN HP_BOARD_CTGRY.USE_YN IS '사용여부';
COMMENT ON COLUMN HP_BOARD_CTGRY.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD_CTGRY.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD_CTGRY.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD_CTGRY.UPD_TM IS '수정일시';
CREATE UNIQUE INDEX HP_BOARD_CTGRY_PK ON HP_BOARD_CTGRY
(
       CTGRY_CD                       ASC,
       CTGRY_GRP_SEQ                  ASC
);

CREATE INDEX XIF1HP_BOARD_CTGRY ON HP_BOARD_CTGRY
(
       CTGRY_GRP_SEQ                  ASC
);


ALTER TABLE HP_BOARD_CTGRY
       ADD  ( PRIMARY KEY (CTGRY_CD, CTGRY_GRP_SEQ) ) ;


CREATE TABLE HP_BOARD_CTGRY_GRP (
       CTGRY_GRP_SEQ        NUMBER NOT NULL,
       CTGRY_GRP_NM         VARCHAR2(50) NOT NULL,
       CTGRY_GRP_EXPL       VARCHAR2(100) NULL,
       USE_YN               VARCHAR2(1) DEFAULT 'Y' NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE HP_BOARD_CTGRY_GRP IS '게시판_카테고리그룹';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.CTGRY_GRP_SEQ IS '카테고리그룹일련번호(순번)';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.CTGRY_GRP_NM IS '카테고리그룹명';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.CTGRY_GRP_EXPL IS '카테고리그룹설명';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.USE_YN IS '사용여부';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD_CTGRY_GRP.UPD_TM IS '수정일시';
CREATE UNIQUE INDEX HP_BOARD_CTGRY_GRP_PK ON HP_BOARD_CTGRY_GRP
(
       CTGRY_GRP_SEQ                  ASC
);


ALTER TABLE HP_BOARD_CTGRY_GRP
       ADD  ( PRIMARY KEY (CTGRY_GRP_SEQ) ) ;


CREATE TABLE HP_BOARD_FILE (
       B_ID                 VARCHAR2(20 BYTE) NOT NULL,
       B_SEQ                NUMBER NOT NULL,
       F_SEQ                NUMBER NOT NULL,
       FILENAME             VARCHAR2(256 BYTE) NOT NULL,
       FILESIZE             NUMBER NOT NULL,
       CONTENT_TYPE         VARCHAR2(200 BYTE) NULL,
       FILEEXT              VARCHAR2(10 BYTE) NULL,
       UPLOADED_FILENAME    VARCHAR2(256 BYTE) NOT NULL,
       UPLOADED_DIRECTORY   VARCHAR2(256 BYTE) NULL,
       IMAGE_WIDTH          NUMBER NULL,
       IMAGE_HEIGHT         NUMBER NULL,
       DOWN_CNT             NUMBER DEFAULT 0 NULL,
       THUMBNAIL_FILENAME   VARCHAR2(256 BYTE) NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE HP_BOARD_FILE IS '게시물_첨부파일';
COMMENT ON COLUMN HP_BOARD_FILE.B_ID IS '게시판ID';
COMMENT ON COLUMN HP_BOARD_FILE.B_SEQ IS '게시물일련번호';
COMMENT ON COLUMN HP_BOARD_FILE.F_SEQ IS '파일일련번호';
COMMENT ON COLUMN HP_BOARD_FILE.FILENAME IS '파일명';
COMMENT ON COLUMN HP_BOARD_FILE.FILESIZE IS '파일사이즈';
COMMENT ON COLUMN HP_BOARD_FILE.CONTENT_TYPE IS 'CONTENT타입';
COMMENT ON COLUMN HP_BOARD_FILE.FILEEXT IS '파일확장자';
COMMENT ON COLUMN HP_BOARD_FILE.UPLOADED_FILENAME IS '업로드파일명';
COMMENT ON COLUMN HP_BOARD_FILE.UPLOADED_DIRECTORY IS '업로드디렉토리';
COMMENT ON COLUMN HP_BOARD_FILE.IMAGE_WIDTH IS '이미지가로사이즈';
COMMENT ON COLUMN HP_BOARD_FILE.IMAGE_HEIGHT IS '이미지세로사이즈';
COMMENT ON COLUMN HP_BOARD_FILE.DOWN_CNT IS '다운로드수';
COMMENT ON COLUMN HP_BOARD_FILE.THUMBNAIL_FILENAME IS '썸네일파일명';
COMMENT ON COLUMN HP_BOARD_FILE.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD_FILE.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD_FILE.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD_FILE.UPD_TM IS '수정일시';
CREATE INDEX XIF1HP_BOARD_FILE ON HP_BOARD_FILE
(
       B_SEQ                          ASC,
       B_ID                           ASC
);


ALTER TABLE HP_BOARD_FILE
       ADD  ( PRIMARY KEY (B_ID, B_SEQ, F_SEQ) ) ;


CREATE TABLE HP_BOARD_GRP (
       B_GRP_SEQ            NUMBER(2,0) NOT NULL,
       B_GRP_NM             VARCHAR2(40) NOT NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE HP_BOARD_GRP IS '게시판그룹';
COMMENT ON COLUMN HP_BOARD_GRP.B_GRP_SEQ IS '게시판그룹ID';
COMMENT ON COLUMN HP_BOARD_GRP.B_GRP_NM IS '게시판그룹명';
COMMENT ON COLUMN HP_BOARD_GRP.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD_GRP.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD_GRP.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD_GRP.UPD_TM IS '수정일시';
CREATE UNIQUE INDEX HP_BOARD_GRP_PK ON HP_BOARD_GRP
(
       B_GRP_SEQ                      ASC
);


ALTER TABLE HP_BOARD_GRP
       ADD  ( PRIMARY KEY (B_GRP_SEQ) ) ;


CREATE TABLE HP_BOARD_MEMO (
       B_ID                 VARCHAR2(20 BYTE) NOT NULL,
       B_SEQ                NUMBER NOT NULL,
       M_SEQ                NUMBER NOT NULL,
       M_NM                 VARCHAR2(10) NULL,
       M_PSWD               VARCHAR2(20) NULL,
       M_CNTS               VARCHAR2(200 BYTE) NULL,
       ESTM_VAL             NUMBER(1,0) NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       REGR_IP_ADDR         VARCHAR2(15) NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE HP_BOARD_MEMO IS '게시물_댓글';
COMMENT ON COLUMN HP_BOARD_MEMO.B_ID IS '게시판ID';
COMMENT ON COLUMN HP_BOARD_MEMO.B_SEQ IS '게시물일련번호';
COMMENT ON COLUMN HP_BOARD_MEMO.M_SEQ IS '댓글일련번호(순번)';
COMMENT ON COLUMN HP_BOARD_MEMO.M_NM IS '성명';
COMMENT ON COLUMN HP_BOARD_MEMO.M_PSWD IS '비밀번호';
COMMENT ON COLUMN HP_BOARD_MEMO.M_CNTS IS '내용';
COMMENT ON COLUMN HP_BOARD_MEMO.ESTM_VAL IS '평가만족도수';
COMMENT ON COLUMN HP_BOARD_MEMO.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD_MEMO.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD_MEMO.REGR_IP_ADDR IS '등록자IP주소';
COMMENT ON COLUMN HP_BOARD_MEMO.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD_MEMO.UPD_TM IS '수정일시';
CREATE INDEX XIF1HP_BOARD_MEMO ON HP_BOARD_MEMO
(
       B_SEQ                          ASC,
       B_ID                           ASC
);


ALTER TABLE HP_BOARD_MEMO
       ADD  ( PRIMARY KEY (B_ID, B_SEQ, M_SEQ) ) ;


CREATE TABLE HP_BOARD_NTC (
       SEQ                  NUMBER NOT NULL,
       B_ID                 VARCHAR2(20 BYTE) NOT NULL,
       TITLE                VARCHAR2(200 BYTE) NOT NULL,
       CNTS                 CLOB NOT NULL,
       TITLE_HTML_YN        VARCHAR2(1) NULL,
       CNTS_HTML_YN         VARCHAR2(1 BYTE) DEFAULT 'N' NULL,
       READ_CNT             NUMBER(5) DEFAULT 0 NULL,
       NTC_SDATE            VARCHAR2(10 BYTE) NULL,
       NTC_EDATE            VARCHAR2(10 BYTE) NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL
);

COMMENT ON TABLE HP_BOARD_NTC IS '게시판별공지글';
COMMENT ON COLUMN HP_BOARD_NTC.SEQ IS '일련번호(순번)';
COMMENT ON COLUMN HP_BOARD_NTC.B_ID IS '게시판ID';
COMMENT ON COLUMN HP_BOARD_NTC.TITLE IS '제목';
COMMENT ON COLUMN HP_BOARD_NTC.CNTS IS '내용';
COMMENT ON COLUMN HP_BOARD_NTC.TITLE_HTML_YN IS '제목HTML여부';
COMMENT ON COLUMN HP_BOARD_NTC.CNTS_HTML_YN IS '내용HTML여부';
COMMENT ON COLUMN HP_BOARD_NTC.READ_CNT IS '조회수';
COMMENT ON COLUMN HP_BOARD_NTC.NTC_SDATE IS '공지시작일자';
COMMENT ON COLUMN HP_BOARD_NTC.NTC_EDATE IS '공지종료일자';
COMMENT ON COLUMN HP_BOARD_NTC.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD_NTC.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD_NTC.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD_NTC.UPD_TM IS '수정일시';
CREATE UNIQUE INDEX HP_BOARD_NTC_PK ON HP_BOARD_NTC
(
       SEQ                            ASC,
       B_ID                           ASC
);

CREATE INDEX XIF1HP_BOARD_NTC ON HP_BOARD_NTC
(
       B_ID                           ASC
);


ALTER TABLE HP_BOARD_NTC
       ADD  ( PRIMARY KEY (SEQ, B_ID) ) ;


CREATE TABLE HP_BOARD_TYPE (
       B_ID                 VARCHAR2(20 BYTE) NOT NULL,
       BOARD_TYPE           VARCHAR2(10 BYTE) NOT NULL,
       MCHN_CD              VARCHAR2(7) NULL,
       TREE_USE_YN          VARCHAR2(1) NULL,
       BOARD_NM             VARCHAR2(100 BYTE) NOT NULL,
       BOARD_DESC           VARCHAR2(200 BYTE) NULL,
       SKIN_NM              VARCHAR2(20 BYTE) NOT NULL,
       FILE_ABLE_CNT        NUMBER(2) DEFAULT 0 NULL,
       TOP_PRT_CNTS         VARCHAR2(4000 BYTE) NULL,
       BTM_PRT_CNTS         VARCHAR2(4000 BYTE) NULL,
       TOP_INC_URL          VARCHAR2(256) NULL,
       PAGESIZE             NUMBER(2,0) DEFAULT 10 NULL,
       DEF_AUTH             VARCHAR2(5 BYTE) NULL,
       BTM_INC_URL          VARCHAR2(256) NULL,
       FILE_ACCESS_EXT      VARCHAR2(256 BYTE) NULL,
       ADMIN_ID             VARCHAR2(100) NULL,
       FILE_SIZE_LIMIT      NUMBER(2,0) NULL,
       USE_SDATE            DATE NULL,
       B_GRP_SEQ            NUMBER(2,0) NULL,
       REGR_ID              VARCHAR2(10) NOT NULL,
       REG_TM               DATE DEFAULT sysdate NOT NULL,
       UPDR_ID              VARCHAR2(10) NULL,
       UPD_TM               DATE DEFAULT sysdate NULL,
       CTGRY_GRP_SEQ        NUMBER NULL,
       USE_IMG_PRT_YN       VARCHAR2(1) NULL,
       USE_DEPT_YN          VARCHAR2(1) NULL,
       NEW_DAY_CNT          NUMBER NULL,
       HOT_READ_CNT         NUMBER NULL,
       USE_YN               VARCHAR2(1) DEFAULT 'Y' NULL,
       USE_DEL_FLAG_YN      VARCHAR2(1) NULL
);

COMMENT ON TABLE HP_BOARD_TYPE IS '게시판';
COMMENT ON COLUMN HP_BOARD_TYPE.B_ID IS '게시판ID';
COMMENT ON COLUMN HP_BOARD_TYPE.BOARD_TYPE IS '게시판유형';
COMMENT ON COLUMN HP_BOARD_TYPE.MCHN_CD IS '기관코드';
COMMENT ON COLUMN HP_BOARD_TYPE.TREE_USE_YN IS '계층구조사용여부';
COMMENT ON COLUMN HP_BOARD_TYPE.BOARD_NM IS '게시판명';
COMMENT ON COLUMN HP_BOARD_TYPE.BOARD_DESC IS '게시판설명';
COMMENT ON COLUMN HP_BOARD_TYPE.SKIN_NM IS 'SKIN명';
COMMENT ON COLUMN HP_BOARD_TYPE.FILE_ABLE_CNT IS '첨부가능파일수';
COMMENT ON COLUMN HP_BOARD_TYPE.TOP_PRT_CNTS IS '게시판상단HTML';
COMMENT ON COLUMN HP_BOARD_TYPE.BTM_PRT_CNTS IS '게시판하단HTML';
COMMENT ON COLUMN HP_BOARD_TYPE.TOP_INC_URL IS '게시판상단include URL';
COMMENT ON COLUMN HP_BOARD_TYPE.PAGESIZE IS '게시판pagesize';
COMMENT ON COLUMN HP_BOARD_TYPE.DEF_AUTH IS '비회원사용권한';
COMMENT ON COLUMN HP_BOARD_TYPE.BTM_INC_URL IS '게시판하단include URL';
COMMENT ON COLUMN HP_BOARD_TYPE.FILE_ACCESS_EXT IS '첨부가능확장자명';
COMMENT ON COLUMN HP_BOARD_TYPE.ADMIN_ID IS '관리자ID';
COMMENT ON COLUMN HP_BOARD_TYPE.FILE_SIZE_LIMIT IS '첨부가능파일사이즈';
COMMENT ON COLUMN HP_BOARD_TYPE.USE_SDATE IS '사용시작일자';
COMMENT ON COLUMN HP_BOARD_TYPE.B_GRP_SEQ IS '게시판그룹ID';
COMMENT ON COLUMN HP_BOARD_TYPE.REGR_ID IS '등록자ID';
COMMENT ON COLUMN HP_BOARD_TYPE.REG_TM IS '등록일시';
COMMENT ON COLUMN HP_BOARD_TYPE.UPDR_ID IS '수정자ID';
COMMENT ON COLUMN HP_BOARD_TYPE.UPD_TM IS '수정일시';
COMMENT ON COLUMN HP_BOARD_TYPE.CTGRY_GRP_SEQ IS '카테고리그룹일련번호(순번)';
COMMENT ON COLUMN HP_BOARD_TYPE.USE_IMG_PRT_YN IS '이미지사용여부';
COMMENT ON COLUMN HP_BOARD_TYPE.USE_DEPT_YN IS '소속출력여부';
COMMENT ON COLUMN HP_BOARD_TYPE.NEW_DAY_CNT IS '새글표시기준일수';
COMMENT ON COLUMN HP_BOARD_TYPE.HOT_READ_CNT IS '핫표시기준일수';
COMMENT ON COLUMN HP_BOARD_TYPE.USE_YN IS '사용여부';
COMMENT ON COLUMN HP_BOARD_TYPE.USE_DEL_FLAG_YN IS '삭제플래그여부';
CREATE INDEX XIF1HP_BOARD_TYPE ON HP_BOARD_TYPE
(
       B_GRP_SEQ                      ASC
);

CREATE INDEX XIF2HP_BOARD_TYPE ON HP_BOARD_TYPE
(
       CTGRY_GRP_SEQ                  ASC
);


ALTER TABLE HP_BOARD_TYPE
       ADD  ( PRIMARY KEY (B_ID) ) ;


ALTER TABLE COM_CD_DTLD
       ADD  ( FOREIGN KEY (MSTR_CD)
                             REFERENCES COM_CD_MSTR ) ;


ALTER TABLE HP_BOARD
       ADD  ( FOREIGN KEY (B_ID)
                             REFERENCES HP_BOARD_TYPE ) ;


ALTER TABLE HP_BOARD_CTGRY
       ADD  ( FOREIGN KEY (CTGRY_GRP_SEQ)
                             REFERENCES HP_BOARD_CTGRY_GRP ) ;


ALTER TABLE HP_BOARD_FILE
       ADD  ( FOREIGN KEY (B_ID, B_SEQ)
                             REFERENCES HP_BOARD ) ;


ALTER TABLE HP_BOARD_MEMO
       ADD  ( FOREIGN KEY (B_ID, B_SEQ)
                             REFERENCES HP_BOARD ) ;


ALTER TABLE HP_BOARD_NTC
       ADD  ( FOREIGN KEY (B_ID)
                             REFERENCES HP_BOARD_TYPE ) ;


ALTER TABLE HP_BOARD_TYPE
       ADD  ( FOREIGN KEY (CTGRY_GRP_SEQ)
                             REFERENCES HP_BOARD_CTGRY_GRP ) ;


CREATE TABLE COM_ZIP_CD
(
  ZIP_CD   VARCHAR2(6 BYTE)                     NOT NULL,
  AREA_NO  VARCHAR2(3 BYTE)                     NOT NULL,
  SD_NM    VARCHAR2(30 BYTE),
  SGG_NM   VARCHAR2(30 BYTE),
  EMD_NM   VARCHAR2(30 BYTE),
  RI_NM    VARCHAR2(30 BYTE),
  BLD_NM   VARCHAR2(100 BYTE),
  ST_BG    VARCHAR2(30 BYTE),
  END_BG   VARCHAR2(30 BYTE),
  REGR_ID  VARCHAR2(20 BYTE)                    ,
  REG_TM   DATE                                 DEFAULT sysdate,
  UPDR_ID  VARCHAR2(20 BYTE)                    ,
  UPD_TM   DATE                                 DEFAULT sysdate 
);

CREATE UNIQUE INDEX PK_COM_ZIP_CD ON COM_ZIP_CD
(ZIP_CD, AREA_NO);



ALTER TABLE COM_ZIP_CD ADD (
  CONSTRAINT COM_ZIP_CD_PK
 PRIMARY KEY
 (ZIP_CD, AREA_NO));


 CREATE TABLE COM_USER
(
  USER_ID  VARCHAR2(20 BYTE)                    NOT NULL,
  USER_NM  VARCHAR2(100 BYTE),
  USE_YN   VARCHAR2(1 BYTE),
  PWD      VARCHAR2(20 BYTE),
  F_ID     INTEGER
);


CREATE UNIQUE INDEX COM_USER_PK ON COM_USER
(USER_ID);


ALTER TABLE COM_USER ADD (
  CONSTRAINT COM_USER_PK
 PRIMARY KEY
 (USER_ID));



CREATE TABLE PJT_MNG
(
  SEQ              INTEGER                      NOT NULL,
  PJT_NM           VARCHAR2(200 BYTE)           NOT NULL,
  PJT_DESC         CLOB,
  PJT_MEMBER       VARCHAR2(200 BYTE)           NOT NULL,
  PRPS_SDATE       DATE,
  PRPS_EDATE       DATE,
  PJT_ID           VARCHAR2(20 BYTE)            NOT NULL,
  PJT_PWD          VARCHAR2(20 BYTE)            NOT NULL,
  PJT_FOLDER_PATH  VARCHAR2(1000 BYTE)          NOT NULL
);

COMMENT ON COLUMN PJT_MNG.SEQ IS '일련번호';

COMMENT ON COLUMN PJT_MNG.PJT_NM IS '프로젝트명';

COMMENT ON COLUMN PJT_MNG.PJT_DESC IS '프로젝트설명';

COMMENT ON COLUMN PJT_MNG.PJT_MEMBER IS '제안참여인력';

COMMENT ON COLUMN PJT_MNG.PRPS_SDATE IS '제안입찰공고일';

COMMENT ON COLUMN PJT_MNG.PRPS_EDATE IS '제안제출일';

COMMENT ON COLUMN PJT_MNG.PJT_ID IS '사용자ID';

COMMENT ON COLUMN PJT_MNG.PJT_PWD IS '패스워드';


CREATE UNIQUE INDEX PJT_MNG_IDX ON PJT_MNG
(PJT_ID);


CREATE UNIQUE INDEX PJT_MNG_PK ON PJT_MNG
(SEQ);


CREATE UNIQUE INDEX PJT_MNG_IDX2 ON PJT_MNG
(PJT_FOLDER_PATH);


ALTER TABLE PJT_MNG ADD (
  CONSTRAINT PJT_MNG_PK
 PRIMARY KEY
 (SEQ));





Tracked by ClickAider