mysql 增加主外键约束

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

-- 创建 orders 表，并添加外键约束
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

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

-- 插入订单数据
INSERT INTO orders (order_date, customer_id) VALUES ('2023-01-01', 1);
INSERT INTO orders (order_date, customer_id) VALUES ('2023-01-02', 2);

-- 查询所有订单及其对应的客户
SELECT orders.order_id, orders.order_date, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- 确保 customers 表存在
CREATE TABLE IF NOT EXISTS customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

-- 确保 orders 表中的 customer_id 和 customers 表中的 customer_id 数据类型匹配
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

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

-- 再插入订单数据
INSERT INTO orders (order_date, customer_id) VALUES ('2023-01-01', 1);