[QT Market] 게시판 - DB 설계 ~5차

작성 일시 : 2021년 12월 5일

작성 툴 : ERDCloud

 

• SQL 파일 생성
• 테이블 생성
• 코멘트 제약 조건 추가
• FK로 할 수 없는 컬럼 삭제 (게시판 댓글의 아이디는 FK 필요없음 참조 관계 제거, 게시판 글 이미지의 아이디 속성 불필요하기 때문에 제거)

 

 

 

-- <TABLE 생성>
CREATE TABLE "BOARD_COMMENT" (
	"COMMENT_NO" NUMBER	NOT NULL,
	"BOARD_NO" NUMBER NOT NULL,
	"USER_ID" VARCHAR2(20) NOT NULL,
	"B_WRITER" VARCHAR2(20) NOT NULL,
	"COMMENT_CONTENT" VARCHAR2(1000) NOT NULL,
	"B_COMMENT_CREATED"	DATE NOT NULL,
	"B_COMMENT_MODIFIED" DATE NOT NULL,
	"B_MOM_COMMENT_NO" NUMBER NOT NULL
);

CREATE TABLE "BOARD_IMAGE" (
	"B_IMG_NO" VARCHAR2(100) NOT NULL,
	"BOARD_NO" NUMBER NOT NULL,
	"USER_ID" VARCHAR2(20) NOT NULL,
	"B_IMG_NAME" VARCHAR2(100) NOT NULL,
	"B_IMG_SERVER_NAME"	VARCHAR2(100) NOT NULL,
	"B_IMG_SIZE" VARCHAR2(100) NOT NULL,
	"B_IMG_DATE" DATE NOT NULL
);

CREATE TABLE "BOARD" (
	"BOARD_NO" NUMBER NOT NULL,
	"USER_ID" VARCHAR2(20) NOT NULL,
	"USER_NICKNAME"	VARCHAR2(20) NOT NULL,
	"BOARD_TITLE" VARCHAR2(50) NOT NULL,
	"BOARD_PWD"	VARCHAR2(10) NOT NULL,
	"BOARD_CONTENT"	VARCHAR2(4000) NOT NULL,
	"BOARD_CREATED"	DATE NOT NULL,
	"BOARD_MODIFIED" DATE NULL,
	"BOARD_HIT"	NUMBER NOT NULL,
	"BOARD_FILE" VARCHAR2(100) NULL,
	"ADMIN_NO" NUMBER NOT NULL,
	"B_CATE_NO"	NUMBER NOT NULL
);

CREATE TABLE "BOARD_CATEGORY" (
	"B_CATE_NO"	NUMBER NOT NULL,
	"CATE_NAME"	VARCHAR2(20) NOT NULL
);


-- <TABLE 제약조건 추가>


-- <BOARD TABLE>
-- BOARD 테이블 COMMENT 추가
COMMENT ON COLUMN BOARD.BOARD_NO IS '글번호';
COMMENT ON COLUMN BOARD.USER_ID IS '아이디';
COMMENT ON COLUMN BOARD.USER_NICKNAME IS '닉네임';
COMMENT ON COLUMN BOARD.BOARD_TITLE IS '제목';
COMMENT ON COLUMN BOARD.BOARD_PWD IS '게시글비밀번호';
COMMENT ON COLUMN BOARD.BOARD_CONTENT IS '글내용';
COMMENT ON COLUMN BOARD.BOARD_CREATED IS '작성일';
COMMENT ON COLUMN BOARD.BOARD_MODIFIED IS '수정일';
COMMENT ON COLUMN BOARD.BOARD_HIT IS '조회수';
COMMENT ON COLUMN BOARD.BOARD_FILE IS '첨부파일';
COMMENT ON COLUMN BOARD.ADMIN_NO IS '관리자번호';
COMMENT ON COLUMN BOARD.B_CATE_NO IS '게시판카테고리번호';


-- BOARD 테이블 제약조건 추가
ALTER TABLE BOARD ADD CONSTRAINT BOARD_BOARD_NO_PK PRIMARY KEY(BOARD_NO);
ALTER TABLE BOARD ADD CONSTRAINT BOARD_B_CATE_NO_FK FOREIGN KEY(B_CATE_NO) REFERENCES BOARD_CATEGORY (B_CATE_NO);
-- ALTER TABLE BOARD DROP CONSTRAINT BOARD_B_CATE_NO_FK;
-- ALTER TABLE BOARD ADD CONSTRAINT BOARD_USER_ID_FK FOREIGN KEY(USER_ID) REFERENCES QT_USER (USER_ID);
-- ALTER TABLE BOARD ADD CONSTRAINT BOARD_ADMIN_ID_FK FOREIGN KEY(ADMIN_NO) REFERENCES ADMIN (ADMIN_NO);


-- <BOARD_CATEGORY TABLE>
-- BOARD_CATEGORY 테이블 COMMENT 추가
COMMENT ON COLUMN BOARD_CATEGORY.B_CATE_NO IS '게시판카테고리번호';
COMMENT ON COLUMN BOARD_CATEGORY.CATE_NAME IS '게시판카테고리이름';


-- BOARD_CATEGORY 테이블 제약조건 추가
ALTER TABLE BOARD_CATEGORY ADD CONSTRAINT BOARD_CATEGORY_B_CATE_NO_PK PRIMARY KEY(B_CATE_NO);
-- ALTER TABLE BOARD_CATEGORY DROP CONSTRAINT BOARD_CATEGORY_B_CATE_NO;


-- <BOARD_COMMENT TABLE>
-- BOARD_COMMENT 테이블 COMMENT 추가
COMMENT ON COLUMN BOARD_COMMENT.COMMENT_NO IS '댓글번호';
COMMENT ON COLUMN BOARD_COMMENT.BOARD_NO IS '글번호';
COMMENT ON COLUMN BOARD_COMMENT.USER_ID IS '아이디';
COMMENT ON COLUMN BOARD_COMMENT.B_WRITER IS '댓글회원ID';
COMMENT ON COLUMN BOARD_COMMENT.COMMENT_CONTENT IS '댓글내용';
COMMENT ON COLUMN BOARD_COMMENT.B_COMMENT_CREATED IS '댓글작성일';
COMMENT ON COLUMN BOARD_COMMENT.B_COMMENT_MODIFIED IS '댓글수정일';
COMMENT ON COLUMN BOARD_COMMENT.B_MOM_COMMENT_NO IS '부모댓글번호';


-- BOARD_COMMENT 테이블 제약조건 추가
ALTER TABLE BOARD_COMMENT ADD CONSTRAINT BOARD_COMMENT_COMMENT_NO_PK PRIMARY KEY(COMMENT_NO);
ALTER TABLE BOARD_COMMENT ADD CONSTRAINT BOARD_COMMENT_BOARD_NO_FK FOREIGN KEY(BOARD_NO) REFERENCES BOARD (BOARD_NO);



-- <BOARD_IMAGE TABLE>
-- BOARD_IMAGE 테이블 COMMENT 추가
COMMENT ON COLUMN BOARD_IMAGE.B_IMG_NO IS '이미지파일번호';
COMMENT ON COLUMN BOARD_IMAGE.BOARD_NO IS '글번호';
COMMENT ON COLUMN BOARD_IMAGE.USER_ID IS '아이디';
COMMENT ON COLUMN BOARD_IMAGE.B_IMG_NAME IS '원본파일이름';
COMMENT ON COLUMN BOARD_IMAGE.B_IMG_SERVER_NAME IS '서버파일이름';
COMMENT ON COLUMN BOARD_IMAGE.B_IMG_SIZE IS '파일크기';
COMMENT ON COLUMN BOARD_IMAGE.B_IMG_ IS '파일생성날짜';


-- BOARD_IMAGE 테이블 제약조건 추가
ALTER TABLE BOARD_IMAGE ADD CONSTRAINT BOARD_IMAGE_B_IMG_NO_PK PRIMARY KEY(B_IMG_NO);
ALTER TABLE BOARD_IMAGE ADD CONSTRAINT BOARD_IMAGE_BOARD_NO_FK FOREIGN KEY(BOARD_NO) REFERENCES BOARD (BOARD_NO);






-- 제약 조건 확인
DESC USER_CONSTRAINTS;
SELECT * FROM USER_CONSTRAINTS;

DESC USER_CONS_COLUMNS;
SELECT * FROM USER_CONS_COLUMNS;