mysql如何删除关联表

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

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

-- 删除客户及其所有订单
DELETE FROM customers WHERE customer_id = 1;

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

-- 删除客户，订单中的customer_id将被设置为NULL
DELETE FROM customers WHERE customer_id = 1;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT
);

-- 尝试删除客户，但如果该客户有订单，则会失败
DELETE FROM customers WHERE customer_id = 1;

-- 删除所有相关订单
DELETE FROM orders WHERE customer_id = 1;

-- 删除客户
DELETE FROM customers WHERE customer_id = 1;

-- 禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 删除客户及其所有订单
DELETE FROM customers WHERE customer_id = 1;

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;