mysql主键和外键语句

-- 创建用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 插入用户数据
INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('Bob', 'bob@example.com');

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

-- 错误示例
INSERT INTO orders (user_id, order_date) VALUES (3, '2023-10-03'); -- user_id 3 不存在

-- 正确示例
INSERT INTO users (username, email) VALUES ('Charlie', 'charlie@example.com');
INSERT INTO orders (user_id, order_date) VALUES (3, '2023-10-03'); -- user_id 3 现在存在

-- 创建表时设置级联删除
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- 删除用户时，相关订单也会被删除
DELETE FROM users WHERE user_id = 1;