실습 Project3 설계

[CreatiCoding]

[CreatiCoding]

[CreatiCoding]

CREATE DATABASE creaticode;
-- 테이블 순서는 관계를 고려하여 한 번에 실행해도 에러가 발생하지 않게 정렬되었습니다.

-- user Table Create SQL
CREATE TABLE creaticode.user
(
    `user_id`         VARCHAR(255)                 NOT NULL    COMMENT 'user_id', 
    `oauth_provider`  ENUM('GOOGLE','FACEBOOK')    NOT NULL    COMMENT 'oauth_provider', 
    `oauth_uid`       VARCHAR(255)                 NOT NULL    COMMENT 'oauth_uid', 
    `username`        VARCHAR(45)                  NOT NULL    COMMENT 'username', 
    `password`        VARCHAR(255)                 NOT NULL    COMMENT 'password', 
    `profile`         VARCHAR(255)                 NULL        COMMENT 'profile', 
    PRIMARY KEY (user_id)
);

ALTER TABLE creaticode.user COMMENT 'user';

-- problem Table Create SQL
CREATE TABLE creaticode.problem
(
    `problem_idx`      INT                              NOT NULL    AUTO_INCREMENT COMMENT 'problem_idx', 
    `problem_title`    TEXT                             NOT NULL    COMMENT 'problem_title', 
    `difficulty`       ENUM('EASY','MEDIUM', 'HARD')    NOT NULL    COMMENT 'difficulty', 
    `like_count`       INT                              NOT NULL    COMMENT 'like_count', 
    `hate_count`       INT                              NOT NULL    COMMENT 'hate_count', 
    `contents`         TEXT                             NOT NULL    COMMENT 'contents', 
    `testcase`         TEXT                             NULL        COMMENT 'testcase', 
    `notes`            TEXT                             NULL        COMMENT 'notes', 
    `accepted_count`   INT                              NOT NULL    COMMENT 'accepted_count', 
    `submitted_count`  INT                              NOT NULL    COMMENT 'submitted_count', 
    `writer_id`        VARCHAR(255)                     NOT NULL    COMMENT 'writer_id', 
    `create_at`        TIMESTAMP                        NOT NULL    COMMENT 'create_at', 
    PRIMARY KEY (problem_idx)
);

ALTER TABLE creaticode.problem COMMENT 'problem';

ALTER TABLE creaticode.problem ADD CONSTRAINT FK_problem_writer_id_user_user_id FOREIGN KEY (writer_id)
 REFERENCES creaticode.user (user_id)  ON DELETE RESTRICT ON UPDATE RESTRICT;

-- answer Table Create SQL
CREATE TABLE creaticode.answer
(
    `answer_idx`   INT             NOT NULL    AUTO_INCREMENT COMMENT 'answer_idx', 
    `problem_idx`  INT             NOT NULL    COMMENT 'problem_idx', 
    `answer_code`  TEXT            NOT NULL    COMMENT 'answer_code', 
    `accepted`     BIT             NOT NULL    COMMENT 'accepted', 
    `writer_id`    VARCHAR(255)    NOT NULL    COMMENT 'writer_id', 
    `create_at`    TIMESTAMP       NOT NULL    COMMENT 'create_at', 
    PRIMARY KEY (answer_idx)
);

ALTER TABLE creaticode.answer COMMENT 'answer';

ALTER TABLE creaticode.answer ADD CONSTRAINT FK_answer_writer_id_user_user_id FOREIGN KEY (writer_id)
 REFERENCES creaticode.user (user_id)  ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE creaticode.answer ADD CONSTRAINT FK_answer_problem_idx_problem_problem_idx FOREIGN KEY (problem_idx)
 REFERENCES creaticode.problem (problem_idx)  ON DELETE RESTRICT ON UPDATE RESTRICT;

-- comment_problem Table Create SQL
CREATE TABLE creaticode.comment_problem
(
    `comment_idx`      INT             NOT NULL    AUTO_INCREMENT COMMENT 'comment_idx', 
    `problem_idx`      INT             NOT NULL    COMMENT 'problem_idx', 
    `comment_content`  TEXT            NOT NULL    COMMENT 'comment_content', 
    `writer_id`        VARCHAR(255)    NOT NULL    COMMENT 'writer_id', 
    `create_at`        TIMESTAMP       NOT NULL    COMMENT 'create_at', 
    PRIMARY KEY (comment_idx)
);

ALTER TABLE creaticode.comment_problem COMMENT 'comment_problem';

ALTER TABLE creaticode.comment_problem ADD CONSTRAINT FK_comment_problem_problem_idx_problem_problem_idx FOREIGN KEY (problem_idx)
 REFERENCES creaticode.problem (problem_idx)  ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE creaticode.comment_problem ADD CONSTRAINT FK_comment_problem_writer_id_user_user_id FOREIGN KEY (writer_id)
 REFERENCES creaticode.user (user_id)  ON DELETE RESTRICT ON UPDATE RESTRICT;

-- comment_answer Table Create SQL
CREATE TABLE creaticode.comment_answer
(
    `comment_idx`      INT             NOT NULL    AUTO_INCREMENT COMMENT 'comment_idx', 
    `answer_idx`       INT             NOT NULL    COMMENT 'answer_idx', 
    `comment_content`  TEXT            NOT NULL    COMMENT 'comment_content', 
    `writer_id`        VARCHAR(255)    NOT NULL    COMMENT 'writer_id', 
    `create_at`        TIMESTAMP       NOT NULL    COMMENT 'create_at', 
    PRIMARY KEY (comment_idx)
);

ALTER TABLE creaticode.comment_answer COMMENT 'comment_answer';

ALTER TABLE creaticode.comment_answer ADD CONSTRAINT FK_comment_answer_answer_idx_answer_answer_idx FOREIGN KEY (answer_idx)
 REFERENCES creaticode.answer (answer_idx)  ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE creaticode.comment_answer ADD CONSTRAINT FK_comment_answer_writer_id_user_user_id FOREIGN KEY (writer_id)
 REFERENCES creaticode.user (user_id)  ON DELETE RESTRICT ON UPDATE RESTRICT;