CREATE TABLE `users` (
`user_idx` int NOT NULL auto_increment,
`fk_user_su_id` int NULL,
`user_id` varchar(20) NOT NULL unique,
`user_pwd` varchar(20) NOT NULL,
`user_name` varchar(20) NOT NULL,
`user_birth` char(10) NOT NULL,
`user_email` varchar(40) NOT NULL,
`user_nickname` varchar(20) NOT NULL COMMENT 'null 시 user_name으로',
`user_intro` varchar(100) NULL,
`user_exp` int NOT NULL DEFAULT 0,
`user_img` varchar(2000) NOT NULL DEFAULT '기본 이미지 경로',
`user_join_date` datetime NOT NULL DEFAULT now(),
`user_update_date` datetime NULL,
`user_revoke_date` datetime NULL,
`user_refresh_token` varchar(300) NULL,
CONSTRAINT `PK_USERS` PRIMARY KEY (`user_idx`)
)default character set utf8mb4;
CREATE TABLE `badges` (
`badge_id` int NOT NULL auto_increment,
`badge_name` varchar(20) NOT NULL,
`badge_img` varchar(2000) NOT NULL,
`badge_info` varchar(30) NOT NULL,
CONSTRAINT `PK_BADGES` PRIMARY KEY (`badge_id`)
)default character set utf8mb4;
CREATE TABLE `user_badges` (
`ub_user_idx` int NOT NULL,
`ub_badge_id` int NOT NULL,
CONSTRAINT `PK_USER_BADGES` PRIMARY KEY (`ub_user_idx`, `ub_badge_id`)
)default character set utf8mb4;
CREATE TABLE `follows` (
`follow_from` int NOT NULL,
`follow_to` int NOT NULL,
`follow_date` datetime NOT NULL DEFAULT now(),
CONSTRAINT `PK_FOLLOWS` PRIMARY KEY (`follow_from`, `follow_to`)
)default character set utf8mb4;
CREATE TABLE `words` (
`word_id` int NOT NULL auto_increment,
`word_name_kr` varchar(50) NOT NULL,
`word_name_en` varchar(50) NOT NULL,
`word_def` varchar(300) NOT NULL,
`word_summ` varchar(100) NOT NULL,
`word_link` varchar(200) NULL,
CONSTRAINT `PK_WORDS` PRIMARY KEY (`word_id`)
)default character set utf8mb4;
CREATE TABLE `cards` (
`card_id` int NOT NULL auto_increment,
`fk_card_user_idx` int NOT NULL,
`card_repost_cnt` int NULL DEFAULT 0,
`card_word_id` int NULL,
`card_origin_card_id` int NULL,
`card_content` varchar(300) NULL,
`card_link` varchar(200) NULL,
`card_regist_date` datetime NOT NULL DEFAULT now(),
`card_update_date` datetime NULL,
`card_delete_date` datetime NULL,
`card_access` int NOT NULL COMMENT '0: 전체, 1: 팔로워, 2: 비공개',
CONSTRAINT `PK_CARDS` PRIMARY KEY (`card_id`)
)default character set utf8mb4;
CREATE TABLE `categories` (
`category_id` int NOT NULL auto_increment,
`category_name` varchar(20) NOT NULL,
CONSTRAINT `PK_CATEGORIES` PRIMARY KEY (`category_id`)
)default character set utf8mb4;
CREATE TABLE `likes` (
`like_from` int NOT NULL,
`like_to` int NOT NULL,
`like_date` datetime NOT NULL DEFAULT now(),
CONSTRAINT `PK_LIKES` PRIMARY KEY (`like_from`, `like_to`)
)default character set utf8mb4;
CREATE TABLE `words_categories` (
`wc_word_id` int NOT NULL,
`wc_category_id` int NOT NULL,
CONSTRAINT `PK_WORDS_CATEGORIES` PRIMARY KEY (`wc_word_id`, `wc_category_id`)
)default character set utf8mb4;
CREATE TABLE `cards_relation_words` (
`cr_card_id` int NOT NULL,
`cr_word_id` int NOT NULL,
CONSTRAINT `PK_CARDS_RELATION_WORDS` PRIMARY KEY (`cr_card_id`, `cr_word_id`)
)default character set utf8mb4;
CREATE TABLE `users_favorite_categories` (
`uf_user_idx` int NOT NULL,
`uf_category_id` int NOT NULL,
CONSTRAINT `PK_USERS_FAVORITE_CATEGORIES` PRIMARY KEY (`uf_user_idx`, `uf_category_id`)
)default character set utf8mb4;
CREATE TABLE `sns_users` (
`su_id` int NOT NULL auto_increment,
`su_name` varchar(20) NULL,
`su_img` varchar(2000) NULL,
CONSTRAINT `PK_SNS_USERS` PRIMARY KEY (`su_id`)
)default character set utf8mb4;
CREATE TABLE `boards` (
`board_id` int NOT NULL auto_increment,
`fk_board_user_idx` int NOT NULL,
`board_title` varchar(50) NOT NULL,
`board_content` varchar(500) NOT NULL,
`board_type` int NOT NULL COMMENT '0 : 일반, 1: Q&A, 2:건의',
`board_regist_date` datetime NOT NULL default now(),
`board_update_date` datetime NULL,
`board_delete_date` datetime NULL,
CONSTRAINT `PK_BOARDS` PRIMARY KEY (`board_id`)
)default character set utf8mb4;
CREATE TABLE `view_words` (
`vw_word_id` int NOT NULL,
`vw_user_idx` int NOT NULL,
`vw_view_date` datetime NOT NULL DEFAULT now(),
CONSTRAINT `PK_VIEW_WORDS` PRIMARY KEY (`vw_word_id`, `vw_user_idx`)
)default character set utf8mb4;
CREATE TABLE `dm_lists` (
`dl_id` int NOT NULL auto_increment,
`dl_create_date` datetime NOT NULL DEFAULT now(),
CONSTRAINT `PK_DM_LISTS` PRIMARY KEY (`dl_id`)
)default character set utf8mb4;
CREATE TABLE `quizzes` (
`quiz_id` int NOT NULL auto_increment,
`quiz_create_date` datetime NOT NULL DEFAULT now(),
`quiz_type` int NOT NULL COMMENT '0: quiz, 1: battle',
CONSTRAINT `PK_QUIZZES` PRIMARY KEY (`quiz_id`)
)default character set utf8mb4;
CREATE TABLE `battles` (
`battle_id` int NOT NULL auto_increment,
`fk_battle_from_user_idx` int NOT NULL,
`fk_battle_to_user_idx` int NOT NULL,
`fk_battle_sl_id` int NOT NULL,
`battle_from_date` datetime NOT NULL default now(),
`battle_to_date` datetime NULL,
`battle_from_score` int NULL,
`battle_to_score` int NULL,
`battle_word_cnt` int NULL,
`fk_battle_quiz_id` int NOT NULL,
CONSTRAINT `PK_BATTLES` PRIMARY KEY (`battle_id`)
)default character set utf8mb4;
CREATE TABLE `searched_keywords` (
`sk_user_idx` int NOT NULL,
`sk_searched_keyword` varchar(40) NOT NULL,
`sk_searched_date` datetime NOT NULL DEFAULT now(),
CONSTRAINT `PK_SEARCHED_KEYWORDS` PRIMARY KEY (`sk_user_idx`)
)default character set utf8mb4;
CREATE TABLE `voca_lists` (
`vl_id` int NOT NULL auto_increment,
`fk_vl_user_idx` int NOT NULL,
`vl_name` varchar(20) NOT NULL,
CONSTRAINT `PK_VOCA_LISTS` PRIMARY KEY (`vl_id`)
)default character set utf8mb4;
CREATE TABLE `voca_list_cards` (
`vlc_id` int NOT NULL auto_increment,
`vlc_sl_id` int NOT NULL,
`vlc_wc_id` int NOT NULL,
`vlc_wc_select` int NOT NULL COMMENT '1 : 단어, 2 : 카드',
`svc_delete_date` datetime NULL,
CONSTRAINT `PK_VOCA_LIST_CARDS` PRIMARY KEY (`vlc_id`, `vlc_sl_id`)
)default character set utf8mb4;
CREATE TABLE `comments` (
`comment_id` int NOT NULL auto_increment,
`fk_comment_user_idx` int NOT NULL,
`comment_board_id` int NULL,
`comment_card_id` int NULL,
`comment_content` varchar(100) NOT NULL,
`comment_picked` boolean NOT NULL DEFAULT false COMMENT 'true : 채택',
`comment_regist_date` datetime NOT NULL default now(),
`comment_delete_date` datetime NULL,
CONSTRAINT `PK_COMMENTS` PRIMARY KEY (`comment_id`)
)default character set utf8mb4;
CREATE TABLE `dm_participants` (
`fk_dp_dl_id` int NOT NULL,
`fk_dp_user_idx` int NOT NULL,
`dp_dl_last_date` datetime NOT NULL DEFAULT now(),
CONSTRAINT `PK_DM_PARTICIPANTS` PRIMARY KEY (`fk_dp_dl_id`, `fk_dp_user_idx`)
)default character set utf8mb4;
CREATE TABLE `dm_messages` (
`dm_id` int NOT NULL auto_increment,
`fk_dm_user_idx` int NOT NULL,
`fk_dm_dl_id` int NOT NULL,
`dm_content` varchar(300) NOT NULL,
`dm_send_date` datetime NOT NULL DEFAULT now(),
`dm_battle_YN` boolean NOT NULL DEFAULT false COMMENT 'true : 도전 메시지',
CONSTRAINT `PK_DM_MESSAGES` PRIMARY KEY (`dm_id`)
)default character set utf8mb4;
CREATE TABLE `quiz_words` (
`qw_id` int NOT NULL auto_increment,
`qw_word_id` int NOT NULL,
`qw_quiz_id` int NOT NULL,
CONSTRAINT `PK_QUIZ_WORDS` PRIMARY KEY (`qw_id`)
)default character set utf8mb4;
CREATE TABLE `quiz_participants` (
`qp_quiz_id` int NOT NULL,
`fk_qp_user_idx` int NOT NULL,
`qp_participate_date` datetime NOT NULL DEFAULT now(),
`qp_score` int NOT NULL,
CONSTRAINT `PK_QUIZ_PARTICIPANTS` PRIMARY KEY (`qp_quiz_id`)
)default character set utf8mb4;
CREATE TABLE `quiz_answers` (
`qw_id` int NOT NULL,
`qa_user_idx` int NOT NULL,
`qa_answer` varchar(50) NULL,
`qa_yn` int NULL COMMENT '0: 오답, 1: 정답',
CONSTRAINT `PK_QUIZ_ANSWERS` PRIMARY KEY (`qw_id`, `qa_user_idx`)
)default character set utf8mb4;
ALTER TABLE `users` ADD CONSTRAINT `FK_sns_users_TO_users_1` FOREIGN KEY (
`fk_user_su_id`
)
REFERENCES `sns_users` (
`su_id`
);
ALTER TABLE `follows` ADD CONSTRAINT `FK_users_TO_follows_1` FOREIGN KEY (
`follow_from`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `follows` ADD CONSTRAINT `FK_users_TO_follows_2` FOREIGN KEY (
`follow_to`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `user_badges` ADD CONSTRAINT `FK_users_TO_user_badges_1` FOREIGN KEY (
`ub_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `user_badges` ADD CONSTRAINT `FK_badges_TO_user_badges_1` FOREIGN KEY (
`ub_badge_id`
)
REFERENCES `badges` (
`badge_id`
);
ALTER TABLE `cards` ADD CONSTRAINT `FK_users_TO_cards_1` FOREIGN KEY (
`fk_card_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `likes` ADD CONSTRAINT `FK_users_TO_likes_1` FOREIGN KEY (
`like_from`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `likes` ADD CONSTRAINT `FK_cards_TO_likes_1` FOREIGN KEY (
`like_to`
)
REFERENCES `cards` (
`card_id`
);
ALTER TABLE `words_categories` ADD CONSTRAINT `FK_words_TO_words_categories_1` FOREIGN KEY (
`wc_word_id`
)
REFERENCES `words` (
`word_id`
);
ALTER TABLE `words_categories` ADD CONSTRAINT `FK_categories_TO_words_categories_1` FOREIGN KEY (
`wc_category_id`
)
REFERENCES `categories` (
`category_id`
);
ALTER TABLE `cards_relation_words` ADD CONSTRAINT `FK_cards_TO_cards_relation_words_1` FOREIGN KEY (
`cr_card_id`
)
REFERENCES `cards` (
`card_id`
);
ALTER TABLE `cards_relation_words` ADD CONSTRAINT `FK_words_TO_cards_relation_words_1` FOREIGN KEY (
`cr_word_id`
)
REFERENCES `words` (
`word_id`
);
ALTER TABLE `users_favorite_categories` ADD CONSTRAINT `FK_users_TO_users_favorite_categories_1` FOREIGN KEY (
`uf_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `users_favorite_categories` ADD CONSTRAINT `FK_categories_TO_users_favorite_categories_1` FOREIGN KEY (
`uf_category_id`
)
REFERENCES `categories` (
`category_id`
);
ALTER TABLE `boards` ADD CONSTRAINT `FK_users_TO_boards_1` FOREIGN KEY (
`fk_board_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `view_words` ADD CONSTRAINT `FK_words_TO_view_words_1` FOREIGN KEY (
`vw_word_id`
)
REFERENCES `words` (
`word_id`
);
ALTER TABLE `view_words` ADD CONSTRAINT `FK_users_TO_view_words_1` FOREIGN KEY (
`vw_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `battles` ADD CONSTRAINT `FK_users_TO_battles_1` FOREIGN KEY (
`fk_battle_from_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `battles` ADD CONSTRAINT `FK_users_TO_battles_2` FOREIGN KEY (
`fk_battle_to_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `battles` ADD CONSTRAINT `FK_voca_lists_TO_battles_1` FOREIGN KEY (
`fk_battle_sl_id`
)
REFERENCES `voca_lists` (
`vl_id`
);
ALTER TABLE `battles` ADD CONSTRAINT `FK_quizzes_TO_battles_1` FOREIGN KEY (
`fk_battle_quiz_id`
)
REFERENCES `quizzes` (
`quiz_id`
);
ALTER TABLE `searched_keywords` ADD CONSTRAINT `FK_users_TO_searched_keywords_1` FOREIGN KEY (
`sk_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `voca_lists` ADD CONSTRAINT `FK_users_TO_voca_lists_1` FOREIGN KEY (
`fk_vl_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `voca_list_cards` ADD CONSTRAINT `FK_voca_lists_TO_voca_list_cards_1` FOREIGN KEY (
`vlc_sl_id`
)
REFERENCES `voca_lists` (
`vl_id`
);
ALTER TABLE `comments` ADD CONSTRAINT `FK_users_TO_comments_1` FOREIGN KEY (
`fk_comment_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `dm_participants` ADD CONSTRAINT `FK_dm_lists_TO_dm_participants_1` FOREIGN KEY (
`fk_dp_dl_id`
)
REFERENCES `dm_lists` (
`dl_id`
);
ALTER TABLE `dm_participants` ADD CONSTRAINT `FK_users_TO_dm_participants_1` FOREIGN KEY (
`fk_dp_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `dm_messages` ADD CONSTRAINT `FK_users_TO_dm_messages_1` FOREIGN KEY (
`fk_dm_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `dm_messages` ADD CONSTRAINT `FK_dm_lists_TO_dm_messages_1` FOREIGN KEY (
`fk_dm_dl_id`
)
REFERENCES `dm_lists` (
`dl_id`
);
ALTER TABLE `quiz_words` ADD CONSTRAINT `FK_words_TO_quiz_words_1` FOREIGN KEY (
`qw_word_id`
)
REFERENCES `words` (
`word_id`
);
ALTER TABLE `quiz_words` ADD CONSTRAINT `FK_quizzes_TO_quiz_words_1` FOREIGN KEY (
`qw_quiz_id`
)
REFERENCES `quizzes` (
`quiz_id`
);
ALTER TABLE `quiz_participants` ADD CONSTRAINT `FK_quizzes_TO_quiz_participants_1` FOREIGN KEY (
`qp_quiz_id`
)
REFERENCES `quizzes` (
`quiz_id`
);
ALTER TABLE `quiz_participants` ADD CONSTRAINT `FK_users_TO_quiz_participants_1` FOREIGN KEY (
`fk_qp_user_idx`
)
REFERENCES `users` (
`user_idx`
);
ALTER TABLE `quiz_answers` ADD CONSTRAINT `FK_quiz_words_TO_quiz_answers_1` FOREIGN KEY (
`qw_id`
)
REFERENCES `quiz_words` (
`qw_id`
);
ALTER TABLE `quiz_answers` ADD CONSTRAINT `FK_users_TO_quiz_answers_1` FOREIGN KEY (
`qa_user_idx`
)
REFERENCES `users` (
`user_idx`
);