Knowledge Notes

게시판 댓글관리 구현 중 삭제된 게시글(tb_board.delete_yn = 'Y')에 달렸던 댓글은 댓글관리에서 볼 수 없게 하기위한 처리를 진행하다가 조인이 불가피하여 사용하였다. (조인은 속도를 늦추니 불가피한 경우에만 사용하면 좋다고 기억)

그 과정에서 탐색한 조인 방법을 정리한다.

1. 테이블

CREATE TABLE `tb_board` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `delete_yn` varchar(1) DEFAULT 'N',
  `create_date` datetime DEFAULT NULL,
  `views` int DEFAULT '0',
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `writer_id` (`writer_id`),
  CONSTRAINT `tb_board_ibfk_1` FOREIGN KEY (`writer_id`) REFERENCES `tb_userinfo` (`id`) ON DELETE CASCADE
)

CREATE TABLE `tb_comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `board_id` int NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `board_id` (`board_id`),
  KEY `writer_id` (`writer_id`),
  CONSTRAINT `tb_comment_ibfk_1` FOREIGN KEY (`board_id`) REFERENCES `tb_board` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tb_comment_ibfk_2` FOREIGN KEY (`writer_id`) REFERENCES `tb_userinfo` (`id`) ON DELETE CASCADE
)

2. Inner Join 예시

select c.*
from board.tb_board b join board.tb_comment c 
	on b.delete_yn='y' and b.id = c.board_id and c.writer = 'rhkdals'
order by b.id;

• select * from [ ] join [ ] on [조건]
• select c.* : tb_comment 테이블의 컬럼만 모두 조회

1. 테이블

CREATE TABLE `tb_board` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `delete_yn` varchar(1) DEFAULT 'N',
  `create_date` datetime DEFAULT NULL,
  `views` int DEFAULT '0',
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `writer_id` (`writer_id`),
  CONSTRAINT `tb_board_ibfk_1` FOREIGN KEY (`writer_id`) REFERENCES `tb_userinfo` (`id`) ON DELETE CASCADE
)

CREATE TABLE `tb_comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `board_id` int NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

2. 외래키(FK) 추가

ALTER TABLE board.tb_comment ADD FOREIGN KEY(board_id) REFERENCES board.tb_board(id);

• tb_comment의 board_id컬럼이 tb_board 테이블의 id 값을 참조하도록 설정
• ALTER TABLE 참조하는 테이블 ADD FOREIGN KEY(참조하는 컬럼명) REFERENCES 참조받는 테이블(참조받는 컬럼명);

ALTER TABLE board.tb_comment ADD FOREIGN KEY(board_id) REFERENCES board.tb_board(id) ON DELETE CASCADE;

• 참조받는 테이블의 데이터가 삭제되면 참조하는 테이블의 데이터도 삭제하고 싶을 때 ON DELETE CASCADE를 맨 뒷 부분에 추가하면 된다.

3. 제약조건 확인

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'board';

1. 테이블 생성 시 바로 적용하기

- 참조 테이블의 데이터가 삭제되면 그 데이터와 연관있던 데이터를 함께 삭제하는 내용이다.

예를 들어 A가 게시글을 썼는데 회원탈퇴를 진행했다. 그럼 A가 썼던 게시글은 자동 삭제된다.

CREATE TABLE `board`.`tb_userinfo` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(100) NOT NULL,
  `email` varchar(45) NOT NULL,
  `role` varchar(20) DEFAULT 'MEMBER',
  PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `board`.`tb_board` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `writer_id` bigint NOT NULL,
  `writer` varchar(20) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`writer_id`, `writer`) REFERENCES `board`.`tb_userinfo` (`id`, `username`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• FK 추가 뒤에 ON DELETE CASCADE만 붙여주면 된다.

문제점 : USERINFO의 PK(기본키)를 참조해서 BOARD에 FK를 만들려고하는데 지속적으로 오류가 발생했다.

• 오류의 이유는 기존 USERINFO테이블의 PK를 id컬럼에만 적용하고는 BOARD 테이블에서 USERINFO테이블의 username컬럼을 참조하려고해서 그랬다.

해결 : USERINFO테이블의 기본키를 id와 username 두 개를 설정하여 해결했다. (id를 해제하려했으나 Auto Increment는 PK가 적용된 컬럼만 된다고 한다.)

* 복합키 : 이렇게 2개의 컬럼을 PK로 지정하면 이를 복합키라고 한다.

수정 후 테이블 상태 (BOARD 테이블의 writer_id와 writer는 각각 USERINFO의 id와 username 컬럼을 참조한다.)

USERINFO 테이블

BOARD 테이블

1. 테이블 데이터 포함 테이블 삭제

사용법 : DROP TABLE 테이블명;

DROP TABEL 'board'.'tb_userinfo';

2. 테이블 데이터 삭제

사용법 : TRUNCATE 테이블명;

TRUNCATE 'board'.'tb_userinfo';

문제점 : SpringBoot 프로젝트 진행 중 테스트 데이터를 넣고 삭제하면 id 값이 1부터 다시 시작이 아닌

(삭제 전의 마지막 값 + 1) 로 시작됐다.

보기 좋지 않아서 초기화 하는 방법을 찾아봤다.

사용법 : ALTER TABLE 테이블명 AUTO_INCREMENT=1;