親殺し

DROP DATABASE test;

CREATE DATABASE test;

use test;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE buildings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE rooms (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    user_id INT NOT NULL,
    building_id INT NOT NULL,
    FOREIGN KEY (user_id)
        REFERENCES users (id)
        ON DELETE CASCADE,
    FOREIGN KEY (building_id)
        REFERENCES buildings (id)
        ON DELETE CASCADE
);

INSERT INTO users(name) VALUES('hoge');
INSERT INTO buildings(name) VALUES('test');
INSERT INTO rooms(name, user_id, building_id) VALUES('room1',1,1);

SELECT * FROM buildings;
SELECT * FROM users;
SELECT * FROM rooms;

DELETE FROM buildings WHERE id = 1;

-- room will be deleted even if user exists