community sql 추가설정

-- 게시판 종류
DROP TABLE "BOARD_TYPE";

CREATE TABLE "BOARD_TYPE" (
	"BOARD_CD"	NUMBER	PRIMARY KEY,
	"BOARD_NM"	VARCHAR2(50)	NOT NULL
);


COMMENT ON COLUMN "BOARD_TYPE"."BOARD_CD" IS '게시판 코드';
COMMENT ON COLUMN "BOARD_TYPE"."BOARD_NM" IS '게시판 이름';


-- 게시판(게시글 저장 테이블)
CREATE TABLE "BOARD" (
	"BOARD_NO"	NUMBER		NOT NULL,
	"BOARD_TITLE"	VARCHAR2(150)		NOT NULL,
	"BOARD_CONTENT"	VARCHAR2(4000)		NOT NULL,
	"CREATE_DT"	DATE	DEFAULT SYSDATE	NOT NULL,
	"UPDATE_DT"	DATE		NOT NULL,
	"READ_COUNT"	NUMBER	DEFAULT 0	NOT NULL,
	"BOARD_ST"	CHAR(1)	DEFAULT 'N'	NOT NULL,
	"MEMBER_NO"	NUMBER		NOT NULL,
	"BOARD_CD"	NUMBER		NOT NULL
);

COMMENT ON COLUMN "BOARD"."BOARD_NO" IS '게시글 번호(시퀀스)';
COMMENT ON COLUMN "BOARD"."BOARD_TITLE" IS '게시글 제목';
COMMENT ON COLUMN "BOARD"."BOARD_CONTENT" IS '게시글 내용';
COMMENT ON COLUMN "BOARD"."CREATE_DT" IS '작성일';
COMMENT ON COLUMN "BOARD"."UPDATE_DT" IS '마지막 수정일';
COMMENT ON COLUMN "BOARD"."READ_COUNT" IS '조회수';
COMMENT ON COLUMN "BOARD"."BOARD_ST" IS '게시글 상태(정상N, 삭제Y)';
COMMENT ON COLUMN "BOARD"."MEMBER_NO" IS '작성자 회원 번호';
COMMENT ON COLUMN "BOARD"."BOARD_CD" IS '게시판 코드';

-- BOARD 테이블 제약조건 추가
ALTER TABLE BOARD
ADD PRIMARY KEY(BOARD_NO); -- 제약 조건명 생략(SYS_XXXXX)

ALTER TABLE BOARD
ADD CONSTRAINT "FK_BOARD_CD" -- 제약 조건명 지정
FOREIGN KEY("BOARD_CD") -- BOARD의 BOARD_CODE 컬럼에 FK 지정
REFERENCES "BOARD_TYPE"; -- 참조할 테이블

ALTER TABLE BOARD
ADD CONSTRAINT "CHK_BOARD_ST"
CHECK ("BOARD_ST" IN ('N', 'Y'));

-- UPDATE_DT  NULL 허용
ALTER TABLE "BOARD"
MODIFY "UPDATE_DT" NULL;



-- BOARD_NO용 시퀀스
CREATE SEQUENCE SEQ_BOARD_NO;


-- BOARD_TYPE 데이터 삽입
INSERT INTO BOARD_TYPE VALUES(1, '공지사항');
INSERT INTO BOARD_TYPE VALUES(2, '자유 게시판');
INSERT INTO BOARD_TYPE VALUES(3, '질문 게시판');

COMMIT;


-- BOARD 테이블 샘플 데이터 삽입(PL/SQL)
BEGIN
   FOR I IN 1..500 LOOP
      INSERT INTO BOARD 
      VALUES( SEQ_BOARD_NO.NEXTVAL,
              SEQ_BOARD_NO.CURRVAL || '번째 게시글',
              SEQ_BOARD_NO.CURRVAL || '번째 게시글 내용 입니다.',
              DEFAULT, DEFAULT, DEFAULT, DEFAULT, 1, 3
      );
   END LOOP;
END;


-- 제약조건 추가 (BOARD -> MEMBER_S)
ALTER TABLE BOARD
ADD CONSTRAINT FK_BOARD_MEMBER_S
FOREIGN KEY(MEMBER_NO)
REFERENCES MEMBER_S
ON DELETE SET NULL;


-- 게시판 이미지 테이블 생성
CREATE TABLE BOARD_IMG(
   IMG_NO NUMBER PRIMARY KEY,
   IMG_RENAME VARCHAR2(500) NOT NULL,
   IMG_ORIGINAL VARCHAR2(500) NOT NULL,
   IMG_LEVEL NUMBER NOT NULL,
   BOARD_NO NUMBER REFERENCES BOARD
);

COMMENT ON COLUMN BOARD_IMG.IMG_NO IS '이미지 번호';
COMMENT ON COLUMN BOARD_IMG.IMG_RENAME IS '이미지 저장 경로 + 변경명';
COMMENT ON COLUMN BOARD_IMG.IMG_ORIGINAL IS '이미지 원본명';
COMMENT ON COLUMN BOARD_IMG.IMG_LEVEL IS '이미지 위치 지정 번호';
COMMENT ON COLUMN BOARD_IMG.BOARD_NO IS '게시글 번호';


-- 이미지 번호 시퀀스 생성
CREATE SEQUENCE SEQ_IMG_NO NOCACHE;


-- 샘플 데이터
INSERT INTO BOARD_IMG VALUES(
   SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample1.jpg', 'cat1.jpg', 0, 500
);
INSERT INTO BOARD_IMG VALUES(
   SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample2.jpg', 'cat2.jpg', 1, 500
);
INSERT INTO BOARD_IMG VALUES(
   SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample3.jpg', 'cat3.jpg', 2, 
   500
);
INSERT INTO BOARD_IMG VALUES(
   SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample4.jpg', 'cat4.jpg', 3, 500
);
INSERT INTO BOARD_IMG VALUES(
   SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample5.jpg', 'cat5.jpg', 4, 500
);


ALTER TABLE BOARD_IMG
DROP CONSTRAINT SYS_C008489;

ALTER TABLE BOARD_IMG
ADD CONSTRAINT FK_BOARD_BOARD_IMG
FOREIGN KEY(BOARD_NO)
REFERENCES BOARD
ON DELETE SET NULL;

DELETE FROM BOARD_IMG
WHERE BOARD_NO NOT IN(
    SELECT BOARD_NO FROM BOARD
);

ALTER TABLE BOARD_IMG
DROP CONSTRAINT 이름(예시-SYS_C008489;)

ALTER TABLE BOARD_IMG
ADD CONSTRAINT FK_BOARD_BOARD_IMG
FOREIGN KEY(BOARD_NO)
REFERENCES BOARD
ON DELETE SET NULL;

DELETE FROM BOARD_IMG
WHERE BOARD_NO NOT IN(
    SELECT BOARD_NO FROM BOARD
);

-- BOARD 테이블 샘플데이터
INSERT INTO BOARD VALUES(
    SEQ_BOARD_NO.NEXTVAL, '스프링 테스트', '테스트 내용 입니다.', 
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, 1, 1
);

-- 이미지 샘플 데이터
INSERT INTO BOARD_IMG VALUES(SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample1.jpg', 'sample1.jpg', 0, SEQ_BOARD_NO.CURRVAL);
INSERT INTO BOARD_IMG VALUES(SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample2.jpg', 'sample1.jpg', 1, SEQ_BOARD_NO.CURRVAL);
INSERT INTO BOARD_IMG VALUES(SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample3.jpg', 'sample1.jpg', 2, SEQ_BOARD_NO.CURRVAL);
INSERT INTO BOARD_IMG VALUES(SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample4.jpg', 'sample1.jpg', 3, SEQ_BOARD_NO.CURRVAL);
INSERT INTO BOARD_IMG VALUES(SEQ_IMG_NO.NEXTVAL, '/resources/images/board/sample5.jpg', 'sample1.jpg', 4, SEQ_BOARD_NO.CURRVAL);

commit;