mysql外键级联删除多个子表

-- 创建父表 orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100)
);

-- 创建子表 order_items
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

-- 插入示例数据
INSERT INTO orders (customer_name) VALUES ('Alice');
INSERT INTO order_items (order_id, product_name) VALUES (LAST_INSERT_ID(), 'Product A');

-- 删除父表记录，自动删除相关子表记录
DELETE FROM orders WHERE order_id = 1;

-- 检查外键约束
SHOW CREATE TABLE order_items;

-- 提交事务
START TRANSACTION;
DELETE FROM orders WHERE order_id = 1;
COMMIT;

-- 授予权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;