mysql如何使用外键

-- 创建 customers 表
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 创建 orders 表，并设置外键
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- 插入客户数据
INSERT INTO customers (name) VALUES ('Alice');
INSERT INTO customers (name) VALUES ('Bob');

-- 插入订单数据，确保 customer_id 存在于 customers 表中
INSERT INTO orders (order_date, customer_id) 
SELECT CURDATE(), id FROM customers WHERE name = 'Alice';

-- 设置级联删除
ALTER TABLE orders 
DROP FOREIGN KEY fk_customer_id,
ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

-- 删除客户数据，级联删除相关订单数据
DELETE FROM customers WHERE id = 1;