含外鍵約束的處理方案

-- 建立父表
CREATE TABLE parent (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 插入一些資料
INSERT INTO parent (id, name) VALUES (1, 'Parent 1'), (2, 'Parent 2');

-- 建立含外鍵約束FOREIGN KEY REFERENCES ON DELETE CASCADE的子表
CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 插入一些資料
INSERT INTO child (id, parent_id, name) VALUES (1, 1, 'Child 1'), (2, 1, 'Child 2'), (3, 2, 'Child 3');

-- 刪除父表記錄時手動刪除子表記錄
DELETE FROM child WHERE parent_id = 1;
DELETE FROM parent WHERE id = 1;

-- 更新父表記錄時手動更新子表記錄
UPDATE parent SET id = 3 WHERE id = 2;
UPDATE child SET parent_id = 3 WHERE parent_id = 2;

-- 建立觸發器
DELIMITER //

CREATE TRIGGER delete_parent_trigger
BEFORE DELETE ON parent
FOR EACH ROW
BEGIN
    DELETE FROM child WHERE parent_id = OLD.id;
END //

CREATE TRIGGER update_parent_trigger
AFTER UPDATE ON parent
FOR EACH ROW
BEGIN
    UPDATE child SET parent_id = NEW.id WHERE parent_id = OLD.id;
END //

DELIMITER ;

-- 插入一些資料
INSERT INTO parent (id, name) VALUES (3, 'Parent 3');
INSERT INTO child (id, parent_id, name) VALUES (4, 3, 'Child 4');

-- 刪除父表資料
DELETE FROM parent WHERE id = 3;

-- 插入一些資料並進行更新
INSERT INTO parent (id, name) VALUES (4, 'Parent 4');
INSERT INTO child (id, parent_id, name) VALUES (5, 4, 'Child 5');
UPDATE parent SET id = 5 WHERE id = 4;