게시판 페이지 수정하기 DB

시간이 남는김에 예전에 해왔던 과제인 게시판만들기 과제를 수정하려고 한다. (7주차)

CREATE TABLE `account` (
	`idx`	INT(12)	NOT NULL AUTO_INCREMENT,
	`id`	VARCHAR(20)	NOT NULL UNIQUE,
	`pw`	VARCHAR(20)	NOT NULL,
	`name`	VARCHAR(20)	NOT NULL,
	`email`	VARCHAR(24)	NOT NULL UNIQUE,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY(idx)  
);

CREATE TABLE `post` (
	`idx` INT(12) NOT NULL AUTO_INCREMENT,
	`account_idx` INT(12) NOT NULL,
	`title` VARCHAR(300) NOT NULL,
	`content` VARCHAR(3000) NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`modified_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (idx),
    FOREIGN KEY (account_idx) REFERENCES `account` (`idx`)
);

CREATE TABLE `comment` (
	`idx` INT(12) NOT NULL AUTO_INCREMENT,
	`post_idx` INT(12) NOT NULL,
	`account_idx` INT(12) NOT NULL,
	`content` VARCHAR(600) NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`modified_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (idx),
    FOREIGN KEY (post_idx) REFERENCES `post` (`idx`),
    FOREIGN KEY (account_idx) REFERENCES `account` (`idx`)
);




DELIMITER //
CREATE TRIGGER update_modified_timestamp
BEFORE UPDATE ON post
FOR EACH ROW
BEGIN
    SET NEW.modified_at = NOW();
END;//
DELIMITER ;


DELIMITER //
CREATE TRIGGER update_timestamp
BEFORE INSERT ON post
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.modified_at = NULL;
END;//
DELIMITER ;



DELIMITER //
CREATE TRIGGER update_comment_modified_timestamp
BEFORE UPDATE ON comment
FOR EACH ROW
BEGIN
    SET NEW.modified_at = NOW();
END;//
DELIMITER ;

DELIMITER //
CREATE TRIGGER update_comment_timestamp
BEFORE INSERT ON comment
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.modified_at = NULL;
END;//
DELIMITER ;

회원정보에서 생년월일은 빼버렸다. 댓글창 제대로 구현하고싶어서 다시 만드는 것이기 때문에