7/8 14:45 - 최신 - type 연결 테이블 추가, 기본 값 삽입 + 해커톤기간에 users 테이블 img 추가

use boonbae;

drop table if exists comments, comments_like, first_category, funding, funding_donate_list, funding_donate, funding_like, qna, recycle_confirm, recycling_info, recycling_info_tag, recycling_info_type, recycling_type, second_category, tag, tip, tree, users;
    
    
    
    create table comments (
       pk bigint not null auto_increment,
        content longtext not null,
        create_at datetime(6),
        report_cnt integer not null,
        info_pk bigint not null,
        user_pk bigint,
        primary key (pk)
    ) engine=InnoDB;

    create table comments_like (
       pk bigint not null auto_increment,
        comment_pk bigint not null,
        user_pk bigint,
        primary key (pk)
    ) engine=InnoDB;

    create table first_category (
       pk bigint not null auto_increment,
        category_name varchar(255),
        primary key (pk)
    ) engine=InnoDB;

    create table funding (
       pk bigint not null auto_increment,
        close_date date not null,
        content longtext not null,
        create_at datetime(6) not null,
        current_amount bigint not null,
        is_approved bit not null,
        main_img longtext not null,
        open_date date not null,
        supporting_amount bigint not null,
        target_amount bigint not null,
        title varchar(255) not null,
        second_category bigint,
        user_pk bigint,
        primary key (pk)
    ) engine=InnoDB;

create table funding_donate (
   pk bigint not null auto_increment,
   funding_pk bigint not null,
   user_pk bigint,
   primary key (pk)
) engine=InnoDB;

create table funding_like (
   pk bigint not null auto_increment,
   funding_pk bigint not null,
   user_pk bigint,
   primary key (pk)
) engine=InnoDB;

create table qna (
   pk bigint not null auto_increment,
   create_at datetime(6) not null,
   descriptions longtext not null,
   qna_type varchar(255) not null,
   reply_text longtext,
   title varchar(255) not null,
   user_pk bigint,
   primary key (pk)
) engine=InnoDB;

create table recycle_confirm (
   pk bigint not null auto_increment,
   create_at datetime(6) not null,
   image_url mediumtext not null,
   confirm_status varchar(255) not null,
   user_pk bigint,
   primary key (pk)
) engine=InnoDB;

    create table recycling_info (
       pk bigint not null auto_increment,
        create_date date not null,
        descriptions mediumtext not null,
        image_url mediumtext,
        info_name varchar(255) not null,
        recycle_process mediumtext not null,
        view_cnt int default 0 not null,
        primary key (pk)
    ) engine=InnoDB;

create table recycling_info_tag (
   pk bigint not null auto_increment,
   recycling_info_pk bigint,
   tag_pk bigint,
   primary key (pk)
) engine=InnoDB;

    create table recycling_info_type (
       pk bigint not null auto_increment,
        recycling_info_pk bigint,
        recycle_type_pk bigint,
        primary key (pk)
    ) engine=InnoDB;
    
        create table recycling_type (
       pk bigint not null auto_increment,
        type_name varchar(255) not null,
        primary key (pk)
    ) engine=InnoDB;

create table second_category (
   pk bigint not null auto_increment,
   category_name varchar(255),
   first_category_pk bigint,
   primary key (pk)
) engine=InnoDB;

create table tag (
   pk bigint not null auto_increment,
   tag_name varchar(255) not null,
   primary key (pk)
) engine=InnoDB
;    

create table tip (
   pk bigint not null auto_increment,
   content varchar(1000),
   primary key (pk)
) engine=InnoDB;

    create table tree (
       pk bigint not null auto_increment,
        accumulated_exp bigint not null,
        exp integer not null,
        recycle_cnt integer not null,
        tonic_available integer not null,
        update_date date not null,
        upload_available integer not null,
        is_watching_ad bit not null,
        primary key (pk)
    ) engine=InnoDB;
    
    
    create table users (
       pk bigint not null auto_increment,
        create_date date not null,
        eco_point integer not null,
        id varchar(255) not null unique,
        introduction longtext,
        nickname varchar(255) not null unique,
        passwords varchar(255) not null,
        image_url longtext default null,
        roles varchar(255) not null,
        tree_pk bigint,
        primary key (pk)
    ) engine=InnoDB;

-- 분리배출정보 댓글은 정보가 사라지면 같이 사라짐
alter table comments 
   add constraint FKboyyxqm3n8k10ywygkdgm1ufv 
   foreign key (info_pk) 
   references recycling_info (pk) on delete cascade
;    

-- 댓글은 user가 사라져도 알 수 없음으로 유지, set null
alter table comments 
   add constraint FKmj8exr3ks40q9v6kwfx4wjs3e 
   foreign key (user_pk) 
   references users (pk) on delete set null
;    

-- 댓글 좋아요는 댓글이 사라지면 같이 사라짐
alter table comments_like 
   add constraint FKb4jsihivs3mr6yms9xk34u3jl 
   foreign key (comment_pk) 
   references comments (pk) on delete cascade;
   
-- 댓글 좋아요는 user가 사라져도 유지됨 - user는 null
alter table comments_like 
   add constraint FK1ij75b6lvi7otgic9vorvhkif 
   foreign key (user_pk) 
   references users (pk) on delete set null;
    
-- 펀딩은 카테고리 없어져도 사라지지 않고, 카테고리는 null로 표시 -> 기타로 들어가든지 해야할듯
alter table funding 
   add constraint FKstks8kvac1rt3thi6h3ae8gvu 
   foreign key (second_category) 
   references second_category (pk) on delete set null;
   
-- 펀딩은 펀딩을 올린 user가 사라져도 사라지지 않음. set null됨. 알수없음으로 표시?
alter table funding 
   add constraint FKm6t2isps31x075qt15qdw8fra 
   foreign key (user_pk) 
   references users (pk) on delete set null;
    
-- 펀딩 후원 리스트는 펀딩이 사라지면 같이 사라짐
alter table funding_donate 
   add constraint FK37o4s4iw8gnr5weraprprwph8 
   foreign key (funding_pk) 
   references funding (pk) on delete cascade;

-- 펀딩 후원 리스트는 user가 사라져도 그대로 남아있어야함
alter table funding_donate 
   add constraint FKc4hhayd6vh5gn2533xi0u1iqr 
   foreign key (user_pk) 
   references users (pk) on delete set null;

-- 펀딩좋아요는 펀딩이 사라지면 같이 사라짐
alter table funding_like 
   add constraint FKe5d4whp3nvry4ax0ja3si3d1f 
   foreign key (funding_pk) 
   references funding (pk) on delete cascade;

-- 펀딩좋아요는 user가 사라져도 남아있음 -> null이다
alter table funding_like 
   add constraint FKb23xnxn3inxxffuhrb91b20xk 
   foreign key (user_pk) 
   references users (pk) on delete set null;
   
   
   
-- qna는 user가 사라져도 사라지지 않는다. 알 수 없음으로 표시됨
alter table qna 
   add constraint FKqio9vmtjr8kl3aoamr4jia1u4 
   foreign key (user_pk) 
   references users (pk) on delete set null;    

-- 재활용 확인(관리자용)은 인증하는 user가 사라져도 남아있음 
alter table recycle_confirm 
   add constraint FKtnjjcay65amcvud1un1ak77ma 
   foreign key (user_pk) 
   references users (pk) on delete set null;
   
-- 분리배출 정보가 사라지면 태그랑 연결된 고리도 사라짐
alter table recycling_info_tag 
   add constraint FK6l4ayach6m272ei1ktys9wcnf 
   foreign key (recycling_info_pk) 
   references recycling_info (pk) on delete cascade;
    
-- 태그가 사라지면 연결고리도 사라짐
alter table recycling_info_tag 
   add constraint FKfu4tnfi8p31teo722r9cocwni 
   foreign key (tag_pk) 
   references tag (pk) on delete cascade;
    
-- recycling_info와 type의 연결고리는 recycling_info 가 사라지면 같이 사라짐
alter table recycling_info_type 
   add constraint FKeuj662r0o43hftdcqq71bcjsx 
   foreign key (recycling_info_pk) 
   references recycling_info (pk) on delete cascade;
   
-- recycling_type이 사라지면 recycling_info_type 연결고리도 같이 사라짐 (쓸 일은 없을듯)
alter table recycling_info_type 
   add constraint FK31ib259jp59l1lsrjj0xu4id0 
   foreign key (recycle_type_pk) 
   references recycling_type (pk) on delete cascade;
    
   
-- 두번째 카테고리는 첫 번째 카테고리가 사라지면 같이 사라짐
alter table second_category 
   add constraint FKntsr0vqy9wt0lcje2otd5xpsf 
   foreign key (first_category_pk) 
   references first_category (pk) on delete cascade;

-- 나무가 사라지면 user도 사라짐
alter table users 
   add constraint FK4r5ut4nge1g6q28cvtr4e9u7b 
   foreign key (tree_pk) 
   references tree (pk) on delete cascade;

-- update users set roles='ADMIN' where pk = 1;
select * from funding;

-- 기본값 삽입
INSERT INTO recycling_type (type_name)
VALUES ('종이'), ('종이팩'), ('페트'), ('플라스틱'), ('유리'), ('비닐'), ('캔'), ('일반쓰레기'), ('음식물');

이전