mysql加外键语句

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

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

-- 示例：插入无效的客户ID
INSERT INTO orders (order_id, order_date, customer_id) VALUES (1, '2023-10-01', 999);
-- 错误：customer_id 999 在 customers 表中不存在

-- 解决方法：插入有效的客户ID
INSERT INTO orders (order_id, order_date, customer_id) VALUES (1, '2023-10-01', 1);

-- 示例：使用 ON DELETE SET NULL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);