django로 내 웹페이지 만들기(5) - DB 만들기 (MySQL)

ERD

이렇게 DB 를 구상해보았다.

 

첨부파일, 이미지를 위해서 file_table 추가했다.

 

다음은 SQL 파일이다.

 

 

SQL파일 쿼리문 보기👇

DROP DATABASE IF EXISTS odin_web_db;

CREATE DATABASE IF NOT EXISTS odin_web_db CHARACTER SET UTF8MB4 COLLATE utf8mb4_general_ci;

USE odin_web_db;

CREATE TABLE `odin_web_db`.`platform_TABLE` (
	`NUM` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `TYPE` VARCHAR(45) NOT NULL COMMENT '\'Server, Language, DB\'',
    PRIMARY KEY (`NUM`)) 
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;


CREATE TABLE `odin_web_db`.`SKILL_TABLE` (
  `NUM` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `TYPE_idx` INT UNSIGNED,
  `SKILL_NAME` VARCHAR(255) NOT NULL COMMENT '\'Windows Server, MS-SQL...\'',
  `Created_At` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`NUM`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;


CREATE TABLE `odin_web_db`.`bbs_table` (
	`bbs_idx` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `skill_idx` INT UNSIGNED,
    `bbs_title` VARCHAR(255) NOT NULL,
    `bbs_content` LONGTEXT,
    `wroteAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modifiedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;



CREATE TABLE `odin_web_db`.`file_table` (
	`file_idx` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `bbs_idx` INT UNSIGNED,
    `file_path` TINYTEXT,
    `file_name` TINYTEXT,
    `file_type` TINYTEXT,
    `file_size` LONGBLOB,
    `CreatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;



ALTER TABLE `SKILL_TABLE` 
ADD FOREIGN KEY (`TYPE_idx`)
REFERENCES `platform_TABLE`(`NUM`);

ALTER TABLE `bbs_table`
ADD FOREIGN KEY (`skill_idx`)
REFERENCES `SKILL_TABLE`(`NUM`);

ALTER TABLE `file_table`  
ADD FOREIGN KEY (`bbs_idx`)
REFERENCES `bbs_table`(`bbs_idx`);

ALTER TABLE `bbs_table`
ADD COLUMN `PWD` VARCHAR(255) NOT NULL;


-- To See Foreign key 
SELECT * FROM information_schema.table_constraints where table_name = 'file_table';
SELECT * FROM information_schema.table_constraints where constraint_schema = 'odin_web_db';