[SpringProject_EBD]DB테이블

테이블 & 시퀀스 생성 쿼리문

drop table ebd_report_cmt;
drop table ebd_report_heart;
drop table ebd_report;
drop table ebd_episode_cmt;
drop table ebd_episode_heart;
drop table ebd_episode;
drop table ebd_wording_heart;
drop table ebd_wording;
drop table ebd_market_cmt;
drop table ebd_market_heart;
drop table ebd_market;
drop table ebd_file_cmt;
drop table ebd_file_heart;
drop table ebd_file;
drop table ebd_users;

drop sequence ebd_report_seq;
drop sequence ebd_report_cmt_seq;
drop sequence ebd_episode_seq;
drop sequence ebd_episode_cmt_seq;
drop sequence ebd_wording_seq;
drop sequence ebd_market_seq;
drop sequence ebd_market_cmt_seq;
drop sequence ebd_file_seq;
drop sequence ebd_file_cmt_seq;
drop sequence ebd_report_heart_seq;
drop sequence ebd_episode_heart_seq;
drop sequence ebd_wording_heart_seq;
drop sequence ebd_file_heart_seq;
drop sequence ebd_market_heart_seq;

-- 도서리뷰 회원 테이블
CREATE TABLE ebd_users(
	id VARCHAR2(100) PRIMARY KEY, --아이디
	pwd VARCHAR2(100) NOT NULL, --비밀번호
	name VARCHAR2(100),  --이름
	nick VARCHAR2(100) NOT NULL, --닉네임
	gender VARCHAR2(10), --성별
	birth_year VARCHAR2(10), --생년
	birth_month VARCHAR2(10), --월
	birth_day VARCHAR2(10), --일 
	email VARCHAR2(100), --이메일
	phone VARCHAR2(100), --핸드폰 
	profile VARCHAR2(2000), --프로필 이미지
	regdate DATE, --등록일
	CONSTRAINT users_nick_uk UNIQUE(nick), -- 닉네임은 유일해야한다. 
	CONSTRAINT users_gender_ck CHECK(gender IN('male','female')) --남자일 경우 'm', 여자일 경우 'f'가 들어간다.
);


-- 독후감 테이블 
CREATE TABLE ebd_report(  
	num NUMBER PRIMARY KEY, --글 번호
	booktitle VARCHAR2(2000), --책 제목
	title VARCHAR2(2000), --글 제목 
	writer VARCHAR2(100), --작성자(닉네임) / join 연결고리
	author VARCHAR2(2000), --책 저자
	link VARCHAR2(2000), -- 구매처 링크
	genre VARCHAR2(100), --책 장르
	stars VARCHAR2(50), --평점
	imgpath VARCHAR2(2000), --이미지
	content CLOB, --내용
	publicck VARCHAR2(50) DEFAULT 'private', --공개/비공개 여부 / DEFAULT 'private'
	viewcnt NUMBER, --조회수
	regdate DATE, --등록일
	CONSTRAINT r_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);

-- 독후감 테이블 시퀀스
CREATE SEQUENCE ebd_report_seq;

-- 독후감 댓글 테이블
CREATE TABLE ebd_report_cmt(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자
	content VARCHAR2(500), --댓글 내용
	target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
	ref_group NUMBER, -- 원글(거래 글)의 글 번호 
	cmt_group NUMBER, -- 댓글의 그룹번호
	deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
	regdate DATE --댓글 작성일
);

-- 독후감 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_report_cmt_seq;

CREATE TABLE ebd_report_heart(
	num NUMBER PRIMARY KEY,
	target_num NUMBER,
	writer VARCHAR2(100),
	CONSTRAINT rh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
	CONSTRAINT rh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_report(num) ON DELETE CASCADE
);

CREATE SEQUENCE ebd_report_heart_seq;

-- 도서 에피소드 테이블
CREATE TABLE ebd_episode(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자(로그인 된 아이디)
	title VARCHAR2(2000), --제목
	imgpath VARCHAR2(2000), --이미지
	content CLOB, --내용
	viewcnt NUMBER, --조회수
	regdate DATE, --등록일
	heartcnt INT, --좋아요 수 
	CONSTRAINT e_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);

-- 도서 에피소드 테이블 시퀀스
CREATE SEQUENCE ebd_episode_seq;

-- 독후감 댓글 테이블
CREATE TABLE ebd_episode_cmt(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자
	content VARCHAR2(500), --댓글 내용
	target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
	ref_group NUMBER, -- 원글(거래 글)의 글 번호 
	cmt_group NUMBER, -- 댓글의 그룹번호
	deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
	regdate DATE --댓글 작성일 
);

-- 독후감 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_episode_cmt_seq;

CREATE TABLE ebd_episode_heart(
	num NUMBER PRIMARY KEY,
	target_num NUMBER,
	writer VARCHAR2(100),
	CONSTRAINT eh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
	CONSTRAINT eh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_episode(num) ON DELETE CASCADE
);

CREATE SEQUENCE ebd_episode_heart_seq;

-- 명언 테이블 
CREATE TABLE ebd_wording(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자(로그인 된 아이디)
	title VARCHAR2(2000), --책 제목
	content CLOB, -- 내용(명언)
	author VARCHAR2(2000), --책의 작가 
	viewcnt NUMBER, --조회수
	regdate DATE, --등록일
	CONSTRAINT w_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);

-- 명언 테이블 시퀀스
CREATE SEQUENCE ebd_wording_seq;


CREATE TABLE ebd_wording_heart(
	num NUMBER PRIMARY KEY,
	target_num NUMBER, --글의 번호
	writer VARCHAR2(100), --좋아요 누른 사람
	CONSTRAINT wh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
	CONSTRAINT wh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_wording(num) ON DELETE CASCADE --지우지마세영~
);

CREATE SEQUENCE ebd_wording_heart_seq;

-- 도서 거래 테이블
CREATE TABLE ebd_market(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자(로그인 된 아이디)
	title VARCHAR2(2000), --제목
	salestype VARCHAR2(50), --판매유형 나눔/교환/판매
	salesstatus VARCHAR2(50), --판매상태 판매중/완료
	imgpath VARCHAR2(2000), --이미지 파일
	content CLOB, --내용
	viewcnt NUMBER, --조회수
	regdate DATE, --등록일
	CONSTRAINT m_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);

-- 도서 거래 테이블 시퀀스
CREATE SEQUENCE ebd_market_seq;

--도서 거래 하트 테이블
CREATE TABLE ebd_market_heart(
	num NUMBER PRIMARY KEY,
	target_num NUMBER,
	writer VARCHAR2(100),
	CONSTRAINT mh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
	CONSTRAINT mh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_market(num) ON DELETE CASCADE
);

--도서 거래 하트 시퀀스 
CREATE SEQUENCE ebd_market_heart_seq;


-- 도서 거래 댓글 테이블
CREATE TABLE ebd_market_cmt(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자
	content VARCHAR2(500), --댓글 내용
	target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
	ref_group NUMBER, -- 원글(거래 글)의 글 번호 
	cmt_group NUMBER, -- 댓글의 그룹번호
	deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
	regdate DATE --댓글 작성일
);

-- 도서 거래 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_market_cmt_seq;

-- 독후감 양식 파일 업로드 테이블
CREATE TABLE ebd_file(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자(로그인 된 아이디의 닉네임)
	title VARCHAR2(2000), --제목
	orgfname VARCHAR2(2000), --파일명
	savefname VARCHAR2(2000), --파일명
	filesize NUMBER,--파일 사이즈
	imgpath VARCHAR2(2000), --이미지 파일
	content CLOB, --내용
	viewcnt NUMBER, --조회수
	regdate DATE, --등록일
	CONSTRAINT f_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE 
);

-- 독후감 양식 파일 업로드 테이블 시퀀스
CREATE SEQUENCE ebd_file_seq;

-- 독후감 양식 파일 업로드 댓글 테이블
CREATE TABLE ebd_file_cmt(
	num NUMBER PRIMARY KEY, --글 번호
	writer VARCHAR2(100), --작성자
	content VARCHAR2(500), --댓글 내용
	target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
	ref_group NUMBER, -- 원글(거래 글)의 글 번호 
	cmt_group NUMBER, -- 댓글의 그룹번호
	deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
	regdate DATE --댓글 작성일 
);

-- 독후감 양식 파일 업로드 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_file_cmt_seq;


CREATE TABLE ebd_file_heart(
   num NUMBER PRIMARY KEY,
   target_num NUMBER, 
   writer VARCHAR2(100), 
   CONSTRAINT fh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, 
   CONSTRAINT fh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_file(num) ON DELETE CASCADE
);

CREATE SEQUENCE ebd_file_heart_seq;

 

 

TRIGGER

--트리거에서 사용된 테이블 삭제 시 트리거도 같이 삭제됩니다!! 주의하세요!!
--user테이블의 nick이 update 되었을 때 발생하는 trigger
	CREATE TRIGGER tg_user_update
	AFTER UPDATE OF nick ON ebd_users FOR EACH ROW   
	BEGIN
					UPDATE ebd_report  
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;
					
					UPDATE ebd_report_cmt 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;
					
					UPDATE ebd_report_cmt 
	        SET target_nick = :NEW.nick
	        WHERE target_nick = :OLD.nick;
					
					UPDATE ebd_report_heart 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;  
					
					UPDATE ebd_wording 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick; 
					
					UPDATE ebd_wording_heart 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick; 
					
					UPDATE ebd_episode 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;  
					
					UPDATE ebd_episode_cmt 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;
					
					UPDATE ebd_episode_cmt 
	        SET target_nick = :NEW.nick
	        WHERE target_nick = :OLD.nick; 
					
					UPDATE ebd_episode_heart 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick; 
					
					UPDATE ebd_file 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;  
					
					UPDATE ebd_file_cmt 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;
					
					UPDATE ebd_file_cmt 
	        SET target_nick = :NEW.nick
	        WHERE target_nick = :OLD.nick;

					UPDATE ebd_file_heart 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick; 
					
					UPDATE ebd_market 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;
					
					UPDATE ebd_market_cmt 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick; 
					
					UPDATE ebd_market_cmt 
	        SET target_nick = :NEW.nick
	        WHERE target_nick = :OLD.nick; 
		
					UPDATE ebd_market_heart 
	        SET writer = :NEW.nick
	        WHERE writer = :OLD.nick;             
	    END;
	/


--user테이블의 행이 삭제되었을 때 발생하는 trigger 
CREATE TRIGGER tg_user_delete
    AFTER DELETE ON ebd_users FOR EACH ROW   
    BEGIN
				UPDATE ebd_report_cmt 
        SET deleted = 'yes'
        WHERE deleted = 'no' AND writer= :OLD.nick;  
				
				UPDATE ebd_episode_cmt 
        SET deleted = 'yes'
        WHERE deleted = 'no' AND writer= :OLD.nick;  
				
				UPDATE ebd_file_cmt 
        SET deleted = 'yes'
        WHERE deleted = 'no' AND writer= :OLD.nick;  
				
				UPDATE ebd_market_cmt 
        SET deleted = 'yes'
        WHERE deleted = 'no' AND writer= :OLD.nick;  
    END;
/

 

트리거 연습

테이블
tmp.writer   target_id(원글의 작성자 이름이 들어간다)
홍주           지윤
홍주            홍주 
홍주           다솜
지윤                지윤 
지윤                홍주 
지윤           새영

target_id 

홍주 --> 홍주22로 바꿈 


create table test_user(
	id varchar2(50) PRIMARY KEY,
	nick varchar2(50) not null,
	name varchar2(50),
	constraint usertest_nick_uk UNIQUE(nick)
);

create table test_cmt(
	writer varchar2(50),
	target_id varchar2(50),
	content varchar2(50),
	deleted CHAR(3) DEFAULT 'no'
);

create table test_report(
	writer varchar2(50),
	content varchar2(50),
	constraint reporttest_writer_fk FOREIGN KEY(writer) REFERENCES test_user(nick) on delete cascade
);

create table test_report(
	writer varchar2(50),
	content varchar2(50),
	constraint reporttest_writer_fk FOREIGN KEY(writer) REFERENCES test_user(nick) on update cascade
);



insert into test_user values('id1','kinhoz','홍주');
insert into test_user values('id2','jyshin','지윤');
insert into test_user values('id3','dasom','다솜');
insert into test_user values('id4','young','새영');


insert into test_cmt(writer, target_id, content) values('kinhoz', 'jyshin','안녕홍주!');
insert into test_cmt(writer, target_id, content) values('kinhoz', 'kinhoz','안녕하세요');
insert into test_cmt(writer, target_id, content) values('kinhoz', 'dasom','이 책 좋은가요');
insert into test_cmt(writer, target_id, content) values('jyshin', 'jyshin','네 좋아요');
insert into test_cmt(writer, target_id, content) values('jyshin', 'kinhoz','아니 안좋아요');
insert into test_cmt(writer, target_id, content) values('dasom','young','과연..');

insert into test_report values('kinhoz','북스북스들 이 책 좋아요 홍주');
insert into test_report values('kinhoz','이 책이 너무 인상깊어요! 홍주');
insert into test_report values('jyshin','책 추천해주세요 지윤');
insert into test_report values('dasom','이 책 이름이 뭔가요? 다솜');
insert into test_report values('young','책읽아웃 새영');
insert into test_report values('young','이 책 좋아요~! 새영');








1. 안된다.
SQL> insert into test_cmt values('kinhoz', 'jyshinv');
insert into cmttest values('kinhoz', 'jyshinv')
*
ERROR at line 1:
ORA-02291: integrity constraint
(SCOTT.CMTTEST_FK2) violated - parent key not
found


2. 안된다.
SQL> update test_user set nick='kinhoz22' where nick='kinhoz';
update usertest set nick='kinhoz22' where nick='kinhoz'
*
ERROR at line 1:
ORA-02292: integrity constraint
(SCOTT.CMTTEST_FK1) violated - child record found


//네번째 시도 ===된다 대박!!
//주의!! 트리거에 쓰인 테이블 drop시 trigger도 같이 drop 된다!!!!

--nick이 변경되었을 때 트리거 테스트
CREATE TRIGGER tg1
    AFTER UPDATE OF nick ON test_user FOR EACH ROW   
    BEGIN
        UPDATE test_cmt --커멘트의 writer가 바껴야하고
        SET writer = :NEW.nick
        WHERE writer = :OLD.nick;

				UPDATE test_cmt --커멘트의 target_id도 바뀌어야하고
        SET target_id= :NEW.nick
        WHERE target_id = :OLD.nick;

				UPDATE test_report --리포트의 writer가 바뀌어야한다. 
        SET writer= :NEW.nick
        WHERE writer = :OLD.nick;       
    END;
/

--nick 삭제되었을 때 트리거 테스트 
CREATE TRIGGER tg2
    AFTER DELETE ON test_user FOR EACH ROW   
    BEGIN
				UPDATE test_cmt
        SET deleted = 'yes'
        WHERE deleted = 'no' AND writer= :OLD.nick;  
    END;
/


--이걸로 테스트 해보기 
update test_user set nick='kinhoz22' where nick='kinhoz';
delete test_user where nick='kinhoz22';
drop trigger 트리거명; --트리거 삭제

 

참고 DB

-- 댓글을 저장할 테이블 
CREATE TABLE board_cafe_comment(
	num NUMBER PRIMARY KEY, --글번호
	writer VARCHAR2(100),--작성자
	content VARCHAR2(500),--내용
	target_id VARCHAR2(100),--댓글 대상자의 아이디
	ref_group NUMBER, --원글(카페글)의 글번호 
	comment_group NUMBER, --댓글의 그룹번호
	deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지 여부 'yes' or 'no'
	regdate DATE --댓글 작성일
);

-- 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE board_cafe_comment_seq;


-- 사용자(회원) 정보를 저장할 테이블
CREATE TABLE users(
	id VARCHAR2(100) PRIMARY KEY,
	pwd VARCHAR2(100) NOT NULL,
	email VARCHAR2(100),
	profile VARCHAR2(100), -- 프로필 이미지 경로를 저장할 칼럼
	regdate DATE -- 가입일
);


CREATE TABLE board_cafe(
	num NUMBER PRIMARY KEY, --글번호
	writer VARCHAR2(100) NOT NULL, --작성자(로그인 된 아이디)
	title VARCHAR2(100) NOT NULL, --제목
	content CLOB, --글 내용
	viewCount NUMBER, --조회수
	regdate DATE --글 작성일 
);

--게시글의 번호를 얻어낼 시퀀스 
CREATE SEQUENCE board_cafe_seq;

--업로드된 파일의 정보를 저장할 테이블
CREATE TABLE board_file(
	num NUMBER PRIMARY KEY,
	writer VARCHAR2(100) NOT NULL,
	title VARCHAR2(100) NOT NULL,
	orgFileName VARCHAR2(100) NOT NULL, -- 원본 파일명
	saveFileName VARCHAR2(100) NOT NULL, -- 서버에 실제로 저장된 파일명
	fileSize NUMBER NOT NULL, -- 파일의 크기 
	regdate DATE
);

CREATE SEQUENCE board_file_seq;

CREATE TABLE board_gallery(
	num NUMBER PRIMARY KEY,
	writer VARCHAR2(100),
	caption VARCHAR2(100),
	imagePath VARCHAR2(100),
	regdate DATE
);

CREATE SEQUENCE board_gallery_seq;