Notice
Recent Posts
Recent Comments
Link
컴퓨터는 잘못이 없다..
[SpringProject_EBD]DB테이블 본문
Contents
접기
테이블 & 시퀀스 생성 쿼리문
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;'공부 > Spring' 카테고리의 다른 글
| [Spring boot]spring initializr로 데모 프로젝트 생성하고 웹브라우저에 Hello World 띄어보기(8080으로 접속 안될 때 해결, Execution Failed 해결) (0) | 2021.08.03 |
|---|---|
| [Spring]경로 작성하기 (0) | 2021.03.03 |
| [Spring]깃헙 Clone하는 방법 (0) | 2021.02.17 |
'공부/Spring' Related Articles
more
Comments
