기능별 쿼리작성, 시퀀스 작성, 외래키 제약조건 추가

-----------------------------------작성중인 쿼리---------------------------------------------
 

 
 
--먼저 각 게시글과 댓글 번호를 1씩 증가시키기 위해서 고유 시퀀스들을 생성함
CREATE SEQUENCE member_bno  --회원게시판(일반게시물)
START WITH 1
INCREMENT BY 1
MAXVALUE 10000;
 
CREATE SEQUENCE best_bno    --회원게시판(베스트게시물)
START WITH 1
INCREMENT BY 1
MAXVALUE 10000;
 
CREATE SEQUENCE admin_bno   --공지게시판
START WITH 1
INCREMENT BY 1
MAXVALUE 10000;
 
CREATE SEQUENCE member_reply_bno    --회원게시판 댓글
START WITH 1
INCREMENT BY 1
MAXVALUE 10000;
 
CREATE SEQUENCE admin_reply_bno --공지게시판 댓글
START WITH 1
INCREMENT BY 1
MAXVALUE 10000;
 
 
 
--게시판마다 조회수 시퀀스 작성--
CREATE SEQUENCE admin_viewcnt -- 공지게시판 조회수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 
CREATE SEQUENCE member_board_viewcnt -- 회원게시판(일반게시물) 조회수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 
CREATE SEQUENCE member_best_board_viewcnt -- 회원게시판(베스트게시물) 조회수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 
 
--게시판마다 추천수 시퀀스 작성--
CREATE SEQUENCE member_board_recommend -- 회원게시판 (일반게시물) 추천수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 
 
CREATE SEQUENCE member_best_board_recommend -- 회원게시판(베스트게시물) 추천수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 
CREATE SEQUENCE member_reply_recommend -- 회원게시판 댓글 추천수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 
CREATE SEQUENCE admin_board_recommend -- 공지게시판 추천수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 
 
CREATE SEQUENCE admin_board_reply_recommend -- 공지게시판 댓글 추천수
START WITH 1
INCREMENT BY 1
MAXVALUE 20000;
 

--회원게시판(일반게시글) 테이블에 자료를 넣어보기---
insert into MEMBER_BOARD(member_bno, user_id, viewcnt, title, rcnt, content, recommend) 
values (member_bno.nextval, 'dlgkstjq12', member_board_viewcnt.nextval, '게시글쓰기',
1,'처음 쓰는 게시글',member_board_recommend.nextval);
 
 
select * from MEMBER_BOARD;
 
delete from MEMBER WHERE E_MAIL='dlgkstjq623@naver.com';
 
select * from MEMBER;
 
 
 
------------외래키에 제약조건을 추가 참조테이블의 값이 삭제되면 연쇄삭제되도록 수정하기 위해 일단 외래키를 전부 삭제하고 제약조건을 추가해서 다시 생성----
 
 
 
 
ALTER TABLE MEMBER_REPLY DROP CONSTRAINT user_id;
 
ALTER TABLE MEMBER_REPLY
ADD CONSTRAINT user_id FOREIGN KEY (user_id)
REFERENCES MEMBER(user_id)ON DELETE CASCADE;
 
 
ALTER TABLE MEMBER_REPLY DROP CONSTRAINT member_bno;
 
ALTER TABLE MEMBER_REPLY
ADD CONSTRAINT member_bno FOREIGN KEY (member_bno)
REFERENCES MEMBER_BOARD(member_bno)ON DELETE CASCADE;
 
 
ALTER TABLE MEMBER_BOARD DROP CONSTRAINT user_id2;
 
--회원게시판(일반게시물) 테이블의 ID를 외래키로 설정
ALTER TABLE MEMBER_BOARD
ADD CONSTRAINT user_id2 FOREIGN KEY (user_id)
REFERENCES MEMBER(user_id)ON DELETE CASCADE;
 
 
ALTER TABLE MEMBER_BEST_BOARD DROP CONSTRAINT user_id3;
 
--회원게시판(베스트게시물) 테이블의 ID를 외래키로 설정해서 MEMBER 테이블을 참조하도록 설정
ALTER TABLE MEMBER_BEST_BOARD
ADD CONSTRAINT user_id3 FOREIGN KEY (user_id)
REFERENCES MEMBER(user_id)ON DELETE CASCADE;
 
 
 
ALTER TABLE MEMBER_BEST_BOARD DROP CONSTRAINT member_bno2;
 
--회원게시판(베스트게시물) 테이블의 member_bno를 외래키로 설정해서 MEMBER_BOARD 테이블을 참조하도록 설정
ALTER TABLE MEMBER_BEST_BOARD
ADD CONSTRAINT member_bno2 FOREIGN KEY (member_bno)
REFERENCES MEMBER_BOARD(member_bno)ON DELETE CASCADE;
 
 
ALTER TABLE ADMIN_BOARD DROP CONSTRAINT admin_id;
 
--관리자 테이블, 공지게시판 테이블, 공지게시판 댓글 테이블의 FK 제약조건을 설정
ALTER TABLE ADMIN_BOARD
ADD CONSTRAINT admin_id FOREIGN KEY (admin_id)
REFERENCES ADMIN(admin_id)ON DELETE CASCADE;
 
 
 
ALTER TABLE ADMIN_BOARD_REPLY DROP CONSTRAINT bno;
 
 
ALTER TABLE ADMIN_BOARD_REPLY
ADD CONSTRAINT bno FOREIGN KEY (bno)
REFERENCES ADMIN_BOARD(bno)ON DELETE CASCADE;
 
 
--댓글 게시판 BNO의 타입을 변경 (참조할 컬럼의 데이터타입과 일치하도록..)
ALTER TABLE ADMIN_BOARD_REPLY modify(bno number(10));
 
 
 
ALTER TABLE ADMIN_BOARD_REPLY DROP CONSTRAINT user_id4;
 
--공지게시판의 댓글 테이블에 ID를 회원 테이블의 ID를 참조하도록 외래키로 설정
ALTER TABLE ADMIN_BOARD_REPLY
ADD CONSTRAINT user_id4 FOREIGN KEY (user_id)
REFERENCES MEMBER(user_id)ON DELETE CASCADE;