[DB] 설계한 schema 기반의 SQL 생성하기

오늘은 어제 점검했던 문제 중 한가지인 DB Foreign key를 연동하기 위해 

 

DB 스키마의 SQL문을 작성할 것이다.

 

처음에는 springboot의 Entity로 스키마를 생성할 때, fk를 자동생성해주는 코드를 짜려고 했는데,

 

생각보다 정상적으로 만들어지지 않아 MySQL workbench에서 일일히 fk를 연결해줬었다.

 

근데 개발자가 이런 반복적인 일을 하나하나 수행한다는게 너무 비효율적이라고 생각되서

 

그나마 현재로서 Entity 정의되어있는건 건들지 않고 자동 생성해주는 방법은

 

SQL문을 작성하고 이를 실행하는 방법인 것 같다고 생각했다.

 

따라서 내가 설계한 ERD를 기반으로 FK 설정이 포함된 SQL문을 작성했다.

 

 

-- Table: workspace
CREATE TABLE workspace (
    space_id VARCHAR(255) PRIMARY KEY,
    owner_id BIGINT NOT NULL,
    space_content VARCHAR(255),
    space_title VARCHAR(30),
    update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    snapshot_count INT,
    workspace_bpm INT,
    FOREIGN KEY (owner_id) REFERENCES account(account_id) ON DELETE CASCADE
);

-- Table: workspace_member_map
CREATE TABLE workspace_member_map (
    map_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    space_id VARCHAR(255) NOT NULL,
    FOREIGN KEY (account_id) REFERENCES account(account_id) ON DELETE CASCADE,
    FOREIGN KEY (space_id) REFERENCES workspace(space_id) ON DELETE CASCADE
);

-- Table: snapshot
CREATE TABLE snapshot (
    snapshot_id VARCHAR(255) PRIMARY KEY,
    snapshot_content VARCHAR(255),
    snapshot_title VARCHAR(50),
    space_id VARCHAR(255) NOT NULL,
    update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    views BIGINT,
    snapshot_bpm INT,
    FOREIGN KEY (space_id) REFERENCES workspace(space_id) ON DELETE CASCADE
);

-- Table: snapshot_instrument_map
CREATE TABLE snapshot_instrument_map (
    map_id VARCHAR(255) PRIMARY KEY,
    instrument ENUM('PIANO', 'GUITAR', 'DRUM') NOT NULL,
    snapshot_id VARCHAR(255) NOT NULL,
    FOREIGN KEY (snapshot_id) REFERENCES snapshot(snapshot_id) ON DELETE CASCADE
);

-- Table: snapshot_note
CREATE TABLE snapshot_note (
    note_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    note_x INT NOT NULL,
    note_y INT NOT NULL,
    snapshot_instrument VARCHAR(255) NOT NULL,
    FOREIGN KEY (snapshot_instrument) REFERENCES snapshot_instrument_map(map_id) ON DELETE CASCADE
);

-- Table: note_instrument_map
CREATE TABLE note_instrument_map (
    map_id VARCHAR(255) PRIMARY KEY,
    instrument ENUM('PIANO', 'GUITAR', 'DRUM') NOT NULL,
    space_id VARCHAR(255) NOT NULL,
    FOREIGN KEY (space_id) REFERENCES workspace(space_id) ON DELETE CASCADE
);

-- Table: note
CREATE TABLE note (
    note_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    note_x INT NOT NULL,
    note_y INT NOT NULL,
    space_instrument VARCHAR(255) NOT NULL,
    FOREIGN KEY (space_instrument) REFERENCES note_instrument_map(map_id) ON DELETE CASCADE
);

-- Table: message
CREATE TABLE message (
    message_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    msg_content VARCHAR(255),
    space_id VARCHAR(255) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES account(account_id) ON DELETE CASCADE,
    FOREIGN KEY (space_id) REFERENCES workspace(space_id) ON DELETE CASCADE
);

 

 

해당 SQL문은 결과적으로 다음과 같은 Schema를 생성한다.

 

 

최종적으로 Table을 모두 삭제하고 SQL문을 실행하여 FK 설정을 마쳤다.