mysql外键约束sql语句

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE;

-- 示例：插入无效的客户ID
INSERT INTO orders (order_date, customer_id) VALUES ('2023-10-01', 999);

-- 确保客户ID存在
SELECT * FROM customers WHERE id = 999;

-- 如果不存在，插入有效数据
INSERT INTO customers (name) VALUES ('New Customer');

-- 示例：删除客户ID为1的客户
DELETE FROM customers WHERE id = 1;

-- 使用级联删除
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE;

-- 再次删除客户ID为1的客户
DELETE FROM customers WHERE id = 1;