init.sql

CREATE TABLE IF NOT EXISTS member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(100) NOT NULL,
    provider_id VARCHAR(1000) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    nickname VARCHAR(255),
    birthdate DATE,
    gender VARCHAR(10),
    theme VARCHAR(30),
    member_status VARCHAR(30)
    CONSTRAINT unique_provider_id UNIQUE (provider_id)
);

CREATE TABLE IF NOT EXISTS member_info (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    residence VARCHAR(255),
    marketing BOOLEAN DEFAULT FALSE,
    check_time DATETIME,
    last_online DATETIME
    CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS review (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL,
    placeCode VARCHAR(100) NOT NULL,
    datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    isRecommend BOOLEAN NOT NULL,
    content VARCHAR(500) NOT NULL,
    tags TEXT,
    likeCnt BIGINT,
    CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS placelike (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    placeCode VARCHAR(100) NOT NULL,
    member_id BIGINT NOT NULL,
    CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS review_like (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    placeCode VARCHAR(100) NOT NULL,
    member_id BIGINT NOT NULL,
    review_id BIGINT NOT NULL,

    CONSTRAINT like_member FOREIGN KEY (member_id)
    REFERENCES member(id)
    ON DELETE CASCADE,

    CONSTRAINT like_review FOREIGN KEY (review_id)
    REFERENCES review(id)
    ON DELETE CASCADE
);