10/5 DB에 DataBase 생성과 테이블 생성

DataBase 생성

이전 글에서 아래 명령어를 이용하여 mysql 데이터베이스에 권한을 부여해줬었다.

grant all privileges on mysql.* to 'boardadm'@'%';

 

하지만 board라는 데이터 베이스를 생성할 것이며 거기에 맞는 권한을 부여해줘야 한다.

아래 명령어를 이용하여 database 생성 후 권한 부여

CREATE DATABASE board;

GRANT ALL PRIVILEGES ON board.* TO 'boardadm'@'%';

 

테이블 생성

User 테이블 

CREATE TABLE User (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    user_pw VARCHAR(255) NOT NULL,
    user_name VARCHAR(100) NOT NULL,
    user_email VARCHAR(100) NOT NULL UNIQUE,
    user_regDate DATETIME DEFAULT CURRENT_TIMESTAMP
);

 

Roll 테이블

CREATE TABLE Roll (
    roll_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

 

User_Roll 테이블

CREATE TABLE User_Roll (
    user_id INT,
    roll_id INT,
    PRIMARY KEY (user_id, roll_id),
    FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
    FOREIGN KEY (roll_id) REFERENCES Roll(roll_id) ON DELETE CASCADE
);

 

Category 테이블

CREATE TABLE Category (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

 

Post 테이블

CREATE TABLE Post (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    category_id INT,
    view_count INT DEFAULT 0,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE SET NULL
);

 

Post_Category 테이블

CREATE TABLE Post_Category (
    post_id INT,
    category_id INT,
    PRIMARY KEY (post_id, category_id),
    FOREIGN KEY (post_id) REFERENCES Post(post_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE CASCADE
);

 

Comment 테이블

CREATE TABLE Comment (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    content TEXT NOT NULL,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES Post(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE
);