Artineer - 테이블 생성 쿼리문

Note...

• 2022 05. 19(목) 멘토링 제외 테이블 설계 완료 추후 멘토링 설계하여 업데이트 해야함.
• 2022. 09. 21 (수) 테이블 구조 수정에 따라 쿼리문 변경

CREATE

use RenewalArtineer;

CREATE TABLE Member(
member_No bigint not null primary key auto_increment,
ID varchar(12),
Password varchar(15),
Name varchar(5),
Year varchar(4),
Month varchar(2),
Day varchar(2),
Email varchar(30),
firstNumber varchar(3),
middleNumber varchar(4),
lastNumber varchar(4),
Gender char(1),
Generation char(1),
Level char(1)
);

/* 공지사항 */
CREATE TABLE Notice (
notice_No bigint not null primary key auto_increment,
member_No bigint,
WriteDate TIMESTAMP,
Title text,
Detail text,
fileName varchar(300),
imageName varchar(300),
view bigint,
constraint FK_Notice_Member FOREIGN KEY (member_No) REFERENCES Member (member_No)
);

CREATE TABLE Notice_Comment (
noticeComment_No bigint not null primary key auto_increment,
member_No bigint,
detail text,
writeDate timestamp,
notice_No bigint,
parentComment_No bigint,
constraint FK_NoticeComment_Member FOREIGN KEY (member_No) REFERENCES Member (member_No),
constraint FK_NoticeComment_Notice FOREIGN KEY (notice_No) REFERENCES Notice (notice_No)
);

/* 회의록 */
CREATE TABLE Meeting (
meeting_No bigint not null primary key auto_increment,
member_No bigint,
WriteDate TIMESTAMP,
Title text,
Detail text,
fileName varchar(300),
imageName varchar(300),
view bigint,
constraint FK_Meeting_Member FOREIGN KEY (member_No) REFERENCES Member (member_No)
);

CREATE TABLE Meeting_Comment (
meetingComment_No bigint not null primary key auto_increment,
member_No bigint,
detail text,
writeDate timestamp,
meeting_No bigint,
parentComment_No bigint,
constraint FK_MeetingComment_Member FOREIGN KEY (member_No) REFERENCES Member (member_No),
constraint FK_MeetingComment_Meeting FOREIGN KEY (meeting_No) REFERENCES Meeting (meeting_No)
);

/* 멘토링 (일단 제외) */
/*CREATE TABLE Mentoring (
No bigint not null primary key auto_increment,
MentoName varchar(5),
Generation char(1),
Title text,
Detail text
);*/

/* 프로젝트 */
CREATE TABLE Project (
project_No bigint not null primary key auto_increment,
member_No bigint,
WriteDate TIMESTAMP,
Title text,
Detail text,
fileName varchar(300),
imageName varchar(300),
view bigint,
constraint FK_Project_Member FOREIGN KEY (member_No) REFERENCES Member (member_No)
);

CREATE TABLE Project_Comment (
projectComment_No bigint not null primary key auto_increment,
member_No bigint,
detail text,
writeDate timestamp,
project_No bigint,
parentComment_No bigint,
constraint FK_ProjectComment_Member FOREIGN KEY (member_No) REFERENCES Member (member_No),
constraint FK_ProjectComment_Project FOREIGN KEY (project_No) REFERENCES Project (project_No)
);

/* 자료실 */
CREATE TABLE Reference (
reference_No bigint not null primary key auto_increment,
member_No bigint,
WriteDate TIMESTAMP,
Title text,
Detail text,
fileName varchar(300),
imageName varchar(300),
view bigint,
constraint FK_Reference_Member FOREIGN KEY (member_No) REFERENCES Member (member_No)
);

CREATE TABLE Reference_Comment (
referenceComment_No bigint not null primary key auto_increment,
member_No bigint,
detail text,
writeDate timestamp,
reference_No bigint,
parentComment_No bigint,
constraint FK_ReferenceComment_Member FOREIGN KEY (member_No) REFERENCES Member (member_No),
constraint FK_ReferenceComment_Reference FOREIGN KEY (reference_No) REFERENCES Reference (reference_No)
);

/* 갤러리 */
CREATE TABLE Gallery (
gallery_No bigint not null primary key auto_increment,
member_No bigint,
WriteDate TIMESTAMP,
Title text,
Detail text,
fileName varchar(300),
imageName varchar(300),
view bigint,
constraint FK_Gallery_Member FOREIGN KEY (member_No) REFERENCES Member (member_No)
);

CREATE TABLE Gallery_Comment (
galleryComment_No bigint not null primary key auto_increment,
member_No bigint,
detail text,
writeDate timestamp,
gallery_No bigint,
parentComment_No bigint,
constraint FK_GalleryComment_Member FOREIGN KEY (member_No) REFERENCES Member (member_No),
constraint FK_GalleryComment_Gallery FOREIGN KEY (gallery_No) REFERENCES Gallery (gallery_No)
);