mysql: on update cascade，on delete set null不工作

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

ALTER TABLE orders MODIFY customer_id INT NULL;

-- 禁用触发器
DISABLE TRIGGER your_trigger_name ON your_table;

-- 执行更新或删除操作

-- 重新启用触发器
ENABLE TRIGGER your_trigger_name ON your_table;

GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'localhost';

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

INSERT INTO customers(customer_id, customer_name) VALUES (1, 'John Doe');
INSERT INTO orders(order_id, customer_id, order_date) VALUES (101, 1, '2023-01-15');

UPDATE customers SET customer_id = 2 WHERE customer_id = 1;
-- 检查orders表，customer_id应该自动更新为2
SELECT * FROM orders;

DELETE FROM customers WHERE customer_id = 2;
-- 检查orders表，customer_id应该自动设置为NULL
SELECT * FROM orders;