CREATE TABLE `PROJECT_TEAM`
(
`PROJECT_TEAM_ID` INTEGER NOT NULL AUTO_INCREMENT,
`MAX_MEMBER` INTEGER NOT NULL,
`CURRENT_MEMBER` INTEGER NOT NULL,
`TEAM_NAME` VARCHAR(50) NOT NULL,
`ADDED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`IS_ACTIVE` BOOLEAN DEFAULT FALSE NOT NULL,
PRIMARY KEY ( `PROJECT_TEAM_ID` )
);
CREATE TABLE `NOTICE`
(
`NOTICE_ID` INTEGER NOT NULL AUTO_INCREMENT,
`TITLE` VARCHAR(50) NOT NULL,
`CONTENT` LONGTEXT NOT NULL,
`POSTED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`IS_DELETED` BOOLEAN DEFAULT FALSE NOT NULL,
PRIMARY KEY ( `NOTICE_ID` )
);
CREATE TABLE `REPLY`
(
`REPLY_ID` INTEGER NOT NULL AUTO_INCREMENT,
`CONTENT` LONGTEXT NOT NULL,
`POSTED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`COMMENT_ID` INTEGER NOT NULL,
`IS_DELETED` BOOLEAN DEFAULT FALSE NOT NULL,
`MEMBER_ID` VARCHAR(20) NOT NULL,
PRIMARY KEY ( `REPLY_ID` )
);
CREATE TABLE `PERSONAL_LINK`
(
`URL` VARCHAR(255) NOT NULL,
`MEMBER_ID` VARCHAR(20) NOT NULL,
PRIMARY KEY ( `URL`,`MEMBER_ID` )
);
CREATE TABLE `PROJECT_MEMBER`
(
`PROJECT_MEMBER_ID` INTEGER NOT NULL AUTO_INCREMENT,
`MEMBER_ID` VARCHAR(20) NOT NULL,
`PROJECT_TEAM_ID` INTEGER NOT NULL,
`MEMBER_CATEGORY` VARCHAR(255) NOT NULL,
PRIMARY KEY ( `PROJECT_MEMBER_ID` )
);
CREATE TABLE `PROJECT`
(
`PROJECT_ID` INTEGER NOT NULL AUTO_INCREMENT,
`TITLE` VARCHAR(50) NOT NULL,
`START_DATE` VARCHAR(30) NOT NULL,
`END_DATE` VARCHAR(30),
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`STATUS` VARCHAR(20) NOT NULL,
`PROJECT_TEAM_ID` INTEGER NOT NULL,
`PROJECT_MEMBER_ID` INTEGER NOT NULL,
`IS_PUBLIC` BOOLEAN DEFAULT TRUE NOT NULL,
PRIMARY KEY ( `PROJECT_ID` )
);
CREATE TABLE `ISSUE`
(
`ISSUE_ID` INTEGER NOT NULL AUTO_INCREMENT,
`TITLE` VARCHAR(50) NOT NULL,
`CONTENT` LONGTEXT NOT NULL,
`IS_CLOSED` BOOLEAN DEFAULT FALSE NOT NULL,
`POSTED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`CLOSED_DATE` VARCHAR(30),
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`PROJECT_MEMBER_ID` INTEGER NOT NULL,
`PROJECT_ID` INTEGER NOT NULL,
PRIMARY KEY ( `ISSUE_ID` )
);
CREATE TABLE `BOOKMARK`
(
`BOOKMARK_ID` INTEGER NOT NULL AUTO_INCREMENT,
`IS_LIKE` BOOLEAN DEFAULT FALSE NOT NULL,
`IS_BOOKMARK` BOOLEAN DEFAULT FALSE NOT NULL,
`ADDED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`SEEKING_MEMBER_POST_ID` INTEGER NOT NULL,
`MEMBER_ID` VARCHAR(20) NOT NULL,
PRIMARY KEY ( `BOOKMARK_ID` )
);
CREATE TABLE `SCHEDULE`
(
`SCHEDULE_ID` INTEGER NOT NULL AUTO_INCREMENT,
`START_DATE` VARCHAR(30) NOT NULL,
`END_DATE` VARCHAR(30),
`TITLE` VARCHAR(50) NOT NULL,
`CONTENT` LONGTEXT NOT NULL,
`PROGRESS` FLOAT DEFAULT 0 NOT NULL,
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`PROJECT_MEMBER_ID` INTEGER NOT NULL,
`PROJECT_ID` INTEGER NOT NULL,
PRIMARY KEY ( `SCHEDULE_ID` )
);
CREATE TABLE `POST`
(
`POSTING_ID` INTEGER NOT NULL AUTO_INCREMENT,
`TITLE` VARCHAR(50) NOT NULL,
`CONTENT` LONGTEXT NOT NULL,
`POSTED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`HITS` INTEGER DEFAULT 0 NOT NULL,
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`MEMBER_ID` VARCHAR(20) NOT NULL,
`IS_DELETED` BOOLEAN DEFAULT FALSE NOT NULL,
PRIMARY KEY ( `POSTING_ID` )
);
CREATE TABLE `PROFILE`
(
`PROFILE_ID` INTEGER NOT NULL AUTO_INCREMENT,
`TILTLE` VARCHAR(50) NOT NULL,
`CONTENT` LONGTEXT NOT NULL,
`TECH_STACK` VARCHAR(255),
`MEMBER_ID` VARCHAR(20) NOT NULL,
`IS_DELETED` BOOLEAN DEFAULT FALSE NOT NULL,
PRIMARY KEY ( `PROFILE_ID` )
);
CREATE TABLE `COMMENT`
(
`COMMENT_ID` INTEGER NOT NULL AUTO_INCREMENT,
`CONTENT` LONGTEXT NOT NULL,
`POSTED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`MEMBER_ID` VARCHAR(20) NOT NULL,
`IS_DELETED` BOOLEAN DEFAULT FALSE NOT NULL,
`COMMENT_CATEGORY` VARCHAR(20) NOT NULL,
`POSTING_ID` INTEGER,
`SEEKING_MEMBER_POST_ID` INTEGER,
`ISSUE_ID` INTEGER,
PRIMARY KEY ( `COMMENT_ID` )
);
CREATE TABLE `JOIN_WAITING_MEMBER`
(
`JOIN_WAITING_MEMBER_ID` INTEGER NOT NULL AUTO_INCREMENT,
`PROJECT_TEAM_ID` INTEGER NOT NULL,
`MEMBER_ID` VARCHAR(20) NOT NULL,
`CATEGORY` VARCHAR(10) NOT NULL,
`JOIN_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
PRIMARY KEY ( `JOIN_WAITING_MEMBER_ID` )
);
CREATE TABLE `MEMBER`
(
`MEMBER_ID` VARCHAR(20) NOT NULL,
`NAME` VARCHAR(30) NOT NULL,
`NICKNAME` VARCHAR(20),
`PASSWORD` VARCHAR(20) NOT NULL,
`EMAIL` VARCHAR(40),
`PHONE_NUM` VARCHAR(11),
`ADDRESS` VARCHAR(255),
`BIRTHDAY` VARCHAR(30),
`SIGN_UP_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`IS_WITHDRAW` BOOLEAN DEFAULT FALSE NOT NULL,
PRIMARY KEY ( `MEMBER_ID` )
);
CREATE TABLE `SEEKING_MEMBER_POST`
(
`SEEKING_MEMBER_POST_ID` INTEGER NOT NULL AUTO_INCREMENT,
`TITLE` VARCHAR(50) NOT NULL,
`SEEKING_MEMBER` INTEGER NOT NULL,
`START_DATE` VARCHAR(30) NOT NULL,
`END_DATE` VARCHAR(30),
`TECH_STACK` VARCHAR(255),
`CONTENT` LONGTEXT NOT NULL,
`LAST_MODIFIED_DATE` VARCHAR(30) DEFAULT NOW() NOT NULL,
`IS_SEEKING` BOOLEAN DEFAULT FALSE NOT NULL,
`MEMBER_ID` VARCHAR(20) NOT NULL,
`IS_DELETED` BOOLEAN DEFAULT FALSE NOT NULL,
`PROJECT_ID` INTEGER NOT NULL,
PRIMARY KEY ( `SEEKING_MEMBER_POST_ID` )
);
ALTER TABLE `REPLY`
ADD CONSTRAINT `REPLY_FK` FOREIGN KEY ( `COMMENT_ID` )
REFERENCES `COMMENT` (`COMMENT_ID` );
ALTER TABLE `REPLY`
ADD CONSTRAINT `REPLY_FK1` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `PERSONAL_LINK`
ADD CONSTRAINT `PERSONAL_LINK_FK` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `PROJECT_MEMBER`
ADD CONSTRAINT `PROJECT_MEMBER_FK` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `PROJECT_MEMBER`
ADD CONSTRAINT `PROJECT_MEMBER_FK1` FOREIGN KEY ( `PROJECT_TEAM_ID` )
REFERENCES `PROJECT_TEAM` (`PROJECT_TEAM_ID` );
ALTER TABLE `PROJECT`
ADD CONSTRAINT `PROJECT_FK` FOREIGN KEY ( `PROJECT_TEAM_ID` )
REFERENCES `PROJECT_TEAM` (`PROJECT_TEAM_ID` );
ALTER TABLE `PROJECT`
ADD CONSTRAINT `PROJECT_FK1` FOREIGN KEY ( `PROJECT_MEMBER_ID` )
REFERENCES `PROJECT_MEMBER` (`PROJECT_MEMBER_ID` );
ALTER TABLE `ISSUE`
ADD CONSTRAINT `ISSUE_FK` FOREIGN KEY ( `PROJECT_MEMBER_ID` )
REFERENCES `PROJECT_MEMBER` (`PROJECT_MEMBER_ID` );
ALTER TABLE `ISSUE`
ADD CONSTRAINT `ISSUE_FK1` FOREIGN KEY ( `PROJECT_ID` )
REFERENCES `PROJECT` (`PROJECT_ID` );
ALTER TABLE `BOOKMARK`
ADD CONSTRAINT `BOOKMARK_FK` FOREIGN KEY ( `SEEKING_MEMBER_POST_ID` )
REFERENCES `SEEKING_MEMBER_POST` (`SEEKING_MEMBER_POST_ID` );
ALTER TABLE `BOOKMARK`
ADD CONSTRAINT `BOOKMARK_FK1` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `SCHEDULE`
ADD CONSTRAINT `SCHEDULE_FK` FOREIGN KEY ( `PROJECT_MEMBER_ID` )
REFERENCES `PROJECT_MEMBER` (`PROJECT_MEMBER_ID` );
ALTER TABLE `SCHEDULE`
ADD CONSTRAINT `SCHEDULE_FK1` FOREIGN KEY ( `PROJECT_ID` )
REFERENCES `PROJECT` (`PROJECT_ID` );
ALTER TABLE `POST`
ADD CONSTRAINT `POST_FK` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `PROFILE`
ADD CONSTRAINT `PROFILE_FK` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `COMMENT`
ADD CONSTRAINT `COMMENT_FK` FOREIGN KEY ( `POSTING_ID` )
REFERENCES `POST` (`POSTING_ID` );
ALTER TABLE `COMMENT`
ADD CONSTRAINT `COMMENT_FK1` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `COMMENT`
ADD CONSTRAINT `COMMENT_FK2` FOREIGN KEY ( `SEEKING_MEMBER_POST_ID` )
REFERENCES `SEEKING_MEMBER_POST` (`SEEKING_MEMBER_POST_ID` );
ALTER TABLE `COMMENT`
ADD CONSTRAINT `COMMENT_FK3` FOREIGN KEY ( `ISSUE_ID` )
REFERENCES `ISSUE` (`ISSUE_ID` );
ALTER TABLE `JOIN_WAITING_MEMBER`
ADD CONSTRAINT `JOIN_WAITING_MEMBER_FK` FOREIGN KEY ( `PROJECT_TEAM_ID` )
REFERENCES `PROJECT_TEAM` (`PROJECT_TEAM_ID` );
ALTER TABLE `JOIN_WAITING_MEMBER`
ADD CONSTRAINT `JOIN_WAITING_MEMBER_FK1` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );
ALTER TABLE `SEEKING_MEMBER_POST`
ADD CONSTRAINT `SEEKING_MEMBER_POST_FK` FOREIGN KEY ( `MEMBER_ID` )
REFERENCES `MEMBER` (`MEMBER_ID` );