mysql外键约束实例

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

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

-- 示例：插入无效的外键
INSERT INTO orders (id, customer_id, order_date) VALUES (1, 999, '2023-10-01');

-- 解决方法：确保 customer_id 存在于 customers 表中
INSERT INTO orders (id, customer_id, order_date) VALUES (1, 1, '2023-10-01');

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

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

-- 创建 orders 表，并设置外键约束
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 插入数据
INSERT INTO customers (id, name) VALUES (1, 'Alice');
INSERT INTO orders (id, customer_id, order_date) VALUES (1, 1, '2023-10-01');

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